Help with ADO stored procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Access 2003, 2000 format, SQL Server 2000

I have a stored procedure that works perfectly in SQL Query Analyser. I
want to be able to create a recordset of the results from within Access. I
am using ADO

Here is what I have so far in Access but the result set is not taking any
notice of the @Order variable that is being sent.

What am I doing wrong?

Access code:
With cmdSales
Set .ActiveConnection = conMIS
.CommandText = "SalesByCustomer"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

params.Append cmdSales.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdSales.CreateParameter("@Order", adChar, adParamInput, 1)
params.Append cmdSales.CreateParameter("@Year", adVarChar, adParamInput, 4)
params.Append cmdSales.CreateParameter("@Month", adVarChar, adParamInput, 2)
params.Append cmdSales.CreateParameter("@Ytd", adChar, adParamInput, 1)

params("@Order") = "2"
params("@Year") = sYear
params("@Month") = Period
params("@Ytd") = "Y"

SQL Stored Procedure snippet of variables

@Order char(1),
@Year varchar(4) = '2005',
@Month varchar (2) = null,
@Ytd char(1) = 'N'

AS

IF @Order = '1'
IF @Ytd = 'N'
SELECT blah blah
IF @Ytd = 'Y'
SELECT blah blah
IF @Order = '2'

IF @Ytd = 'N'
SELECT blah blah
IF @Ytd = 'Y'
SELECT blah blah


.

Relevant Pages

  • Re: Report to display data from sql serv.
    ... IIF (case statements in SQL Server) scenarios etc. ... then you need to create a Stored Procedure in SQL Server and use that as ... at run time supplying the parameters in code by using the Exec command. ...
    (microsoft.public.access.reports)
  • Re: ADO stored proc
    ... to know how to access a SQL Server stored procedure using vbscript ... I've looked at vbscript books and sql server books on ... I don't like this technique since: ...
    (microsoft.public.scripting.vbscript)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Procedures - Patterns and Practices
    ... >published the reasoning behind its opinions. ... I disagree that the debate in SQL Server related discussion forums ... If the natural key is long or spans too many ... I want to call a stored procedure that adds a customer ...
    (microsoft.public.sqlserver.programming)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... "Active Server Pages error 'ASP 0113' ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)