October 1st, 2008, 06:58 PM
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.
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)
reader = comm.ExecuteReader(Data.CommandBehavior.SingleRow)
October 2nd, 2008, 07:39 AM
are you saying that your SQL should be...
SELECT * FROM WebsiteInfo WHERE '4' = 'some address'
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?
October 2nd, 2008, 07:57 AM
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.
October 2nd, 2008, 10:17 AM
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.
October 2nd, 2008, 10:51 AM
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.
October 2nd, 2008, 12:03 PM
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.
October 2nd, 2008, 01:12 PM
here is your problem....
Originally Posted by Jancarius
needs to be
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.
SELECT * FROM WebsiteInfo WHERE 4 = '1133 Bradenton Rd'
This is because "4" is not equal to "1133 Bradenton Rd". Therefore, no rows are returned.
October 2nd, 2008, 02:50 PM
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.
October 4th, 2008, 06:05 AM
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!?!?
October 4th, 2008, 06:53 PM
put brackets around column name....I don't know how I forgot about that.
sql = "SELECT * FROM WebsiteInfo WHERE [" & SiteInfo.Street.ToString & "] = '" & propertyAddress & "'"
October 5th, 2008, 07:07 AM
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
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.
October 6th, 2008, 07:28 AM
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  = '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.
October 7th, 2008, 12:47 PM
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.