Get SQL query results in MS Access, but not in VBScript
When I execute a SQL query through MS Access, I get the returned results, but if I execute the same query in VBScript, my RecordCount is -1. I cannot tell if this is a connection error. I am not getting anything, but it is clear that SQL is returning results in Access. I am getting 0 hits in the below connection 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
source to share
r.CursorLocation = 3 'adUseClient. Thanks @HansUp
Add the above line before using r.Open
. CursorLocation
- adUseServer
. As a result, records are fetched as they go (similar to .net datareader). Changing it to adUseClient
will write all client side records, which will give the correct one RecordCount
.
EDIT: Also, it doesn't make sense to store a recordset in a session. And you should close the connection as soon as you are done using it -
conn.Close
set conn = Nothing
What is the need to store a set of records in a session?
source to share
If you just need the number of records, then you can give
sqlquery = "SELECT COUNT(*) AS cnt FROM i2cner WHERE Authors LIKE 'Ish%'"
Note that the SQL line does not ;
. When you need to recover an account, you can simply
hits = r.fields("cnt")
OR
hits = r("cnt")
Recordcount
sometimes deceiving, so I don't use it much. I use this approach every time.
source to share