Help with ADO stored procedure

A

Al Newbie

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
 
G

Geoffs

Hi Al,
You need an "Else" for the second "If"
IF @Order = '1'
........
Else IF @Order = '2'

:)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top