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

Question

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);

I might be missing something here but the GetItem lines would return multiple values and you are trying to pass these multiple values to the CreateParameter calls. Wouldn't you be looking to have a loop in there somewhere to loop through every record and execute the stored procedure based on the parameter values in that record. I don't understand the "& stateNGN &" part of creating the parameter either. In the past I simply place the variable in that argument without the quotes or the ampersand. — Matt Weller
– Matt Weller, Commented Jan 9, 2014 at 18:53
The GetItem will only return one value. I used stateNGN and stateDate as those variables need to be updated each time the code is executed, rather than hard coding the variables. — user2989011
– user2989011, Commented Jan 9, 2014 at 18:59
Point taken on GetItem, the second point was about CreateParameter("@SPstateNGN", adVarChar, adParamInput, 0, stateNGN), just removing the quotes and ampersands as whatever value is held in the variable is passed in as the parameter value. — Matt Weller
– Matt Weller, Commented Jan 9, 2014 at 19:03
Great, thank you that seems to have worked. Does the stored procedure code look correct? I am not sure if the parameters will actually get passed to the stored procedure. Is there a way to check that they are being passed correctly? — user2989011
– user2989011, Commented Jan 9, 2014 at 19:10
Thank you so much! I was able to test and make sure the variables were being passed by creating the temp table and running the two queries. — user2989011
– user2989011, Commented Jan 9, 2014 at 19:25

Matt Weller · Accepted Answer · 2014-01-09 19:32:38Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related