ADO Recrodset Error 3001 with command parameters

[franklin1232]

Below is the statement used to execute a stored procedure from VBA using ADO with command parameters. I captured it from the SQL Profiler while debugging my code.

exec sp_GrossIntervalSalesReport 1, 0, 'Dec 31 2007 12:00:00:000AM', 'Jan 27 2008 12:00:00:000AM', 3, 0, 2006, 3, 0, 2006, 0, 1, 2, -5

If I copy and paste this line into SQL Query Analyzer and hit go it will return the data. So the parameters appear to be valid. However VBA throw run-time error 3001: Arguments are of the wrong type, aro out of acctaple range. or are in conflict with one another. Is this error not a problem with the command object but assigning it to the recordeset.

stProcName = "sp_GrossIntervalSalesReport" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdStoredProc 'Define the ADODB command
cmd.ActiveConnection = cnt 'Set the command connection string
cmd.CommandText = stProcName 'Define Stored Procedure to run


'set parameters to be executed
'myVariable = myForm.myControl.Value
Set prmGross = cmd.CreateParameter("@Gross", adTinyInt, adParamInput, 1, 1)
Set prmReportType = cmd.CreateParameter("@ReportType", adTinyInt, adParamInput, 1, 0)
Set prmDateStart = cmd.CreateParameter("@DateStart", adDBDate, adParamInput, , MyStartDate)
Set prmDateEnd = cmd.CreateParameter("@DateEnd", adDBDate, adParamInput, , MyEndDate)
Set prmPeriodMonth1 = cmd.CreateParameter("@PeriodMonth1", adInteger, adParamInput, 1, 3)
Set prmWeekOFPeriod1 = cmd.CreateParameter("@WeekOFPeriod1", adInteger, adParamInput, 1, 0)
Set prmYearOfPeriod1 = cmd.CreateParameter("@myYearOFPeriod1", adInteger, adParamInput, 4, 2006)
Set prmPeriodMonth2 = cmd.CreateParameter("@PeriodMonth2", adInteger, adParamInput, 1, 3)
Set prmWeekOFPeriod2 = cmd.CreateParameter("@WeekOFPeriod2", adInteger, adParamInput, 1, 0)
Set prmYearOfPeriod2 = cmd.CreateParameter("@YearOFPeriod2", adInteger, adParamInput, 1, 2006)
Set prmSummary = cmd.CreateParameter("@Summary", adTinyInt, adParamInput, 1, 0)
Set prmGlobalCat = cmd.CreateParameter("@GlobalCat", adTinyInt, adParamInput, 1, 1)
Set prmInterval = cmd.CreateParameter("@Interval", adTinyInt, adParamInput, 1, 2)
Set prmDeptNo = cmd.CreateParameter("@DeptNo", adSmallInt, adParamInput, 1, -5)

'Append parameters
With cmd
.Parameters.Append prmGross
.Parameters.Append prmReportType
.Parameters.Append prmDateStart
.Parameters.Append prmDateEnd
.Parameters.Append prmPeriodMonth1
.Parameters.Append prmWeekOFPeriod1
.Parameters.Append prmYearOfPeriod1
.Parameters.Append prmPeriodMonth2
.Parameters.Append prmWeekOFPeriod2
.Parameters.Append prmYearOfPeriod2
.Parameters.Append prmSummary
.Parameters.Append prmGlobalCat
.Parameters.Append prmInterval
.Parameters.Append prmDeptNo
End With

'Execute stored procedure and return to a recordset
rst.Open cmd.Execute

 

[cmmrfrds]

I think a place to look is whether the data type adTinyInt is mapping correctly through OLEDB from SQL Server to Access.

see attached.

http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

Probably try adUnsignedTinyInt instead.

 

[franklin1232]

The problem was so simple.

CHANGE
rst.Open cmd.Execute
TO
Set RST = cmd.execute