CFrasnelly
asked on
ADO Stored Procedure Issue - VBA
I cannot figure out how to load an ADO Recordset with a Stored Procedure???? Any help would be appreciated. Every time I run the below code I get -1 as the result????
' Create a connection object.
Dim cnPubs As New ADODB.Connection
Dim cmd1 As New ADODB.Command
' Provide the connection string.
cnPubs.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=testtest;Initial Catalog=TimberOps;Data Source=ServerName\SQLExpress"
' Create a recordset object.
Dim rsPubs As New ADODB.Recordset
cmd1.ActiveConnection = cnPubs
cmd1.CommandText = "spUnitHarvestCosts_ByYear"
cmd1.CommandType = adCmdStoredProc
Set rsPubs = cnPubs.Execute("exec spUnitHarvestCosts_ByYear")
'Set rsPubs = cmd1.Execute()
'rsPubs.Open "exec spUnitHarvestCosts_ByYear", cnPubs
MsgBox rsPubs.RecordCount
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
ASKER
That didn't work so I tried it with a different stored procedure and it DID work?!?!? The stored procedure I want it to work with only has 3 fields and seems simple. Any thoughts on why it would work with one SP and not another????? I have literally just copied and pasted the sp name over so it's not that. :)
Thanks!!!
Thanks!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just to make clear - when you say
>> "I cannot figure out how to load an ADO Recordset with a Stored Procedure"
you had absolutely achieved that.
Just because you were getting -1 as the record count doesn't mean the recordset wasn't loaded. :-)
The RecordCount property is just that - a property of the recordset. It's not a physical count of the rows, but something that ADO exposes for your convenience.
The method suggested by Kelvin was such that you are able to open a different cursor type on your recordset. (In ADO the cursor type is fundamental in determining the behaviour and functionality that your recordset will expose).
By allowing an Execute method you'd opened a ForwardOnly cursor type recordset. This doesn't support the RecordCount property (not in a meaningful way at least, it returns -1 always).
The revision offered allowed you to open a Keyset.
This supports a RecordCount, though a complete accurate count isn't guaranteed (if it's something you need accurately then you could include it in your stored procedure and return it as a subsequent recordset or as an Output parameter).
You found it to fail on an aggregate procedure and that is a possibility.
However ensuring that you have a fully loaded recordset (and by that I mean of data not just keys) should force the issue.
You achive this by setting your cursor connection to be client side prior to opening it.
rsPubs.CursorLocation = adUseClient
None the less, using the stored proc is the more robust way.
>> "I cannot figure out how to load an ADO Recordset with a Stored Procedure"
you had absolutely achieved that.
Just because you were getting -1 as the record count doesn't mean the recordset wasn't loaded. :-)
The RecordCount property is just that - a property of the recordset. It's not a physical count of the rows, but something that ADO exposes for your convenience.
The method suggested by Kelvin was such that you are able to open a different cursor type on your recordset. (In ADO the cursor type is fundamental in determining the behaviour and functionality that your recordset will expose).
By allowing an Execute method you'd opened a ForwardOnly cursor type recordset. This doesn't support the RecordCount property (not in a meaningful way at least, it returns -1 always).
The revision offered allowed you to open a Keyset.
This supports a RecordCount, though a complete accurate count isn't guaranteed (if it's something you need accurately then you could include it in your stored procedure and return it as a subsequent recordset or as an Output parameter).
You found it to fail on an aggregate procedure and that is a possibility.
However ensuring that you have a fully loaded recordset (and by that I mean of data not just keys) should force the issue.
You achive this by setting your cursor connection to be client side prior to opening it.
rsPubs.CursorLocation = adUseClient
None the less, using the stored proc is the more robust way.
rsPuubs.LockType = adLockOptimistic
rsPubs.CursorType = adOpenKeyset
rsPubs.Open cmdl
Above should do it for you.
Kelvin