Passing several parameters via code

  • Thread starter Thread starter Davy
  • Start date Start date
D

Davy

I am trying to execute an UPDATE stored procedure, from
code, that requires 3 parameters. I tried the following
two line items but get an error with both of them.

cn.Execute "StoredProcedure", Parameters = Array
(ReqAmount, Forms![Requisition List View (Detail)]!
[Purchase Order Number], ReqAmount)

cn.Execute "StoredProcedure '" & ReqAmount & "'", "'" &
Forms![Requisition List View (Detail)]![Purchase Order
Number] & "'", "'" & ReqAmount & "'"

Which or what should I use? What mistake(s) am I making?

Help

Davy
 
D> cn.Execute "StoredProcedure", Parameters = Array
D> (ReqAmount, Forms![Requisition List View (Detail)]!
D> [Purchase Order Number], ReqAmount)

should be

cn.execute "storedprocedure(parm1,parm2,...)"

non-muneric parms may need to be enclosed in quotes.

Vadim
 
Hello Davy

You can use:

Set spMyProcedure = New ADODB.Command
With spMyProcedure
.CommandType = adCmdStoredProc
.CommandText = "spa_Proc"
Set .ActiveConnection = CurrentProject.Connection
.Parameters.Append .CreateParameter("@Param1", adVarChar,
adParamInput, 10)
.Parameters.Append .CreateParameter("@Param2", adVarChar,
adParamInput, 10)
.Parameters.Append .CreateParameter("@Param3", adInteger,
adParamInput)

.Parameters("@Param1") = strParam1
.Parameters("@Param2") = strParam2
.Parameters("@Param3") = intParam3

.Execute
End With

Or

Set spMyProcedure = New ADODB.Command
With spMyProcedure
.CommandType = adCmdStoredProc
.CommandText = "spa_Proc"
Set .ActiveConnection = CurrentProject.Connection
.Execute , Array(strParam1, strParam2, intParam3)
End With

Regards
 
you dont need the (parenthesis) in the TSQL

cnn.execute "EXEC myStoredproc PARAM1, 'param2', param3"
 
Hello (e-mail address removed):
You wrote in conference microsoft.public.access.adp.sqlserver on Mon, 1 Mar
2004 14:52:25 -0800:

ak> you dont need the (parenthesis) in the TSQL

but they don't hurt, do they?

Vadim
 
Back
Top