Link to home
Start Free TrialLog in
Avatar of BlakeMcKenna
BlakeMcKennaFlag for United States of America

asked on

Stored Procedure expecting a parameter Error?

I have a Stored Procedure in SQL Server 2008 that contains about 21 parameters. My VB.Net app makes a call to this SP and when the sqlCommand executes, I get the Error - "Procedure or function 'spAddUpdateRefCellDisp' expects parameter '@CellUnit', which was not supplied."

I check the SP to make sure the number of parameters were the same as in my call thru the VB.Net app and made sure all the parms were spelled correctly. Everything is identical. Could there be something else causing this?

My code for the VB App is below as well as the SP.

        Public Function AddUpdateRefCellDisp(ByVal cls As clsRefCellDisp) As String
        Dim retVal As Integer = 0

        Try
            If DBConnection() Then
                cmd = New SqlCommand("", cnn)
                cmd.Parameters.AddWithValue("@ID", cls.ID)                              '@ID                        
                cmd.Parameters.AddWithValue("@CellDispID", cls.CellDispID)              '@CellDispID            
                cmd.Parameters.AddWithValue("@CellDispName", cls.CellDispName)          '@CellDispName            
                cmd.Parameters.AddWithValue("@Capacity", cls.Capacity)                  '@Capacity                  
                cmd.Parameters.AddWithValue("@CellUnit", cls.CellUnit)                  '@CellUnit                  
                cmd.Parameters.AddWithValue("@LoadCellModel", cls.LoadCellModel)        '@LoadCellModel            
                cmd.Parameters.AddWithValue("@LoadCellSN", cls.LoadCellSN)              '@LoadCellSN            
                cmd.Parameters.AddWithValue("@CellUncertainty", cls.CellUncerntainty)   '@CellUncertainty      
                cmd.Parameters.AddWithValue("@DisplayModel", cls.DisplayModel)          '@DisplayModel            
                cmd.Parameters.AddWithValue("@DisplaySN", cls.DisplaySN)                '@DisplaySN                  
                cmd.Parameters.AddWithValue("@DisplayUncertainty", cls.DispUncertainty) '@DisplayUncertainty
                cmd.Parameters.AddWithValue("@CDUncertainty", cls.CDUncertainty)        '@CDUncertainty            
                cmd.Parameters.AddWithValue("@NIST", cls.NIST)                          '@NIST                        
                cmd.Parameters.AddWithValue("@CalDate", cls.CalDate)                    '@CalDATe                  
                cmd.Parameters.AddWithValue("@NextCalibration", cls.NextCalibration)    '@NextCalibration      
                cmd.Parameters.AddWithValue("@Interval", cls.Interval)                  '@Interval                  
                cmd.Parameters.AddWithValue("@CalHouseID", cls.CalHouseID)              '@CalHouseID            
                cmd.Parameters.AddWithValue("@TechID", cls.TechID)                      '@TechID                  
                cmd.Parameters.AddWithValue("@Newest", cls.Newest)                      '@Newest                  
                'cmd.Parameters.AddWithValue("@IsHide", cls.IsHide)                      '@IsHide                  
                cmd.Parameters.AddWithValue("@active", cls.Active)                      '@active                  
                cmd.CommandText = "spAddUpdateRefCellDisp"
                cmd.CommandType = CommandType.StoredProcedure
                retVal = cmd.ExecuteNonQuery
                strIOType = "U"
            End If

        Catch ex As Exception
            strErr = "CalDataIO/AddUpdateRefCellDisp() - " & ex.Message
            strIOType = strErr
        End Try

        Return strIOType
    End Function


    USE [CalibrationTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==============================================================================================
-- Author:            Blake McKenna
-- Create date: 9/25/2013
-- Description:      Adds or Updates the SystemCellDisp Table
-- ==============================================================================================
ALTER PROCEDURE [FUTEKLOCAL\blake].[spAddUpdateRefCellDisp]
      @ID                              INT,
      @CellDispID                  INT,
      @CellDispName            VARCHAR(50),
      @Capacity                  VARCHAR(100),
      @CellUnit                  VARCHAR(20),
      @LoadCellModel            VARCHAR(20),
      @LoadCellSN                  VARCHAR(20),
      @CellUncertainty      DECIMAL(18,9),
      @DisplayModel            VARCHAR(20),
      @DisplaySN                  VARCHAR(20),
      @DisplayUncertainty      DECIMAL(18,9),
      @CDUncertainty            DECIMAL(18,9),
      @NIST                        VARCHAR(50),
      @CalDATe                  DATETIME,
      @NextCalibration      DATETIME,
      @Interval                  INT,
      @CalHouseID                  INT,
      @TechID                        VARCHAR(50),
      @Newest                        BIT,
      --@IsHide                        BIT,
      @active                        BIT
AS
BEGIN

      IF EXISTS (SELECT COUNT(*) FROM SystemCellDisp WHERE ID = @ID)
            BEGIN
                  UPDATE      SystemCellDisp
                     SET      CellDispID = @CellDispID,
                              CellDispName = @CellDispName,
                              Capacity = @Capacity,
                              CellUnit = @CellUnit,
                              LoadCellModel = @LoadCellModel,
                              LoadCellSN = @LoadCellSN,
                              CellUncertainty = @CellUncertainty,
                              DisplayModel = @DisplayModel,
                              DisplaySN = @DisplaySN,
                              DispUncertainty = @DisplayUncertainty,
                              CDUncertainty = @CDUncertainty,
                              Nist = @NIST,
                              CalDate = @CalDATe,
                              NextCalibration = @NextCalibration,
                              Interval = @Interval,
                              CalHouseID = @CalHouseID,
                              TechID = @TechID,
                              Newest = @Newest
                              --IsHide = @IsHide
                   WHERE      ID = @ID
             
                  RETURN
            END
      ELSE
            BEGIN
                  INSERT INTO SystemCellDisp
                        (CellDispID,
                           CellDispName,
                           Capacity,
                           CellUnit,
                           LoadCellModel,
                           LoadCellSN,
                           CellUncertainty,
                           DisplayModel,
                           DisplaySN,
                           DispUncertainty,
                           CDUncertainty,
                           Nist,
                           CalDate,
                           NextCalibration,
                           Interval,
                           CalHouseID,
                           TechID,
                           Newest,
                           --IsHide,
                           active)
                   VALUES
                        (@CellDispID,
                         @CellDispName,
                         @Capacity,
                         @CellUnit,
                         @LoadCellModel,
                         @LoadCellSN,
                         @CellUncertainty,
                         @DisplayModel,
                         @DisplaySN,
                         @DisplayUncertainty,
                         @CDUncertainty,
                         @NIST,
                         @CalDATe,
                         @NextCalibration,
                         @Interval,
                         @CalHouseID,
                         @TechID,
                         @Newest,
                         --@IsHide,
                         @active)
                         
                  RETURN                     
            END      
END
Avatar of Jerry_Justice
Jerry_Justice

I would check for invalid characters in one of the parameters preceding the CellUnit param.

Does the "Capacity" parameter perhaps have a comma or some other character that might be interpreted as a delimiter of some kind?  CR? LF? an embedded quote?
Avatar of BlakeMcKenna

ASKER

Are you referring to the Data Value or the "@Capacity" itself?
ASKER CERTIFIED SOLUTION
Avatar of Jerry_Justice
Jerry_Justice

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial