SQL Stored Call not passing parameter

Question

I have a SQL Server 2008 stored procedure which when called via the code shown below returns a missing parameter exception for the @BatchID parameter.

As you can see I am passing in the parameter with

cmd.Parameters.AddWithValue("@BatchID", batchID)

but for some reason its not picking it up when making the call to the server.

Probably missing something simple here, its been a long day.

Stored procedure

ALTER PROCEDURE [dbo].[spIsEngineSixCylinderByBatchID]
    @BatchID as int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @IsSixCylinder as bit
    SET @IsSixCylinder = 0

    SELECT @IsSixCylinder = tblBatches.SixCylinder
        FROM tblBatches
        WHERE IdBatch = @BatchID

    SELECT @IsSixCylinder
END

Code

Private Function IsBatchSixCylinder(batchID As Int32) As Boolean
    Dim isSixCylinder As Boolean = False

    Try
        Using cmd As New SqlClient.SqlCommand("[dbo].[spIsEngineSixCylinderByBatchID]", _conn)
            cmd.Parameters.AddWithValue("@BatchID", batchID)
            isSixCylinder = cmd.ExecuteScalar
        End Using

    Catch ex As SqlClient.SqlException
        LogError(ex.Message)
    End Try

    Return isSixCylinder

End Function

Exception

Procedure or function 'spIsEngineSixCylinderByBatchID' expects parameter '@BatchID', which was not supplied.

Call Stack

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at ClassLib_SAPtoEPMS.clsSAPtoEPMS.IsBatchSixCylinder(Int32 batchID) in C:\Users\phil.murray\Desktop\EPMS_05022012\EPMS_05022012\Power Systems EPMS\ClassLib_SAPtoEPMS\ClassLib_SAPtoEPMS\clsSAPtoEPMS.vb:line 229

Ramesh Durai · Accepted Answer · 2013-04-25 09:56:26Z

2

Can you try whether the code in the below snippet was working?

Using cmd As New SqlClient.SqlCommand("spIsEngineSixCylinderByBatchID", _conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@BatchID", batchID)
            isSixCylinder = cmd.ExecuteScalar
End Using

Or

Using cmd As New SqlClient.SqlCommand("spIsEngineSixCylinderByBatchID", _conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@BatchID ", SqlDbType.Int)
            cmd.Parameters("@BatchID ").Value = batchID
End Using

answered Apr 25, 2013 at 9:56

Ramesh Durai

2,6869 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ramesh Durai Over a year ago

Included cmd.CommandType = CommandType.StoredProcedure in edit

Phil Murray Over a year ago

Thought the default CommandType was StoredProcedure??? It worked but I'm confused now.

Ramesh Durai Over a year ago

Please see stackoverflow.com/questions/9799524/…

marc_s Over a year ago

@PhilMurray: nope, the default is CommandType.Text - e.g. an inline SQL statement

Mandeep Singh · Accepted Answer · 2013-04-25 09:57:45Z

0

use this

Private Function IsBatchSixCylinder(batchID As Int32) As Boolean

Dim isSixCylinder As Boolean = False



Try
    Using cmd As New SqlClient.SqlCommand("[dbo].[spIsEngineSixCylinderByBatchID]", _conn)

    cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@BatchID", batchID)
        isSixCylinder = cmd.ExecuteScalar
    End Using

Catch ex As SqlClient.SqlException
    LogError(ex.Message)
End Try

Return isSixCylinder

End Function

answered Apr 25, 2013 at 9:57

Mandeep Singh

2,1668 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

SQL Stored Call not passing parameter

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related