ADO command object - cursor type problem

[JamesLean]

This is what I'm trying to do:

- set up a command object to execute a stored proc, passing in a couple of parameters
- execute the command and return a recordset
- get the record count of the recordset

I can execute the command and get the recordset back no probs. The problem I'm having is setting the cursor type of the recordset to static, so I can get the record count.

This is the code to set the command up:

Set oCmd = Server.CreateObject("ADODB.Command")
With oCmd
  .ActiveConnection = oConn
  .CommandType = adCmdStoredProc
  .CommandText = "myproc"
	
  .Parameters.Append(.CreateParameter("@p1", adInteger, adParamInput,, 10))
  .Parameters.Append(.CreateParameter("@p2", adBoolean, adParamInput,, True))
End With

Set oRS = oCmd.Execute

This works fine and I can loop through the recordset as normal and output a standard HTML table. But when I try and output the RecordCount property, all I get is -1.

I have tried explicitly setting the cursor type using:

Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.CursorType = adOpenStatic
oRS.Open oCmd

But I get the same thing. Even if I try looping through the recordset and counting the records manually by incrementing a variable, I get the following error:

Microsoft OLE DB Provider for SQL Server (0x80040E18)
Rowset position cannot be restarted.

This error occurs on the line where I try and go back to the start of the recordset ready to output the table.

For some reason it seems to be ignoring the request for a static cursor. I am executing this against a SQL Server 7 database. Does anyone know how I can get the record count (either through the property or looping through)?

--James

 

[JamesLean]

FYI, I've just solved this.

All I did was change the connection to use client-side cursors:

oConn.CursorLocation = adUseClient

--James

 

[SQL2KDBA69]

cursors sucks they drain your resourses use batches