Getting SQL query results in MS Access but not in VBScript

Question

When I perform a SQL Query through MS Access I get results returned but if I perform the same query in VBScript my RecordCount is -1. I can't tell if this is a connection error. I'm not getting any but it's clear that the SQL does return results in Access. I'm getting 0 hits in the below connect code.

sqlquery = "SELECT * FROM i2cner WHERE Authors Like 'Ish*';"
dim conn
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "d:/inetpub/ga/sarina/i2cner/contacts2000.mdb"
set r = Server.CreateObject("ADODB.recordset")
if r.state = 1 then r.close
r.Open sqlquery, conn
hits = r.RecordCount
session("hits") = hits
set session("r") = r

shahkalpesh · Accepted Answer · 2013-02-18 07:17:09Z

1

r.CursorLocation = 3 'adUseClient. Thanks @HansUp

Add the above line before using r.Open.
The CursorLocation is adUseServer. As a result, records are fetched as you progress (similar to .net datareader). Changing it to adUseClient will bring all records on the client side, which will give correct RecordCount.

EDIT: Also, it isn't meaningful to store recordset in session. And, you should close the connection once you are done using it -

conn.Close set conn = Nothing

What is the need to store recordset in session?

edited Feb 18, 2013 at 7:17

answered Feb 18, 2013 at 7:08

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rachcha · Accepted Answer · 2013-02-18 11:14:27Z

1

If you just need the number of record counts then you can give

sqlquery = "SELECT COUNT(*) AS cnt FROM i2cner WHERE Authors LIKE 'Ish%'"

Note that there is no ; in the SQL string. When you have to retrieve the count, you can just have

hits = r.fields("cnt")

OR

hits = r("cnt")

Recordcount is sometimes deceptive, so I don't use it much. I use the above approach every time.

edited Feb 18, 2013 at 11:14

answered Feb 18, 2013 at 4:43

Rachcha

8,8969 gold badges52 silver badges70 bronze badges

1 Comment

Rachcha Over a year ago

Yes. Right. I didn't know this about MS Access. Editing the answer accordingly.

Collectives™ on Stack Overflow

Getting SQL query results in MS Access but not in VBScript

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related