CODEWITHSUNDEEP
sql-servervbavariablesstored-proceduresparameters
user2989011
user2989011

Reputation: 77

Pass Parameter(s) from Access Variable to SQL Server Stored Procedure

I am trying to pass variables/parameters from an Access table to a stored procedure in SQL Server using an ADODB connection. I am pulling the variables in Access from and Access query (so I’m trying to pass a variable that is updated each time the code is executed). I am getting the following error message when trying to pass the variables below in the Access VBA Code:

Parameter object is improperly defined. Inconsistent or incomplete information was provided.

I am new to this kind of coding. Could someone please provide some direction on how to pass variables (a result of an Access query) to a stored procedure, and then call those variables in the stored procedure?

Access VBA code:

        '''CREATE LINKED TABLE THAT PULLS FROM TBL_STATEMENT_MASTER IN SQL
        ''variables to pass to get dFileRE
        '' (1) stateDate
        stateDate = getItem("select distinct paydate from update_statement_master_re")
        '' (2) stateNGN
        stateNGN = getItem("select distinct deal_code from update_statement_master_re")

            user = GetUser()

        '' Connect to Data Source - Securities DB - SQL Server
            Set dbconn = New ADODB.Connection
            dbconn.ConnectionString = "driver=SQL Server;server=R7SQL1;database=SecuritiesDB;trusted_connection=YES"
            dbconn.Open dbconn.ConnectionString
            Set cmd = New ADODB.Command
            cmd.ActiveConnection = dbconn

        '' Set CommandText equal to the stored procedure name (spStatementCheck)
            cmd.CommandType = adCmdStoredProc
            cmd.CommandText = "spStatementCheck"
            cmd.NamedParameters = True 'paramStatementCheck'

'' THIS IS THE PART OF THE CODE THAT IS CRASHING – I AM NOT SURE HOW TO CALL THESE VARIABLES
            cmd.Parameters.Append _
                cmd.CreateParameter("@SPstateNGN", adVarChar, adParamInput, 0, "& stateNGN &")
            cmd.Parameters.Append _
                cmd.CreateParameter("@SPstateDate", adDate, adParamInput, 0, "& stateDate &")

SQL Server stored procedure code:

     USE [SecuritiesDB_TEST]
      GO
     SET ANSI_NULLS ON
      GO
     SET QUOTED_IDENTIFIER ON
      GO

     ALTER PROCEDURE [dbo].[spStatementCheck]
     @SPstateNGN as nvarchar(25),
     @SPstateDate as datetime

      AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


     -- Insert statements for procedure here

       INSERT INTO UPDATE_Statement_Master_Check ( NGN_Full, [Date], [DESCRIPTION], AMOUNT,     NGN_SHORT, Series, FileDate, EffectiveDate, ActualDate, [Source], ID )
        SELECT tbl_Statement_Master.NGN_Full, tbl_Statement_Master.[Date], 
       tbl_Statement_Master.[DESCRIPTION], 
       tbl_Statement_Master.AMOUNT, 
       tbl_Statement_Master.NGN_SHORT, 
       tbl_Statement_Master.Series, 
       tbl_Statement_Master.FileDate, 
       tbl_Statement_Master.EffectiveDate, 
       tbl_Statement_Master.ActualDate, 
       tbl_Statement_Master.[Source], 
       tbl_Statement_Master.ID
       FROM tbl_Statement_Master
       where tbl_Statement_Master.[Date] <> @SPstateDate
       and tbl_Statement_Master.NGN_Full  = @SPstateNGN
       and tbl_statement_master.FileDate = (Select distinct max(filedate) from tbl_statement_master
       where [DATE] = @SPstateDate and NGN_Full = @SPstateNGN);

Upvotes: 0

Views: 3563

Answers (1)

Matt Weller
Matt Weller

Reputation: 2764

When you try to add your parameters you only need to pass the variables themselves into the call.

'' THIS IS THE PART OF THE CODE THAT IS CRASHING – I AM NOT SURE HOW TO CALL THESE VARIABLES            
cmd.Parameters.Append _
    cmd.CreateParameter("@SPstateNGN", adVarChar, adParamInput, 0, "& stateNGN &")
cmd.Parameters.Append _
    cmd.CreateParameter("@SPstateDate", adDate, adParamInput, 0, "& stateDate &")

The problem is with the 2 values you are passing in as parameters: "& stateNGN &" and "& stateDate &". In this instance you don't have to do anything fancy with your variables as they already contain the values you want to pass to the stored procedure, and the CreateParameter call will handle them for you.

So just change those calls to:

cmd.Parameters.Append _
    cmd.CreateParameter("@SPstateNGN", adVarChar, adParamInput, 0, stateNGN)
cmd.Parameters.Append _
    cmd.CreateParameter("@SPstateDate", adDate, adParamInput, 0, stateDate)

Note that you could also create the parameter in one step and then assign a value to it in a subsequent step, as opposed to the all-in-one approach taken above. Further examples on CreateParameter can be found on various Stack Overflow threads and on the MSDN website.

Upvotes: 1

Related Questions