Link to home
Start Free TrialLog in
Avatar of CFrasnelly
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

Open in new window

Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

   Set rsPubs = New ADODB.Recordset
    rsPuubs.LockType = adLockOptimistic
    rsPubs.CursorType = adOpenKeyset
    rsPubs.Open cmdl


Above should do it for you.


Kelvin
Avatar of CFrasnelly
CFrasnelly

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!!!
ASKER CERTIFIED SOLUTION
Avatar of CFrasnelly
CFrasnelly

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.