passing multiple parameters to a Stored Procedure
 

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

passing multiple parameters to a Stored Procedure
 

The easiest way is a structu

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"
schreef in bericht
...
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

passing multiple parameters to a Stored Procedure
 

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