Parameter array to SQL stored procedure

[mhall]

With cn
'Establish DSN-less connection
.ConnectionString = ConnectionString
.ConnectionTimeout = 10
.Open
End With

Set Qr.ActiveConnection = cn
Qr.CommandText = "sp_get_comments"
Qr.CommandType = adCmdStoredProc

'create parameter code
Set ordno = Qr.CreateParameter("@ordno", adChar, adParamInput, 10)
ordno.Value = frmParts.txtOrderNo.Text

Can anyone help

I am accessing a stored procedure with three input parameters, I can pass one parameter using the code below but how do you pass an array of parameters. I've also tried using the append method to add the parameters, when I run the sp the first time it works, but if I change the vales for the parameters on the input sreen and run the SP again I get an error message saying too many parameters supplied.


'create parameter code
Set partno = Qr.CreateParameter("@partno", adChar, adParamInput, 20)
partno.Value = frmParts.flxParts.TextMatrix(frmParts.flxParts.RowSel, 0)

'create parameter code
Set duedate = Qr.CreateParameter("@duedate", adChar, adParamInput, 8)
duedate.Value = frmParts.flxParts.TextMatrix(frmParts.flxParts.RowSel, 3)

Set rs = Qr.Execute("sp_get_parts", ord_no)

basically all I'm asking for is some sample code that shows me how to pass more than one parameter to a sql stored procedure.

Any help would be greatly appreciated

thanks

Matt

 

[Terpsfan]

I've used this code to execute a stored procedure. There are a million different ways to make it happen. It's all a matter of preference. Here are two ways to do it-both involve creating a command object. The first assumes you have text boxes with these names affixed to them. Just replace it with the control names you use for these fields.


Dim objComm As ADODB.Command
Dim pr_Parameters(2) As Variant
Set objComm = New ADODB.Command

With objComm
.ActiveConnection = cn
.CommandText = "sp_get_comments"
.CommandType = adCmdStoredProc
End With

pr_Parameters(0) = txtOrdNo
pr_Parameters(1) = txtPartNo
pr_Parameters(2) = txtDueDate

objComm.Execute , pr_Parameters

OR do this

Set cmd = New ADODB.Command
With cmd
.ActiveConnection =
.CommandText = "EXEC sp_get_comments 10,20,8"
.CommandType = adCmdText
.Execute
End With