Why is SQL Server return a -1 for Recordcount

[MichaelaLee]

Hi Everyone.
I know that if the recordcount is -1, it suppose to mean that this property is not supported. I'm using Microsoft SQL Server 2000 - 8.00.760 (Build 2195: Service Pack 3).
Here is the code:
Set mCommand = New ADODB.Command
mCommand.CommandType = adCmdStoredProc
mCommand.CommandText = "nf_GetInventoryByProgram"
Set mParam = mCommand.CreateParameter("@ProgramId", adVarChar, adParamInput, 15)
mParam.Value = cmbPrograms1.Columns(0).Text
mCommand.Parameters.Append mParam
mCommand.ActiveConnection = ado

rsInventory.CursorLocation = adUseClient
rsInventory.CursorType = adOpenKeyset
rsInventory.LockType = adLockBatchOptimistic
Set rsInventory = mCommand.Execute

After running the execute method ADO/SQL Server changes the setting to the following:
CursorLocation = 2 (adUseServer)
CursorType = 0 (????)
LockType = 1 (adLockReadOnly)
Why is it changing these setting on me. Please help. Thanks for any help.
Michael Lee

 

[MarkSweetland]

Check the connection object properties (ado) and set the cursor location to adUseClient. The object rsInventory is receiving the recordset returned from the command object which is executed with the settings of the connection object "ado"--setting the cursor location of the receiving recordset has no effect on how the command object will return the recordset.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[chiph]

You can't really trust the RecordCount property -- sometimes you get -1 even when using client-side cursors. Very annoying.

I would use a SELECT COUNT(*) to return your record count. You can combine this with your other query and use ADO's NextResultSet method:

SELECT COUNT(*)
FROM tbl_MyTable
WHERE <criteria>;
SELECT Name, Address
FROM tbl_MyTable
WHERE <same criteria>

The semi-colon separates the result sets. The first result set you get back has your count, the next one has your data.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[MichaelaLee]

Hi Mark,
Thanks for the reply. That makes since but there is no CursorLoaction property for the Command Object. Was you talking about another object. THanks again for the reply.
Michael

 

[MarkSweetland]

I'm assuming that ado is an ADODB.Connection object:

Dim ado As ADODB.Connection
Dim mCommand As ADODB.Command

Set ado = New ADODB.Connection
With ado
    .Provider = "ProviderInfo"
    .CursorLocation = adUseClient
    .ConnectionString = "ConnString"
    .Open
End With

Set mCommand = New ADODB.Command
mCommand.CommandType = adCmdStoredProc
mCommand.CommandText = "nf_GetInventoryByProgram"
Set mParam = mCommand.CreateParameter("@ProgramId", adVarChar, adParamInput, 15)
mParam.Value = cmbPrograms1.Columns(0).Text
mCommand.Parameters.Append mParam
mCommand.ActiveConnection = ado

[green]'rsInventory.CursorLocation = adUseClient[/green]
rsInventory.CursorType = adOpenKeyset
rsInventory.LockType = adLockBatchOptimistic
Set rsInventory = mCommand.Execute

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[MichaelaLee]

Hi Mark,
I'm sorry, I miss read your reply. I though I saw Command object and not connection object. Thanks again for your swift reply. I finally found the cursorloaction property of the connection object. I will give that a try and see what happens. THanks again.
Michael

 

[MichaelaLee]

Hi Mark,
I tried your suggestion and the Cursorlocation works file now, BUT the Locktype and cursortype changes on me when the command mCommand.execute is run. Now the properties are being set to:
CursorType = adOpenStatic
LockType = asLockReadOnly
This is a problem, as I will need to update the recordset. But I guess I could still just use Stored Procedures and then refresh/Requery the database after the operation is done.
Does these property setting changing make since to you. Thanks again.
Michael