#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    111
    Rep Power
    14

    OleDBDataReader "No data exists for row/column" when it DOES :(


    When a page loads a class is initialized and I'm getting the property information for that class here. I'm querying an Access DB and I've checked it manually, the data is there. The reader.FieldCount is 22(correct) but the Depth is zero. I'm guessing this is where the problem is but the information is definately there. WebsiteInfo is the correct DB name and SiteInfo.Street returns '4', which is the name of the column that holds the value of propertyAddress, which is correct casing and punctuation as compared to the field value. I get the error on reader.GetValues(results)

    Thanks a bunch, guys.

    Code:
    Dim results(0) As Object
    Dim reader As OleDbDataReader
    sql = "SELECT * FROM WebsiteInfo WHERE " & SiteInfo.Street.ToString & " = '" & propertyAddress & "'"
    conn = New OleDbConnection(connStr)
    comm = New OleDbCommand(sql, conn)
    conn.Open()
    reader = comm.ExecuteReader(Data.CommandBehavior.SingleRow)
    Array.Resize(results, reader.FieldCount)
    reader.GetValues(results)
    conn.Close()
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Posts
    296
    Rep Power
    60
    are you saying that your SQL should be...

    [code=sql]
    SELECT * FROM WebsiteInfo WHERE '4' = 'some address'
    [/code]

    or am I missing something?

    what happens when you run the EXACT query in ACCESS? breakpoint at the where you instantiate the new connection. copy the query from the "sql" variable and paste it in ACCESS and run it. How many rows are returned?
  4. #3
  5. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,134
    Rep Power
    123
    Just as eclipse said I would say check out what StreetInfo.Street actually is and what propertyAddress is. I'm guessing they don't match.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    111
    Rep Power
    14
    I checked the actual query with a breakpoint already and SiteInfo.Street returns 4 and streetAddress, in this case, returns 1133 Bradenton Rd

    In Access database WebsiteInfo contains 1133 Bradenton Rd in column named '4'. I inserted teh data into the table programmatically so my connString is fine.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Posts
    296
    Rep Power
    60
    ok, so "SiteInfo.Street" is actually the column name that you want to match the address on?

    I don't think you need the " ' " around the column name.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    111
    Rep Power
    14
    SiteInfo is an enumeration, Street = 4, the column name = 4, row1col5(cols named 0 to 21) in DB WebsiteInfo contains 1133 Bradenton Rd and the propertyAddress variable contains "1133 Bradenton Rd".

    When it breaks I get: SELECT * FROM WebsiteInfo WHERE '4' = '1133 Bradenton Rd';

    The reader returns the correct fieldcount but no depth. I inserted programmatically the information that I'm selecting in an ealier sub procedure. What's happening is somebody submits a form, one of those fields for my TEST contains "1133 Bradenton Rd". After form submission another page is called and when it loads it reads the information just inserted into the database, that is the statement you see above. I have to delete the records from access every time I retest it or else I get a duplicate value error, so the data is obviously there and recognizable to the program. This doesn't make any sense.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Posts
    296
    Rep Power
    60
    Originally Posted by Jancarius
    SiteInfo is an enumeration, Street = 4, the column name = 4, row1col5(cols named 0 to 21) in DB WebsiteInfo contains 1133 Bradenton Rd and the propertyAddress variable contains "1133 Bradenton Rd".

    When it breaks I get: SELECT * FROM WebsiteInfo WHERE '4' = '1133 Bradenton Rd';

    The reader returns the correct fieldcount but no depth. I inserted programmatically the information that I'm selecting in an ealier sub procedure. What's happening is somebody submits a form, one of those fields for my TEST contains "1133 Bradenton Rd". After form submission another page is called and when it loads it reads the information just inserted into the database, that is the statement you see above. I have to delete the records from access every time I retest it or else I get a duplicate value error, so the data is obviously there and recognizable to the program. This doesn't make any sense.
    here is your problem....

    Code:
    SELECT * FROM WebsiteInfo WHERE '4' = '1133 Bradenton Rd'
    needs to be

    Code:
    SELECT * FROM WebsiteInfo WHERE 4 = '1133 Bradenton Rd'
    I just did a quick query in SQL Server. without the " ' ", the query returned data, but when I added " ' " around the column name, I didn't get any data.

    This is because "4" is not equal to "1133 Bradenton Rd". Therefore, no rows are returned.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    111
    Rep Power
    14
    But then I get a data type mismatch. I guess I cant use numbers for the fieldnames? I guess I'll use the enumeration.ToString() and see if that works. Nope. This Select statement doesn't work either.

    SELECT * FROM WebsiteInfo WHERE Street = '1133 Bradenton Rd';

    But the info is there.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    111
    Rep Power
    14
    This is ridiculous. It doesn't make any damn sense why it won't return any information from the row/column. The SQL Statement is correct. The Connection String is correct. The Connection and Command execution are correct. The tables are created, named and ordered correctly. WTF!?!?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Posts
    296
    Rep Power
    60
    put brackets around column name....I don't know how I forgot about that.

    Code:
    sql = "SELECT * FROM WebsiteInfo WHERE [" & SiteInfo.Street.ToString & "] = '" & propertyAddress & "'"
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    111
    Rep Power
    14
    I tried that and I still got no info. I used the Database Explorer to make a new query against my AgentInfo table. I used this statement:
    sql = "SELECT * FROM AgentInfo WHERE [Username] = '" & Username & "'"

    On break the statement looks like this:
    SELECT * FROM AgentInfo WHERE [Username] = 'jancarius'

    So I copied this statement over to the Database Explorer and Execute New Query. It reformatted it to the following and returned the proper result. I tried putting this code into my string but it didn't return anything either.

    SELECT Username, [First], [Last], Phone1, Phone1Type, Phone2, Phone2Type, Fax, Email, Slogan, Company, Street, Street2, Unit, City, State, Zip, AddressType, PersonalURL, Mortgage, Insurance, Title, Membership
    FROM AgentInfo
    WHERE (Username = 'jancarius')

    I also tried, with all of these statements, to "Verify Syntax" in the Database Explorer and it says "THIS COMMAND IS NOT SUPPORTED BY THIS PROVIDER" WTF?! Here's my connection string:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ListingWebsiteGenerator\App_Data\db.mdb;Persist Security Info=True"

    Thanks again for taking your time.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Posts
    32
    Rep Power
    7
    I'm guessing the problem is that your column name is a number.

    Try brackets again, so that when it breaks it reads:
    SELECT * FROM WebsiteInfo WHERE [4] = '1133 Bradenton Rd';

    I also noticed that in your first post, your sql doesn't end with a ';'. I don't know if that would affect anything.

    Other things to try:
    sql = "SELECT * FROM WebsiteInfo WHERE WebsiteInfo." & SiteInfo.Street.ToString & " = '" & propertyAddress & "';"

    sql = "SELECT * FROM WebsiteInfo WHERE [" & SiteInfo.Street.ToString & "] Like '*" & propertyAddress & "*';"

    sql = "SELECT * FROM WebsiteInfo WHERE [" & SiteInfo.Street.ToString & "] Like '*';"

    sql = "SELECT * FROM WebsiteInfo;"

    Maybe, you've already tried all of those.


    Just to clarify the last two examples won't fix your problem. There just there to try to isolate your problem.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    111
    Rep Power
    14
    Thanks for ya'lls efforts. I finally figured out that I didn't do the If reader.Read() statement. I didn't think I needed it with a single result and the first time I tried it something else was wrong.

IMN logo majestic logo threadwatch logo seochat tools logo