passing multiple parameters to a Stored Procedure

[Guest]

I have the following code which works fine to pass a parameter to a stored
procedure but how do i allow it to pass a second integer value to the same
procedure which has now been rewritten accomodate it?

Dim ADOCmd As New ADODB.Command
Dim ADOPrm As New ADODB.Parameter
Dim ADORs As ADODB.Recordset
Dim ADOCon As ADODB.Connection
Dim sParmName As String
Dim strConnect As String
Dim rStr As String
Dim X As Double
Dim rs As ADODB.Recordset
Dim ReportDate As String
Dim currMonth As Integer
Dim currMonthStr As String

ReportFirstRow = "A" & StartingRow
ReportFirstColumn = 1

currMonth = Combo_StartMonth.ListIndex + 1
If (currMonth < 10) Then
currMonthStr = "0" & CStr(currMonth)
Else
currMonthStr = CStr(currMonth)
End If

ReportDate = CStr(Combo_SelectYear.Text) & "-" & currMonthStr

'On Error GoTo ErrHandler

'Set Connection
strConnect = "Provider=SQLOLEDB;Initial Catalog=HeadlinePerformance;User
ID=MSNToolUser;Password=ToolUser;Data Source=sapcdsql02;"

Set ADOCon = New ADODB.Connection

With ADOCon
.CursorLocation = adUseServer 'Must use Server side cursor.
.ConnectionString = strConnect
.Open
End With

'Set Command
Set ADOCmd.ActiveConnection = ADOCon
With ADOCmd
.CommandType = adCmdStoredProc
.CommandText = "prc_RHP_CampaignsByChannel_new"
End With

'Set Parameters
sParmName = "@ReportDate"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adVarChar, _
adParamInput, 8, ReportDate)
ADOCmd.Parameters.Append ADOPrm

'Execute Query
Set ADORs = ADOCmd.Execute

With ADORs

' Copy the records into cells
Worksheets("Campaigns By
Channel").Range(ReportFirstRow).CopyFromRecordset ADORs

End With

GoTo Shutdown

ErrHandler:
Call ErrHandler(ADOCon)
Resume Next

Shutdown:

Set ADOPrm = Nothing
Set ADORs = Nothing
Set ADOCmd = Nothing
Set ADOCon = Nothing
Set rs = Nothing
End Sub

thanks for any help in advance,

Amit

 

[moon]

The easiest way is a structure:

Private Type MyType
FirstName As String
LastName As String
End Type

Public Sub SetParams()
Dim MT As MyType
MT.FirstName = "Joe"
MT.LastName = "Blogg"
ShowParams MT
End Sub

Private Sub ShowParams(ByRef CTX As MyType)
MsgBox CTX.FirstName
MsgBox CTX.LastName
End Sub






"DowningDevelopments" <[email protected]>
schreef in bericht
news:[email protected]...

 

[Guest]

hello all,
I solved this by using another append parameter statement to add a value
(sParm2Name) to the ADOcmd.Parameters object using the following code:

'Set Parameters
sParmName = "@ReportDate"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adVarChar, adParamInput,
8, ReportDate)
ADOCmd.Parameters.Append ADOPrm

sParm2Name = "@action"
Set ADOPrm = ADOCmd.CreateParameter("sParm2Name", adInteger,
adParamInput, 4, resultSetNo)
ADOCmd.Parameters.Append ADOPrm

'Execute Query
Set ADORs = ADOCmd.Execute

Amit