Record Count question (yes it's a Keyset Cursor)

I'm just doing a basic little test to print out records and a record count, and even though I've set the cursor to adOpenKeyset (both with the CursorType=adOpenKeyset and w/i the objRS.Open properties) and it's still giving me the blasted -1 !!
I've also set the cursor location to client, and done some manipulations there as well.

Here's the code, any thoughts? Much appreciated!

<%
Dim objConn
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString="DSN=A_Test"
objConn.Open

Dim objRS, strSQL
Set objRS=Server.CreateObject("ADODB.Recordset")
strSQL="SELECT * FROM Products"

objRS.Open strSQL, objConn, adOpenKeyset

Response.Write "<B> A Listing of our Products </B><BR>"
Response.Write "Record Count: " & objRS.RecordCount & "<BR>"

Do While Not objRS.EOF
Response.Write objRS("Brand") & " " & objRS("Model") & "<BR>"
objRS.MoveNext
Loop

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

Try do it like this instead:

Code:

---

<%
Dim objConn
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString="DSN=A_Test"
objConn.Open

strSQL="SELECT * FROM Products"
strSQL2 = "SELECT Count(*) as ProdCount FROM Products"

set objRs = objConn.Execute(strSQL)
set objRs2 = objConn.Execute(strSQL2)

Response.Write "<B> A Listing of our Products </B><BR>"
Response.Write "Record Count: " & objRS2("ProdCount") & "<BR>"

Do While Not objRS.EOF
    Response.Write objRS("Brand") & " " & objRS("Model") & "<BR>"
    objRS.MoveNext
Loop

objRS.Close
Set objRS = Nothing
objRs2.Close
Set objRs2 = Nothing
objConn.Close
Set objConn = Nothing
%>

---

I haven't tested this code now, but it should be working. Let me know if it doesn't...

but I wonder why the recordcount property does not, even though I think I've got all the cursor settings correct.....

It's just the principle of knowing why :-)

In the meantime though, I will use your code so I can keep going with my happy little code!

Thanks very much!!

If you want to use the RecordCount property you can also use adOpenStatic.

Another option (if you're interested) with a forwardonly recordset, would be as follows:

Code:

---

Dim objConn
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString="DSN=A_Test"
objConn.Open

Dim objRS, strSQL

Dim I, strList

Set objRS=Server.CreateObject("ADODB.Recordset")
strSQL="SELECT * FROM Products"

objRS.Open strSQL, objConn, adOpenForwardOnly

I = 1
Do While Not objRS.EOF
  strList = strList & objRS("Brand") & " " & objRS("Model") & "<BR>"
  objRS.MoveNext
  I = I + 1
Loop

Response.Write "<B> A Listing of our Products </B><BR>"
Response.Write "Record Count: " & I & "<BR>"
Response.Write strList

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

---

I've tried w/ the cursor set to adOpenStatic, and get the same -1... It makes me wonder if it's something in my connection string or the provider settings that doesn't recognize that property.

The looping works as well, though, so at least I have options!!

Thanks for the help!

1) Use Server.CreateObject with ASP

2) Specify a client side cursor.

That worked!! I thought I'd specified client-side cursor before, but I guess it was "user error" :)

Thanks very much for your help, this one was stupid and it was bugging me!!