Executing(ADO) Stored Procedure with Multiple Parameters

[Jen123]

Hi

I'm trying to execute store procedure with multiple input parameters but sql server isn't recognising the parameters that I am passing.

The error message is "procedure ....expects parameter @Period, which was not supplied. It's breaking on the execute bit.

I've tried naming the parameter with and without @in front

Has anyone else had this problem before and know how to fix it???

I'm using SQL Server 2000 and Access 2002Code attached

Thanks

Private Sub Command1_Click()
Dim cnn1 As ADODB.Connection
Dim rstClients As ADODB.Recordset
Dim adoCommand As ADODB.Command
Dim prmPeriod As ADODB.Parameter
Dim prmOffice As ADODB.Parameter
Dim prmDept As ADODB.Parameter

' Open connection.

Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
'opens command for stored proc
Set adoCommand = New ADODB.Command
Set adoCommand.ActiveConnection = cnn1
adoCommand.CommandText = "_StoredProcName"
adoCommand.CommandType = adCmdStoredProc
adoCommand.CommandTimeout = 15

'set paremeters
'adocommand.
Set prmPeriod = adoCommand.CreateParameter("@Period", adInteger, adParamInput)
adoCommand.Parameters.Append prmPeriod
prmPeriod.Value = 200312

Set prmOffice = adoCommand.CreateParameter("@Office", adChar, adParamInput, 8)
adoCommand.Parameters.Append prmOffice
prmPeriod.Value = LON

Set prmDept = adoCommand.CreateParameter("@Dept", adInteger, adParamInput, 8)
adoCommand.Parameters.Append prmDept
prmDept = ALL


Set rstClients = adoCommand.Execute

rstEmployees.Close
cnn1.Close

 

[Axoliien]

Where's the actual stored procedure?

 

[Axoliien]

Just as a quick note, the prmPeriod is being set twice.

 

[PHV]

Have you tried to replace this:
prmPeriod.Value = LON
By this:
prmOffice.Value = "LON"
And this:
prmDept = ALL
By this:
prmDept = "ALL"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jen123]

Thank you Axoliien, I hadn't noticed that I had set the prmPeriod twice............. doh!

 

[VBslammer]

My understanding is that the parameters you add to the parameters collection of the command object must appear in the same order as the parameters within the stored procedure, and that the name of the variables you assign to them don't matter - only the position, data type, size and parameter type.

You can avoid creating the parameters altogether by using:

adoCommand.Parameters.Refresh

...after you assign the stored procedure name, which will retrieve the parameters for you, albeit at the expense of a trip to the server. Once refreshed, you can assign the values using their server names:

adoCommand.Parameters("@Period") = 200312

If you do create the parameters in code, I would also assign values to the parameters before adding them to the collection. Typically, you would only use a single parameter variable and reuse it for each entry:

Dim prm As ADODB.Parameter

Set prm = adoCommand.CreateParameter("period", adInteger, adParamInput,,200312)
adoCommand.Paramters.Append prm

Set prm = adoCommand.CreateParameter("office", adChar, adParamInput,8,"LON")
adoCommand.Paramters.Append prm

'etc.

VBSlammer

Unemployed in Houston, Texas

 

[Jen123]

All working fine and dandy now

Thanks for all the tips