[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

How to execute a parameterized stored proc with multiple parms

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 paramets get loaded as per the following Immediate Window values:

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.

?RptYearF
 2005
?RptYearS
 2004

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
SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid], C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State, C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + ' ' +  C.CustomerNumber AS [Account Number], C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value], P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber  FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber) ON S.StateFS = C.State WHERE
 (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber <> 'NETWORKED' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='OTHER' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[BondsFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='FIXED INCOME' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='MUTUAL FUND' AND PlanNumber = 'NETWORKED' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[CashFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[StocksFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='EQUITY
' AND LEN(P.IraCode) = 0 AND S.FallCycle='1') UNION ALL SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid], C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State, C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + ' ' +  C.CustomerNumber AS [Account Number], C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value], P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber  FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.Custome
rNumber = P.CustomerNumber) ON S.StateFS = C.State WHERE (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber <> 'NETWORKED' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='OTHER' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[BondsFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='FIXED INCOME' And LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber = 'NETWORKED' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[CashFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[StocksFS],CAST
(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='EQUITY' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')

0
zimmer9
Asked:
zimmer9
  • 6
  • 4
1 Solution
 
Anthony PerkinsCommented:
This error -2147217900 occurs because you are combining the Parameter's Refresh method with CreateParameter method.  See here for a full discussion on it:
PRB: Error When You Combine ADO Refresh Method with CreateParameter
http://support.microsoft.com/default.aspx?scid=kb;en-us;298118
0
 
Anthony PerkinsCommented:
This is how I suggest you call the Stored Procedure with the three parameters:

With com
       Set .ActiveConnection = cn
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDFl"
       .Parameters.Append .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
       .Parameters.Append .CreateParameter("@RptYearF", adInteger, adParamInput, 4, intYearSP)
       .Parameters.Append .CreateParameter("@RptYearS", adInteger, adParamInput, 4, intYearSPS)
       Set rstQueryFS = .Execute
End With
0
 
zimmer9Author Commented:
I tried the following:

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

the result is still as follows:

?err.Number
-2147217900

If I comment out 1 of the 2 parameters, the execution is flawless but together, I get the error.

SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid], C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State, C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + ' ' +  C.CustomerNumber AS [Account Number], C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value], P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber  FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber) ON S.StateFS = C.State WHERE
 (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber <> 'NETWORKED' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='OTHER' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[BondsFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='FIXED INCOME' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='MUTUAL FUND' AND PlanNumber = 'NETWORKED' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[CashFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0 AND S.FallCycle='1')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[StocksFS],CAST(intYearSP as char(4)) + '-06-30'))  AND P.PropertyType='EQUITY
' AND LEN(P.IraCode) = 0 AND S.FallCycle='1') UNION ALL SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid], C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State, C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + ' ' +  C.CustomerNumber AS [Account Number], C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value], P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber  FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.Custome
rNumber = P.CustomerNumber) ON S.StateFS = C.State WHERE (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber <> 'NETWORKED' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='OTHER' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[BondsFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='FIXED INCOME' And LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber = 'NETWORKED' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[CashFS],CAST(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')  OR  (C.DateLost <= (DateAdd(YEAR,-1 * [S].[StocksFS],CAST
(intYearSPS as char(4)) + '-12-31'))  AND P.PropertyType='EQUITY' AND LEN(P.IraCode) = 0 AND S.FallCycle='0')
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Anthony PerkinsCommented:
And your stored procedure looks like the following?

CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYearF int,                                     -- Notice the comma missing in your original (commented) code
@RptYearS int


AS  

-- rest of the code
0
 
Anthony PerkinsCommented:
In order to attempt to duplicate your problem, here is what I did:
1.  I created the following Stored Procedure

CREATE PROCEDURE dbo.procUDFl

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

AS  

Select @prmSQL, @RptYearF, @RptYearS                                

GO
_________________________________________________________________________________________________
2. I created the following code in VB:

Dim cn As ADODB.Connection
Dim com As ADODB.Command
Dim rstQueryFS As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
   .ConnectionString = MY_CONN
   .Open
End With

Set com = New ADODB.Command
With com
       Set .ActiveConnection = cn
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDFl"
       .Parameters.Append .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, "@prmSQL")
       .Parameters.Append .CreateParameter("@RptYearF", adInteger, adParamInput, 4, 1)
       .Parameters.Append .CreateParameter("@RptYearS", adInteger, adParamInput, 4, 2)
       Set rstQueryFS = .Execute
End With
Set com = Nothing

With rstQueryFS
   Do While Not .EOF
      Debug.Print .Fields(0).Value, .Fields(1).Value, .Fields(2).Value
      .MoveNext
   Loop
   .Close
End With
Set rstQueryFS = Nothing

cn.Close
Set cn = Nothing

It ran through successfully and printed out the expected values.
0
 
zimmer9Author Commented:
I totally respect your judgement and want to thank you for addressing my question.

As I stated, my application works fine when I pass 1 parameter. When I pass 2 parameters together, I get the error:
 ?err.Number
-2147217900

on the line: Set rstQueryFS = .Execute

Do you think the problem could be that I use the REPLACE function for both parameters ?

SET @prmSQL = REPLACE(@prmSQL,'intYearSP',CAST(@RptYearF AS CHAR(4)))
SET @prmSQL = REPLACE(@prmSQL,'intYearSPS',CAST(@RptYearS AS CHAR(4)))
0
 
zimmer9Author Commented:
I took a sample example and it worked fine with 2 parameters both using the REPLACE function. Thanks for all your help.

regards Zimmer9
0
 
zimmer9Author Commented:
As it turns out I think I can get by with 1 parameter becauses intYearSPS is equal to intYearSP - 1.
0
 
Anthony PerkinsCommented:
>> When I pass 2 parameters together, I get the error:<<
I understood.  That is why I attempted to duplcate your probelm with more than one parameter.

>>Do you think the problem could be that I use the REPLACE function for both parameters ?<<
No.  I believe the error has to do with the Stored Procedure signature and how you are calling it, rather than the actual contents itself.  But you can confirm this yourself, by doing the same exercise I did.  In other words, create the stored procedure I posted and run it with the VB code I included.  This will also help you to troubleshoot your problem.

In any case, I am glad you got your problem fixed and thanks for the points.
0
 
Anthony PerkinsCommented:
By the way, I should have mentioned that all my tests were using the OLEDB driver.  There are subtle differences when you use the ODBC driver, so let me know if that is what you are using and I can re-test.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now