Returning a recordset and an output parameter
-
I have the following stored procedure which works just fine in QA, returning both the 280,000 row recordset and the corresponding count
CREATE PROCEDURE dbo.stp_ConvertCharges
@RecordCount int output AS
BEGIN
DECLARE @ReturnError int
SET NOCOUNT ON
SELECT chgCaseNumber, chgPartyNumber
FROM Cases sca
LEFT OUTER JOIN Charges sch ON
sca.CasCaseNumber = sch.ChgCaseNumber
LEFT OUTER JOIN CaseParty cp ON
sca.CasCaseNumber = cp.CaseID
LEFT OUTER JOIN uCaseType uCT ON
sca.CasCaseTypeCode = uCT.CaseUTypeID
INNER JOIN sCaseType sCT ON
uCT.CaseTypeKy = sCT.CaseTypeKy
LEFT OUTER JOIN uExtConn uEC ON
cp.ExtConnID = uEC.ExtConnID
LEFT OUTER JOIN uOffense uO ON
sch.ChgStatuteNum = uO.Statute
WHERE sCT.CaseCategoryKy = 'CR'
ORDER BY sca.CasCaseNumber
SELECT @RecordCount = @@RowCount, @ReturnError = @@Error
RETURN @ReturnError
END
GO
I have the following VB6 routine that attempts to run the stored procedure to return the recordset and the output parameters
Private Sub S030_RetrieveData()
Dim cmdSubCommand As ADODB.Command
m_intDataReturnError = 99
m_lngDataRecordCount = 99
Err.Clear
Set g_rsData = New ADODB.Recordset
g_rsData.CursorLocation = adUseClient
g_rsData.CursorType = adOpenStatic
g_rsData.LockType = adLockReadOnly
Set cmdSubCommand = New ADODB.Command
If Err.Number > 0 Then
Exit Sub
End If
With cmdSubCommand
.CommandType = adCmdStoredProc
.CommandTimeout = 300
.CommandText = "stp_ConvertCharges"
.ActiveConnection = strDBConnectionString
.Parameters.Append .CreateParameter("ReturnError", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("RecordCount", adInteger, adParamOutput)
Set g_rsData = .Execute()
.ActiveConnection = Nothing
m_lngDataRecordCount = .Parameters("RecordCount").Value
m_intDataReturnError = .Parameters("ReturnError").Value
End With
MsgBox ("Record Count = " & m_lngDataRecordCount & " ReturnError = " & m_intDataReturnError)
Set cmdSubCommand = Nothing
End Sub
The VB returns the recordset just fine, but the output parameters both come back as zeroes. Can anyone see what's wrong with it?
Thanks.
MattieNH
-
Did you try closing the connection before trying to access the parameters? I vaguely remember that you hav to CLOSE something (either the cn or rs but I'm pretty sure it's the cn) before you can access any output parameter.
But first I would run the sp in qa to make sure that the problem doesn't come from there. Then I would try closing the connection before trying to fetch the recordcount.
One other option would be not to use the set nocount on option and use the recordset recordcount property if possible.
-
Thanks for responding. I'm not sure how to close the connection, since I don't explicitly create and open one in this routine. I did create a connection, use it to delete all the records from an exception table, and close it in a previous routine.
I've read that you have to close the recordset to access the output variables, and I tried that before retrieving the parameters, as in
g_rsData.close
m_lngSustainDataRecordCount = .Parameters("RecordCount").Value
m_intSustainDataReturnError = .Parameters("ReturnError").Value
End With
but that didn't work.
I have run the SP in Query Analyzer, and it works just fine. And with or without SET NOCOUNT ON, I can't get anything but a -1 for a record count.
What would the syntax be to close the implicit connection created by the command object?
MattieNH
-
Hmm this can be a problem
.ActiveConnection = strDBConnectionString
You could try setting ActiveConnection to an adodb.connection object and close that object before accessing the params to see if it works.
As for the recordcount not working you'd have to change to cursor type or locktype... don't remember which one causes this behavior.
-
You could try to open the recordset in an alternative way:
g_rsData.Open cmdSubCommand, , adOpenStatic, adLockReadOnly
Set g_rsData.ActiveConnection = Nothing
m_lngDataRecordCount = cmdSubCommand.Parameters("@RecordCount")
This does the trick in my applications.
Erik
-
Actually this is one of those gotcha items.
The OUPUT parameters are only available after the recordset is processed.
User a client side cursor and do a MoveLast then get your parameters and MoveFirst on the recordset, this should do it.
-
'Gotcha' is a polite term for what this is.
Part of me hates to ask this, but the part of me that's spent two days on this is desperate. Apparently I can't do a MoveLast because even though I explicitly open the recordset as client side (g_rsSustainData.CursorLocation = adUseClient), the command object will only open a ForwardOnly recordset with the Execute method. I know how to use the rs.open method, but not in conjunction with the the command object, which is the only way I know how to pass and retrieve parameters.
I would really appreciate it if you could show me exactly what the code should be to call a stored procedure with parameters and still return a dynamic recordset.
Thanks for all your help.
Mattie
-
You can't exactly do what you're doing in VB6. In .NET it might be Ok.
You should change your Procedure to this:
- CREATE PROCEDURE dbo.stp_ConvertCharges
@Errors int output = 0
AS
- SET NOCOUNT ON
BEGIN
SELECT chgCaseNumber, chgPartyNumber
FROM Cases sca
LEFT OUTER JOIN Charges sch ON sca.CasCaseNumber = sch.ChgCaseNumber
LEFT OUTER JOIN CaseParty cp ON sca.CasCaseNumber = cp.CaseID
LEFT OUTER JOIN uCaseType uCT ON sca.CasCaseTypeCode = uCT.CaseUTypeID
INNER JOIN sCaseType sCT ON uCT.CaseTypeKy = sCT.CaseTypeKy
LEFT OUTER JOIN uExtConn uEC ON cp.ExtConnID = uEC.ExtConnID
LEFT OUTER JOIN uOffense uO ON sch.ChgStatuteNum = uO.Statute
WHERE sCT.CaseCategoryKy = 'CR'
ORDER BY sca.CasCaseNumber
- SET @Errors = @@Error
RETURN
END
SET NOCOUNT OFF
GO
And your VB Code should look like this:
On Error GoTo ErrHandler
Dim adoCon As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim rsData As ADODB.Recordset
Dim nRecords As Long, nError As Long
'Open the Connection
Set adoCon = New ADODB.Connection
adoCon.Open sConnectString
'Initialize the Command
Set adoCmd = New ADODB.Command
With adoCmd
.CommandType = adCmdStoredProc
.CommandText = "spMyProc"
Set .ActiveConnection = adoCon
'There is no need to Add any Parameters
'The ADO Command Object intrinsicly knows the Parameters
'because it has a Connection to the Database
'The 'nRecords' variable will return the number of Records affected
'This eliminates the need for an Output Paramters telling you how many Records were affected
Set rsData = .Execute(nRecords)
nError = .Parameters("@Errors").Value
End With
While Not rsData.EOF
'Do something...
rsData.MoveNext
Wend
ErrHandler:
If (Err.Number <> 0) Then MsgBox Err.Description
'CleanUp
If (Not rsData Is Nothing) Then rsData.Close
If (Not adoCon Is Nothing) Then adoCon.Close
Set rsData = Nothing: Set adoCmd = Nothing: Set adoCon = Nothing
Also, DO NOT CLOSE the Connection until you are finished with the Recordset. Any Recordset opened through a Command Object will be ForwardOnly with a ServerSide Cursor. You cannot change that.
By using the ADODB.Connection variable, you are ensured that your connection will not close after the Command has been executed. Sure, it's one more variable to cleanup, but it's not that big a difference.
- CREATE PROCEDURE dbo.stp_ConvertCharges
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply