Help with sending two parameters to Stored Procedure.

[Mark Ardito]

My application is a Access 2000 front end and SQL server
backend. I have a stored procedure on the server that
will accept 2 variables. From my Front End I have a form
with 2 comboboxes that pass that data along. I have been
successful at passing one variable, but 2 is giving me
grief. Here is my module...

'Set up the DAO for the pass-through SQL query
Dim db As Database
Dim MyQueryDef As QueryDef
Dim SQLString As String
Dim SPTQueryName As String
Dim msg As String

Set db = CurrentDb()
db.QueryTimeout = 240
SPTQueryName = "qrySponsorReportByDivision_SQL"
If ObjectExists("Queries", SPTQueryName) = True Then
db.QueryDefs.Delete SPTQueryName
db.QueryDefs.Refresh
End If
Set MyQueryDef = db.CreateQueryDef(SPTQueryName)

MyQueryDef.Connect = IMO_ODBCConnectionString

'Now is a good time to display the Project Manager
Choice Selection Screen.
If intDivision = 0 Then
DoCmd.OpenForm "frmSponsorReportByDivision",
acNormal, , , acFormEdit, acDialog
End If

'Set the SQL property and concatenate the variables
If intDivision <> 0 Then
I NEED HELP HERE -----> SQLString
= "upSponsorReportByDivision" & "'" & intDivision & "' '"
& intSponsor & "'"
MyQueryDef.SQL = SQLString
MyQueryDef.ReturnsRecords = True
MyQueryDef.Close
db.Close
DoCmd.OpenQuery SPTQueryName, acViewNormal
Else
MyQueryDef.ReturnsRecords = False
MyQueryDef.Close
db.Close
End If

What am I doing wrong? I have tried to seperate those 2
variables with comma's and that didn't work either.

Thanks!
Mark

 

[Douglas J. Steele]

SQL Server Stored Procedure properties are delimited with commas:

EXEC MyStoredProc Param1, Param2

If you're not positive about the order of the parameters, you can name them:

EXEC MyStoredProc @Param1=Param1, @Param2=Param2

 

[david epsom dot com dot au]

I NEED HELP HERE -----> SQLString
= "upSponsorReportByDivision" & "'" & intDivision & "' '"
& intSponsor & "'"

And I see that the space is missing between the proc name and the first
parameter :~)

(david)

 

[Guest]

Doug,

Is the EXEC statement necessary? I have other modules
just like this one however it only uses 1 parameter and I
don't use the EXEC statement.

Thanks!
Mark

 

[Douglas J. Steele]

I don't believe it's mandatory, but I consider it a good idea since it helps
document what you're doing.