ADO RecordCount doesn't

[MikeR]

When I use the following, the RS.recordcount is -1. I also can't do a RS.moveprevious.
What have I overlooked?
Thanks,
Mike

dbname="DBQ=" & Server.MapPath("../db/my.mdb")
set Conn=server.createobject("adodb.connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbname

sql = "SELECT * FROM Zips Where zipcode = '"& Request("zip") & "'"
response.write "<p>" & sql
set RS = Server.CreateObject("ADODB.recordset")
RS.cursortype = adOpenDynamic
RS.open sql, conn

 

[Bob Barrows [MVP]]

When I use the following, the RS.recordcount is -1. I also can't do a
RS.moveprevious. What have I overlooked?
Thanks,
Mike

dbname="DBQ=" & Server.MapPath("../db/my.mdb")
set Conn=server.createobject("adodb.connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbname

Nothing to do with your problem, but: http://www.aspfaq.com/show.asp?id=2126

sql = "SELECT * FROM Zips Where zipcode = '"& Request("zip") & "'"

Again, nothing to do with your problem, but:
http://www.aspfaq.com/show.asp?id=2096

response.write "<p>" & sql
set RS = Server.CreateObject("ADODB.recordset")
RS.cursortype = adOpenDynamic

You think you're getting a dynamic cursor, but:
http://www.adopenstatic.com/faq/jetcursortypes.asp

RS.open sql, conn

I don't work with Jet, so I am a little surprised that the keyset or static
cursor you are getting does not support recordcount. If you change the
cursorlocation to adUseClient, you are guaranteed to get a cursor that
supports bookmarks and recordcount. However, there are alternatives that
will perform better:
http://www.aspfaq.com/show.asp?id=2193

For a further explanation of the effect of cursor type on record count, see
here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdprorecordcount.asp

Bob Barrows

 

[Bob Lehmann]

Change adOpenDynamic to adOpenKeySet.

Or better yet, stop using select *, and add a column to your query for the
record count and stop using the Open method.

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbname

sql = "SELECT whatever1, whatever2, whatever3, COUNT(*) as record_count FROM
Zips Where zipcode = '"& Request("zip") & "'"

Set rs = conn.Execute(sql)

RecordCount = rs("record_count")

Bob Lehmann

 

[MikeR]

Bobs -
Thank you gentlemen!
Mike