BlakeMcKenna
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.AddWithValu e("@ID", cls.ID) '@ID
cmd.Parameters.AddWithValu e("@CellDi spID", cls.CellDispID) '@CellDispID
cmd.Parameters.AddWithValu e("@CellDi spName", cls.CellDispName) '@CellDispName
cmd.Parameters.AddWithValu e("@Capaci ty", cls.Capacity) '@Capacity
cmd.Parameters.AddWithValu e("@CellUn it", cls.CellUnit) '@CellUnit
cmd.Parameters.AddWithValu e("@LoadCe llModel", cls.LoadCellModel) '@LoadCellModel
cmd.Parameters.AddWithValu e("@LoadCe llSN", cls.LoadCellSN) '@LoadCellSN
cmd.Parameters.AddWithValu e("@CellUn certainty" , cls.CellUncerntainty) '@CellUncertainty
cmd.Parameters.AddWithValu e("@Displa yModel", cls.DisplayModel) '@DisplayModel
cmd.Parameters.AddWithValu e("@Displa ySN", cls.DisplaySN) '@DisplaySN
cmd.Parameters.AddWithValu e("@Displa yUncertain ty", cls.DispUncertainty) '@DisplayUncertainty
cmd.Parameters.AddWithValu e("@CDUnce rtainty", cls.CDUncertainty) '@CDUncertainty
cmd.Parameters.AddWithValu e("@NIST", cls.NIST) '@NIST
cmd.Parameters.AddWithValu e("@CalDat e", cls.CalDate) '@CalDATe
cmd.Parameters.AddWithValu e("@NextCa libration" , cls.NextCalibration) '@NextCalibration
cmd.Parameters.AddWithValu e("@Interv al", cls.Interval) '@Interval
cmd.Parameters.AddWithValu e("@CalHou seID", cls.CalHouseID) '@CalHouseID
cmd.Parameters.AddWithValu e("@TechID ", cls.TechID) '@TechID
cmd.Parameters.AddWithValu e("@Newest ", cls.Newest) '@Newest
'cmd.Parameters.AddWithVal ue("@IsHid e", cls.IsHide) '@IsHide
cmd.Parameters.AddWithValu e("@active ", cls.Active) '@active
cmd.CommandText = "spAddUpdateRefCellDisp"
cmd.CommandType = CommandType.StoredProcedur e
retVal = cmd.ExecuteNonQuery
strIOType = "U"
End If
Catch ex As Exception
strErr = "CalDataIO/AddUpdateRefCel lDisp() - " & 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].[spAddU pdateRefCe llDisp]
@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
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
Dim retVal As Integer = 0
Try
If DBConnection() Then
cmd = New SqlCommand("", cnn)
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
'cmd.Parameters.AddWithVal
cmd.Parameters.AddWithValu
cmd.CommandText = "spAddUpdateRefCellDisp"
cmd.CommandType = CommandType.StoredProcedur
retVal = cmd.ExecuteNonQuery
strIOType = "U"
End If
Catch ex As Exception
strErr = "CalDataIO/AddUpdateRefCel
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].[spAddU
@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
ASKER
Are you referring to the Data Value or the "@Capacity" itself?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?