Problem generating report using stored procedure

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Hi,
I have a stored procedure in sql server.The front end application has data
in sql server. I am trying to use a stored procedure (parameterized) to run a
report. However I am getting an error>
CODE:

Private Sub Report_Open(Cancel As Integer)
'To make all the fields visible

'MarketChannel.Visible = True
'TBSGroup.Visible = True
'Amount.Visible = True
'GLPeriod.Visible = True
'GLYear.Visible = True


Dim cmdSelect As ADODB.Command
Dim strMarketChannel As String
Dim strTBSGroup As String
'Dim strAmount As String
Dim strGLPeriod As String
Dim strGLYear As String
'Dim curAmount As Currency
Set conn = New ADODB.Connection
Set cmdSelect = New ADODB.Command

conn.Provider = "SQLOLEDB"


conn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=TBS;Data
Source=localhost;User ID=sa;Password=test;"
conn.CursorLocation = adUseClient
conn.Open

'Code added

cmdSelect.CommandType = adCmdStoredProc
cmdSelect.CommandText = "sp_select_data"
Set cmdSelect.ActiveConnection = conn

' Get the form values
strMarketChannel = Forms!frmSearchRecords!cboBusinessUnit
strTBSGroup = Forms!frmSearchRecords!cboTbsGroup
strGLPeriod = Forms!frmSearchRecords!cboGLPeriod
strGLYear = Forms!frmSearchRecords!cboTBSYear

strTBSGroup1 = Val(strTBSGroup)
strGLPeriod1 = Val(strGLPeriod)
strGLYear1 = Val(strGLYear)

MsgBox ("Your output data will be based on the following values" & vbCrLf & _
"[Market Channel]: " & strMarketChannel & vbCrLf & "[TBS Group]: " &
strTBSGroup & vbCrLf & "[GL Period]: " & strGLPeriod & vbCrLf & "[GL Year]: "
& strGLYear)


' Add the parameters
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@businessunit",
adVarWChar, adParamInput, 1, strMarketChannel)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@tbsgroup",
adInteger, adParamInput, 4, strTBSGroup1)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@glperiod",
adInteger, adParamInput, 4, strGLPeriod1)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@tbsyear", adInteger,
adParamInput, 4, strGLYear1)
'cmdSelect.Parameters.Append cmdSelect.CreateParameter("@Percent",
adCurrency, adParamInput, , curPercent1)



' Execute the command
Set rs = cmdSelect.Execute
Me.RecordSource = rs


End Sub

The error is in the line Me.RecordSource = rs
It tells type mismatch. I would appreciate any help for resolution of this
issue. Thanks.
 
Back
Top