CFrasnelly
asked on
Access ADO Recordset via Stored Procedure - Multiple Paramaters
Hi All,
I am trying to figure out how to populate a recordset via a stored procedure with 4 parameters. Any help would be GREATLY appreciated. The stored procedure has 4 parameters @5,@4,@3,@2. The attached code returns no records
I am trying to figure out how to populate a recordset via a stored procedure with 4 parameters. Any help would be GREATLY appreciated. The stored procedure has 4 parameters @5,@4,@3,@2. The attached code returns no records
Dim rsGP As New ADODB.Recordset
Dim cnGP As New ADODB.Connection
Dim cmdGP As New ADODB.Command
cnGP.Open "Provider=sqloledb;Password=1234;Persist Security Info=True;User ID=User;Initial Catalog=Database;Data Source=Server"
cmdGP.ActiveConnection = cnGP
cmdGP.CommandText = "rptTractProjectCost"
cmdGP.CommandType = adCmdStoredProc
With cmdGP
.Parameters.Append .CreateParameter("@5", adChar, adParamInput, 5, "0250")
.Parameters.Append .CreateParameter("@4", adChar, adParamInput, 5, Null)
.Parameters.Append .CreateParameter("@3", adChar, adParamInput, 5, Null)
.Parameters.Append .CreateParameter("@2", adChar, adParamInput, 5, Null)
'set the recordset
Set rsGP = .Execute
rsGP.MoveFirst
End With
MsgBox rsGP.RecordCount
cnGP.Close
Set rsGP = Nothing
Set cnGP = Nothing
ASKER
If I run the stored procedure on the server itself it works fine??? See attached image.
StoredProcedure.jpg
StoredProcedure.jpg
Do you get any error ?
sub update
On error goto errorHandler
Dim rsGP As New ADODB.Recordset
Dim cnGP As New ADODB.Connection
Dim cmdGP As New ADODB.Command
cnGP.Open "Provider=sqloledb;Password=1234;Persist Security Info=True;User ID=User;Initial Catalog=Database;Data Source=Server"
cmdGP.ActiveConnection = cnGP
cmdGP.CommandText = "rptTractProjectCost"
cmdGP.CommandType = adCmdStoredProc
With cmdGP
.Parameters.Append .CreateParameter("@5", adChar, adParamInput, 5, "0250")
.Parameters.Append .CreateParameter("@4", adChar, adParamInput, 5, Null)
.Parameters.Append .CreateParameter("@3", adChar, adParamInput, 5, Null)
.Parameters.Append .CreateParameter("@2", adChar, adParamInput, 5, Null)
'set the recordset
Set rsGP = .Execute
rsGP.MoveFirst
End With
MsgBox rsGP.RecordCount
cnGP.Close
Set rsGP = Nothing
Set cnGP = Nothing
exit sub
ErrorHandler:
msgbox err.number & err.description
end sub
ASKER
I just get a run-time error '3021' Either BOF or EOF is True
I must be setting up the parameters incorrect???? But I'm not familiar with the syntax to do this.
I must be setting up the parameters incorrect???? But I'm not familiar with the syntax to do this.
I think that the major problem lies in the way you open your recordset
Using ADO and stored procedures
http://www.vb6.us/tutorials/using-ado-and-stored-procedures-vb6
Visual Basic 6 ADO Tutorial
http://www.timesheetsmts.com/adotutorial.htm
Using ADO and stored procedures
http://www.vb6.us/tutorials/using-ado-and-stored-procedures-vb6
Visual Basic 6 ADO Tutorial
http://www.timesheetsmts.com/adotutorial.htm
Dim cnGP As New ADODB.Connection
Dim cmdGP As New ADODB.Command
dim rsGP As New ADODB.Recordset
cnGP.Connectionstring = "Provider=sqloledb;Password=1234;Persist Security Info=True;User ID=User;Initial Catalog=Database;Data Source=Server"
cnGP.Open
With cmdGP
.ActiveConnection = cnGP
.CommandText = "rptTractProjectCost"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@5", adChar, adParamInput, 5, "0250")
.Parameters.Append .CreateParameter("@4", adChar, adParamInput, 5, Null)
.Parameters.Append .CreateParameter("@3", adChar, adParamInput, 5, Null)
.Parameters.Append .CreateParameter("@2", adChar, adParamInput, 5, Null)
End With
With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With
If rstRecordSet.EOF = False Then
rsGP.MoveFirst
end if
MsgBox rsGP.RecordCount
cnGP.Close
Set rsGP = Nothing
Set cnGP = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> same order they are in the actual Stored Procedure on the SQL Server
That's true, because SQL-server does not work with named parameters
That's true, because SQL-server does not work with named parameters
Hi,
sorry - but that's wrong. SQL Server uses of course named parameters - otherwise the execution of the demo screenshot would not work.
To load the parameters into the ADO parameter collection you simply need to use .parameters.refresh, this loads names and datatypes into the parameter collection and then you can normally fill in the values.
I wrote the attached code for my databases to do that. This one executes a stored procedure and returns the recordset and additionally saves an optional return value into a class variable (the function ist inside a class). "clsvar_strLastSQL" is a variable which saves the last SQL command for any of these functions into a class property. "ADOOpenConnection" is a property of the class which returns "OK" if it could be opened. The connection object is hold in the class variable clsvar_objADOConnection. The parameter "clsvar_lngADORecordCount" is a class variable which should return the affected number of records (will be filled by the ADO execute command). The class variable "clsvar_lngReturnValueSP" will be filled with an optional additional return value from the stored procedure. The rest at the end is for error handling.
Cheers,
Christian
sorry - but that's wrong. SQL Server uses of course named parameters - otherwise the execution of the demo screenshot would not work.
To load the parameters into the ADO parameter collection you simply need to use .parameters.refresh, this loads names and datatypes into the parameter collection and then you can normally fill in the values.
I wrote the attached code for my databases to do that. This one executes a stored procedure and returns the recordset and additionally saves an optional return value into a class variable (the function ist inside a class). "clsvar_strLastSQL" is a variable which saves the last SQL command for any of these functions into a class property. "ADOOpenConnection" is a property of the class which returns "OK" if it could be opened. The connection object is hold in the class variable clsvar_objADOConnection. The parameter "clsvar_lngADORecordCount"
Cheers,
Christian
'---------------------------------------------------------------------------------------
' Procedure : ADOExecuteSP_RS
' Author : Christian Coppes
' Date : 18.06.2008
' Last Change : 16.10.2010
' Purpose : Same as ADOExecuteSP, but returns an ADO recordset generated by the stored procedure
' Parameters : strSPName: Name of the stored procedure on the server
' strParamNameList : Comma-separated list of parameters for the SP
' strParamValueList: Comma-separated list of parameter values for the SP
' Example : ADOExecuteSP_RS "NameOfSP" , "@P1,@P2,@P3" , "V1,V2,V3"
' Returns : obj - ADODB.Recordset
' Returns the recordset generated by the stored procedure
'---------------------------------------------------------------------------------------
'
Public Function ADOExecuteSP_RS(ByVal strSPName As String, _
Optional ByVal strParamNameList As String = "", _
Optional ByVal strParamValueList As String = "") As ADODB.Recordset
Dim cmdADO As ADODB.Command
Dim strParameterList() As String
Dim strValueList() As String
Dim i As Long
clsvar_strObjectError = "OK"
On Error GoTo ADOExecuteSP_RS_Error
If Me.ADOOpenConnection = "OK" Then
Set cmdADO = New ADODB.Command
With cmdADO
.CommandText = strSPName
.CommandType = adCmdStoredProc
.ActiveConnection = clsvar_objADOConnection
' Automatically load the parameters for the stored procedure
' into the parameters collection
.Parameters.Refresh
If Not strParamNameList = "" Then
strParameterList = Split(strParamNameList, ",")
End If
If Not strParamValueList = "" Then
strValueList = Split(strParamValueList, ",")
End If
If Not strParamNameList = "" And _
Not strParamValueList = "" Then
' fill the parameters with values
If .Parameters.Count > 0 Then
For i = 0 To UBound(strParameterList)
.Parameters(strParameterList(i)).Value = strValueList(i)
Next
Else
clsvar_strObjectError = "No Parameters"
End If
End If
' Assign return table to ADO recordset
Set ADOExecuteSP_RS = .Execute(clsvar_lngADORecordCount)
On Error Resume Next
clsvar_strLastSQL = .CommandText
' If an error occurs in the next line the return value will have a value of -1
clsvar_lngReturnValueSP = -1
' @Return_Value is ALWAYS a LONG value!
clsvar_lngReturnValueSP = .Parameters("@RETURN_VALUE")
End With
End If
ADOExecuteSP_RS_Exit:
Exit Function
ADOExecuteSP_RS_Error:
Select Case Err.Number
Case Else
ObjErr(clsvar_objADOConnection).fnErr _
"Class: " & cMODULENAME, _
"Function: ADOExecuteSP_RS"
Me.ADOCloseConnection
clsvar_strObjectError = "ERROR"
End Select
Resume ADOExecuteSP_RS_Exit
End Function
ASKER
I figured out that the parameters had to be in the same order.
exec rptTractProjectCost '0250', null, null, null