Whats wrong with retrieve data?

ProgMan

Well-known member
Joined
Nov 25, 2006
Messages
55
Location
UK
Programming Experience
3-5
Hi,

I'm trying to retrieve data from ms access database. Here's the simple code

VB.NET:
Private mADOConnect As New ADODB.Connection()
Private mRS As New ADODB.Recordset()

mADOConnect.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\pr\HQ.mdb")
mRS.Open("item", mADOConnect, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
[B]query = "select item.instock from item where item.id=3"[/B]
mADOConnect.Execute(query)
[COLOR="Red"]stk = mRS.Fields(0).Value[/COLOR]

mRS.Close()
mADOConnect.Close()

This is only showing the first record from the item table (e.g. item.id 1 not item.id 3). I guess I am doing something wrong with the recordset (mRS) and not with the select statement because the following statement works fine.

VB.NET:
query = "update item set instock=" & newstk & " where item.id=3"
mADOConnect.Execute(query)

I've been scratching my head for more than an hour now :( Any help would be much appreciated :eek:
 
Last edited:
Here's a troubleshooting tip:

Take your query, run it in the mdb to see how many or what type of data you are getting back. Compare the query result with your program's output and go from there.

Thanks giadich. I did try that (in ms access) and it works ok.

This is only showing the first record from the item table (e.g. item.id 1 not item.id 3).

Actually, I was wrong there! The mRS recordset is containing all the records not just the first one (id 1) or the required one (id 3) as if its not filtering the result according to the criteria. :confused:

Don't have a clue whats going on !!! :mad:

Anyone ? :eek:
 
In the mdb, the query returns only 1 row as expected but the same query from VB is returning all the records from that table to the mRS recordset. :confused:

Although, I've found a way to solve this by putting an additional line (e.g. mRS.filter=criteria) in my code, I'm not very happy with this solution since the original query is returning all records which will slow down my application when I'll have lots of records in my database.

Any idea why the query is returning all records to the mRS recordset?
 
I reread your original post and I think the issue lies in the line:

VB.NET:
mRS.Open("item", mADOConnect, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

Here you open the table "item". That's why you are getting the whole bunch of records back instead of just one.

Change your code to something like this:
VB.NET:
query = "select item.instock from item where item.id=3"
mADOConnect.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\pr\HQ.mdb")
mRS.Open(query, mADOConnect, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

To confirm the row count you could check the recordcount property:
VB.NET:
Response.write(msRS.RecordCount)
You might want to google & read more about adodb.RecordSet, Connection & Command objects.

Good luck.
 
VB.NET:
query = "select item.instock from item where item.id=3"
mADOConnect.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\pr\HQ.mdb")
mRS.Open([B][SIZE="3"]query[/SIZE][/B], mADOConnect, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

That does work! I didn't even know that I could open the connection using a query. Thanks for sharing the information.

However, i can't use this in my case as I'll be making queries within a loop with different ids. In that case, for every row, I will have to open and close connections for each query which doesn't seem very wise i guess.

But again, thanks for sharing your thoughts. I greatly appreciate it. :)
 
Back
Top