Passing parameters to stored procedures in code 2

[zappaBR]

Hi, I've been working as a semi-pro (very "semi" in what matters) access programmer here in Brazil for a couple of years now and I am considering upgrading one client's database to MSDE/Access Project. I'm facing many problems, however. Maybe the more serious by now is that I cannot manage to pass parameters to Stored Procedures in VB code.

I have a stored procedure like this:
------------------------------------------
ALTER PROCEDURE dbo.agentesqry
@codigo varchar(12)
AS SELECT dbo.Agentes.*
FROM dbo.Agentes
WHERE (CODICE = @codigo)
------------------------------------------

Somewhere in my code I try the following:

---------------------------------------------
dim adoConnection As New ADODB.Connection
dim adoRecordset As New ADODB.Recordset
dim adoCommand As New ADODB.Command
dim adoPar As New ADODB.Parameter

adoConnection.Open _
CurrentProject.Connection.ConnectionString
Set adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "agentesqry"

Set adoPar = adoCommand.CreateParameter _
("@codigo",adVarChar, adParamInput, 12, "2030061&quot
adoCommand.Parameters.Append adoPar
adocommand.execute
adoRecordset.Open adoCommand
---------------------------------------------------------
but IT DOESN'T WORK!!! The following message appears:

"Procedure 'agentesqry' expects parameter '@codigo', which is not supplied."


When I put the stored procedure as RecordSource of a form and put the parameter value in the InputParameter property it works fine. I whant the code above so that I can put everything in a "begintrans".

I hope someone can show me what I'm doing wrong.

 

[cmmrfrds]

First, I will give you an example of one of my calls and then try to identify what may be wrong with yours.

EXAMPLE.
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim param4 As Parameter, param5 As Parameter

pubCaseCount = 0
pubInvoiceCount = 0

' Connect
Set cnn = CurrentProject.Connection

' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cnn
cmd.CommandText = "dbo.sp_variousCounts"
cmd.CommandType = adCmdStoredProc

' Set up input parameters.
Set param1 = cmd.CreateParameter("Input", adDouble, adParamInput)
cmd.Parameters.Append param1
param1.Value = pubEmployeeID
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = pubBegDate
Set param3 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param3
param3.Value = pubEndDate

' Set up output parameters.
Set param4 = cmd.CreateParameter("Output", adDouble, adParamOutput)
cmd.Parameters.Append param4
Set param5 = cmd.CreateParameter("Output", adDouble, adParamOutput)
cmd.Parameters.Append param5

' Execute command to run stored procedure
cmd.Execute

One thing I see missing is the command type and I am not sure what the default is.
adoCommand.CommandType = adCmdStoredProc

Since the stored procedure is returning a recordset do this.
Set adoRecordset = adoCommand.Execute

Also. Always prefix the stored procedure with dbo. Since when the client gets the program they will not be the database owner and the SP will not be recognized.

Post back with results.

 

[zappaBR]

Hi cmmrfrds, I did each of the modifications suggested step by step and the "CommandType" thing made the difference! Now it is working fine, thanks a lot!

 

[cmmrfrds]

You are welcome. I am glad it is working for you.

 

[Nowell]

Hi cmmrfrds,

I have used a few ado command objects recently, do you know whether they have a large overhead?

Meaning is it an efficient way of passing parameters?

 

[cmmrfrds]

My understanding from Microsoft and other literature that the Command Ojbect is the preferred way of handling stored procedures. It provides more options and flexibility and one option is that the parameters can be declared in the vba code to avoid additional overhead of calling sql server to check out the paramters ahead of the call.

 

[Nowell]

Excellent thanks very much,
I just had a c# programmer confirm the same thing to me.

 

[pskettle]

I have been using the code explained in this article to pass some date parameters to my SQL stored procedure however I get the error data ype mismatch on the following line
Set param1 = cmd.CreateParameter("startdate", adDBDate, adParamInput)

The code is in a MS Access 2000 database and not a project file. Can anyone advise of why this error should be occurring.

Many Thanks

Pete

 

[cmmrfrds]

That will be difficult to answer without seeing the declaration and coding for cmd and your stored procedure parameter list. Paste in the first part of your SP with the parameter definitions and any thing else that may be helpful.

 

[pskettle]

The declarations I have made in the Access program are:

Dim startdate As Date
Dim enddate As Date
Dim con As New ADODB.connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim param1 As Parameter
Dim param2 As Parameter

My connection is made using:

strcon = "driver={sql server};server=ABCD;database=Case;uid=XX;pwd=XXXX;DSN=localserver;"
Set cmd = CreateObject("ADODB.Command&quot
cmd.ActiveConnection = strcon

And the Parameter is been setup using:

Set param2 = cmd.CreateParameter("enddate", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = enddate

Repeat above for startdate

My stored Procedure variables are:

@startdate as datetime,
@endDate as datetime

Any ideas would be gratefully appreciated I seem to be going round in circles on this one it looks so simple but I must be missing something. thanks

 

[cmmrfrds]

Try leaving this out.
Set cmd = CreateObject("ADODB.Command&quot
The cmd was already defined and instantiated by.
Dim cmd As New ADODB.Command