Whats wrong with retrieve data?

[ProgMan]

Hi,

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

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.

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

 

[giadich]

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.

 

[ProgMan]

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.

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

Anyone ?

 

[giadich]

Your query is set to return only 1 row (assuming the ID column is unique) unless you allow duplicate values.

So question, how many rows did the query you ran in mdb return? Can you have duplicate values in this field?

 

[ProgMan]

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.

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?

 

[giadich]

I reread your original post and I think the issue lies in the line:

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:

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:

Response.write(msRS.RecordCount)

You might want to google & read more about adodb.RecordSet, Connection & Command objects.

Good luck.

 

[ProgMan]

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.

 

[giadich]

If you need to loop through all the rows then you can open the table like you had it before then in the loop, change the row filter option (as you discovered before)