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

Syntax to pass 2 parameters to a stored procedure

Is the following syntax correct for passing 2 parameters to a stored procedure ? I am getting a 3420 error when I execute the application. I am passing intYearSP and intYearSPS to the stored procedure which follows:

Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDFl"
       .Parameters.Refresh
       Set prmSQL = .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
       .Parameters.Append prmSQL
       Set RptYear = .CreateParameter("@RptYearF", adInteger, adParamInput, 4, intYearSP)
       .Parameters.Append RptYearF
       Set RptYear = .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 assigned to the following dynamic SQL that I strung together in the Accss Application:

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.CustomerNumber = 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
  • 5
  • 2
1 Solution
 
Anthony PerkinsCommented:
Either create the parameters or preferably lose this line:
       .Parameters.Refresh

not both.
0
 
zimmer9Author Commented:
I lost the .Parameters.Refresh as you can see by the following yet I still get the error 3420 when I execute the application.
How would I create the parameters ?

With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDFl"
       Set prmSQL = .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
       .Parameters.Append prmSQL
       Set RptYear = .CreateParameter("@RptYearF", adInteger, adParamInput, 4, intYearSP)
       .Parameters.Append RptYearF
       Set RptYear = .CreateParameter("@RptYearS", adInteger, adParamInput, 4, intYearSPS)
       .Parameters.Append RptYearS
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
End With
0
 
Anthony PerkinsCommented:
You need to set the ActiveConnection property for the Command object as in:

With com
       Set .ActiveConnection = YourConnectionObjectGoesHere           ' If you have a connection string than comment this line
       '.ActiveConnection = YourConnectionStringGoesHere                 ' If you have a connection object than comment this line
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDFl"
       Set prmSQL = .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
       .Parameters.Append prmSQL
       Set RptYear = .CreateParameter("@RptYearF", adInteger, adParamInput, 4, intYearSP)
       .Parameters.Append RptYearF
       Set RptYear = .CreateParameter("@RptYearS", adInteger, adParamInput, 4, intYearSPS)
       .Parameters.Append RptYearS
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
End With
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Anthony PerkinsCommented:
Oops, I see you have already done that.
0
 
Anthony PerkinsCommented:
On what line do you get the error?
0
 
zimmer9Author Commented:
Go figure this one. I defined a few parameters as follows:

Dim RptYear As ADODB.Parameter
Dim RptYrF As ADODB.Parameter
Dim RptYrS As ADODB.Parameter
Dim prmSQL As ADODB.Parameter

If I use parameter RptYear as in the following, then it works fine. If I use the parameter RptYrF as a replacement for RptYear instead, I get the error 3420 on the line:
.Parameters.Append RptYrF

How can 1 parameter name work and another name fail ?

 Set RptYear = .CreateParameter("@RptYear", adInteger, adParamInput, 4, intYearSP)
       .Parameters.Append RptYear

REATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYear  int

AS  
SET @prmSQL = REPLACE(@prmSQL,'intYearSP',CAST(@RptYear AS CHAR(4)))
                                         
EXEC( @prmSQL)
GO
0
 
Anthony PerkinsCommented:
That is strange and I probably will not be able to duplicate the problem.  I assume you know what error 3420 represents.

As an alternative consider using code like this:
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

You can skip DIMing the parameters this way.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now