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

[quincin]

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

[CreoN]

Try do it like this instead:

<% 
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...

[quincin]

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!!

[dcarlson]

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:

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

[quincin]

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!

[monte96]

1) Use Server.CreateObject with ASP

2) Specify a client side cursor.

[quincin]

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!!