passing 2 parameters to a stored procedure 1

[git2dn]

The following Access Application works fine when I pass 1 parameter at a time to the stored proc. When I pass 2 parms at the same time the parameters get loaded as per the following Immediate Window values:

?RptYearF
2005
?RptYearS
2004

For the snapshots of my code, you will see that I commented out the code for 1 of the parameters to prove that the application runs fine when I run the application with just 1 parameter.

However, the application blows up on the following line:

Set rstQueryFS = .Execute

?err.Number
-2147217900
--------------------------------

How can I modify the application or stored procedure to handle the 2 parameters in the stored procedure ?

With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.procUDFl"
.Parameters.Refresh
Set prmSQL = .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
.Parameters.Append prmSQL
'Set RptYearF = .CreateParameter("@RptYearF", adInteger, adParamInput, 4, intYearSP)
'.Parameters.Append RptYearF
Set RptYearS = .CreateParameter("@RptYearS", adInteger, adParamInput, 4, intYearSPS)
.Parameters.Append RptYearS
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With


CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
--@RptYearF int
@RptYearS int


AS

--SET @prmSQL = REPLACE(@prmSQL,'intYearSP',CAST(@RptYearF AS CHAR(4)))
SET @prmSQL = REPLACE(@prmSQL,'intYearSPS',CAST(@RptYearS AS CHAR(4)))

EXEC( @prmSQL)
GO
------------------
prmSQL is a dynamics SQL string that I create in an Access Application and pass to the Stored Procedure for execution.

 

[cmmrfrds]

Try sending a data type that matches in byte size. I don't believe putting a 4 in is going to override the adInteger containing only 2 bytes or change the sql server side to tinyint. You only need a size in the parameter for an undefined byte size like varchar. Otherwise, it looks okay as far as I can tell.

Set RptYearS = .CreateParameter("@RptYearS", adSingle, adParamInput,, intYearSPS

 

[git2dn]

I tried your suggestion as follows but I still get the error as follows:

?err.Number
-2147217900

error line: Set rstQueryFS = .Execute
----------------------------------------------
With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.procUDFl"
Set prmSQL = .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
.Parameters.Append prmSQL
Set RptYearF = .CreateParameter("@RptYearF", adSingle, adParamInput, , intYearSP)
.Parameters.Append RptYearF
Set RptYearS = .CreateParameter("@RptYearS", adSingle, adParamInput, , intYearSPS)
.Parameters.Append RptYearS
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With

 

[git2dn]

Do you think my problem is that in the Stored Procedure I use the REPLACE function for both of the parameters I pass to the Stored Procedure ?

CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYearF int,
@RptYearS int

AS

SET @prmSQL = REPLACE(@prmSQL,'intYearSP',CAST(@RptYearF AS CHAR(4)))
SET @prmSQL = REPLACE(@prmSQL,'intYearSPS',CAST(@RptYearS AS CHAR(4)))

EXEC( @prmSQL)
GO

 

[cmmrfrds]

You should look at the final result of the replace by returning the string to the app.

--EXEC( @prmSQL)
--GO
Select @prmSQL

Also, try adding error handling to your code, which might give a better description of the error.

Public Function SomeFunction()
On Error GoTo ErrHandler
Your code................

Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Next
End Function