How to call Stored Procedure with two Parameters

[Guest]

I have this code that i use to call a stored procedure which works. It
requires one parameter.
I have another stored procedure that requires two different paramters.
How do i call it using ADO?

What do I need to Change in this statement?
I need to add a year parameter.
I already have an account number hard-coded here for testing purposes.
the stored proc is built and I know how to call it in sql server just not in
access.

Where would i put the second parameter e.g. "2006"?


Set rst = cnn.Execute("GetAssocEquipment""1609756""")
--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003

 

[Guest]

I'm actually trying to pass two parameters which are each a comma separated
list. I'm sure the problem is something to do with the quotations. I keep
trying different variations but haven't got it right yet.


Set rst = cnn.Execute("GetAssocInvoiceMast""1609756,1619995,1619986",
"01,06,02,06,03,06,04,06,05,06,06,06,07,06,08,06,09,05,10,05,11,05,12,05""")

--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003

 

[Guest]

I found another approach that works using the command object.

Sub StoredProcedureTwoParam()

Dim cnn As ADODB.Connection
Dim FieldNames As String
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim cmd As New ADODB.Command
Set cnn = New ADODB.Connection

cnn.Open "Provider=SQLOLEDB;data source=SDALSQL2;" & _
"database=Ext_Usr_Rpts;Trusted_Connection=yes;"

Set cmd.ActiveConnection = cnn


cmd.CommandText = "GetAssocInvoiceMast"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0


cmd.Parameters.Refresh


cmd.Parameters("@OrderList").Value = "1609756,1619995,1619986"
cmd.Parameters("@MonthYearList").Value =
"01,06,02,06,03,06,04,06,05,06,06,06,07,06,08,06,09,05,10,05,11,05,12,05"
rs.Open cmd





For Each fld In rst.Fields
FieldNames = FieldNames & " " & fld.Name
Next fld

Debug.Print FieldNames


Do Until rst.EOF = True
Debug.Print rst.Fields(0) & " " & rst.Fields(1) & " " &
rst.Fields(2) & " " & rst.Fields(3) & " " & rst.Fields(4) & " " &
rst.Fields(5) & rst.Fields(6) & " " & rst.Fields(7)
rst.MoveNext
Loop


cnn.Close
Set cnn = Nothing
Set rst = Nothing

End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003