How do you call a stored procedure that returns a value?

[Dave]

I have an ADP which connects to SQL Server. I have a stored procedure that
has 2 required input parameters, 4 optional input parameters and 1 return
parameter. When I execute the code I get the following error: Procedure or
function sp_Create_Report_Data expects parameter @RecordsCreated which was
not supplied.

Here is the code:
Set adoCmd = New ADODB.Command
Set adoParam = New ADODB.Parameter
Set adoRS = New ADODB.Recordset

Dim RecordsCreated as integer

With adoCmd 'set the connection last
Set .ActiveConnection = CurrentProject.Connection
'create the return parameter
'Set adoParam = .CreateParameter("@RecordsCreated", adInteger,
adParamReturnValue)
'.Parameters.Append adoParam
.Parameters.Append adoCmd.CreateParameter("@RecordsCreated",
adInteger, adParamReturnValue)
'create and append the input parameters
Set adoParam = .CreateParameter("@FiscalYear", adVarChar,
adParamInput, Len(FiscalYear), FiscalYear)
.Parameters.Append adoParam
Set adoParam = .CreateParameter("@UserName", adVarChar,
adParamInput, Len(UserName), UserName)
.Parameters.Append adoParam
If Len(PhysicianId) > 0 Then
Set adoParam = .CreateParameter("@PhysicianId", adVarChar,
adParamInput, Len(PhysicianId), PhysicianId)
.Parameters.Append adoParam
End If
If Len(Department) > 0 Then
Set adoParam = .CreateParameter("@Department", adVarChar,
adParamInput, Len(Department), Department)
.Parameters.Append adoParam
End If
If Len(Division) > 0 Then
Set adoParam = .CreateParameter("@Divison", adVarChar,
adParamInput, Len(Division), Division)
.Parameters.Append adoParam
End If
If Len(Speciality) > 0 Then
Set adoParam = .CreateParameter("@Speciality", adVarChar,
adParamInput, Len(Speciality), Speciality)
.Parameters.Append adoParam
End If



'specify a stored prcoedure
.CommandType = adCmdStoredProc
'Brackets must surround stored procedure names with spaces
.CommandText = "sp_Create_Report_Data"

'execute the the stored procedure
.Execute

'get the return value
RecordsCreated = .Parameters("@RecordsCreated").Value


Not sure what the problem is. The stored procedure runs without any problems
when I execute from the SQL Management console. Also, is it necessary to
include the @ when creating the ADO parameters?

 

[Tom van Stiphout]

On Tue, 16 Dec 2008 16:55:01 -0800, Dave

Your code looks OK at first sight. Run Profiler and see what Access is
sending to SQL Server.

-Tom.
Microsoft Access MVP

 

[Dave]

I found the problem. I needed to create a unique parameter object for each
parameter and then pass null values for each parameter as needed resolved the
problem. So it is a bit more verbose but at least it works.

Dave

 

[Tom van Stiphout]

On Wed, 17 Dec 2008 07:40:10 -0800, Dave

Can you post your improved code?

-Tom.
Microsoft Access MVP