none
Insert Stored Procedure, call from VB.net code RRS feed

  • Question

  • I dont know what am i doing wrong here???

    Here's my stored proc
    ALTER PROCEDURE dbo.uspInsertExpenseType
        @Type NVARCHAR(50),
        @Description NVARCHAR(200),
        @CreatedOn DATETIME,
        @ModifiedOn DATETIME,
        @CreatedBy NVARCHAR(50),   
        @ModifiedBy NVARCHAR(50)
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        INSERT INTO dbo.ExpenseType (
            [Type],
            [Description],
            [CreatedOn],
            [ModifiedOn],
            [CreatedBy],
            [ModifiedBy]
        ) VALUES ( @type, @Description, @CreatedOn, @ModifiedOn, @CreatedBy, @ModifiedBy)
       
    END

    which runs fine as it is.

    Here's my call from the data access layer

       Dim conn As New SqlConnection(_connectionString)
            Dim _adapter As SqlClient.SqlDataAdapter = New Global.System.Data.SqlClient.SqlDataAdapter
          
            _adapter.InsertCommand = New Global.System.Data.SqlClient.SqlCommand

            _adapter.InsertCommand.Connection = conn
            _adapter.InsertCommand.CommandText = "dbo.uspInsertExpenseType"
            _adapter.InsertCommand.CommandType = Global.System.Data.CommandType.StoredProcedure
            Dim type As New Global.System.Data.SqlClient.SqlParameter("@Type", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.Type)
            type.Direction = ParameterDirection.Input

            Dim description As New SqlParameter("@Description", Global.System.Data.SqlDbType.NVarChar, 200, expenseType.Description)
            description.Direction = ParameterDirection.Input

            Dim createdOn As New SqlParameter("@CreatedOn", Global.System.Data.SqlDbType.DateTime, 8, expenseType.CreatedOn)
            createdOn.Direction = ParameterDirection.Input

            Dim modifiedOn As New SqlParameter("@ModifiedOn", Global.System.Data.SqlDbType.DateTime, 8, expenseType.ModifiedOn)
            modifiedOn.Direction = ParameterDirection.Input

            Dim createdBy As New SqlParameter("@CreatedBy", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.CreatedBy)
            createdBy.Direction = ParameterDirection.Input

            Dim modifiedBy As New SqlParameter("@ModifiedBy", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.ModifiedBy)
            createdBy.Direction = ParameterDirection.Input

            _adapter.InsertCommand.Parameters.Add(type)
            _adapter.InsertCommand.Parameters.Add(description)

            _adapter.InsertCommand.Parameters.Add(createdOn)

            _adapter.InsertCommand.Parameters.Add(modifiedOn)

            _adapter.InsertCommand.Parameters.Add(createdBy)

            _adapter.InsertCommand.Parameters.Add(modifiedBy)
            _adapter.InsertCommand.Connection.Open()
            _adapter.InsertCommand.ExecuteNonQuery()
            _adapter.InsertCommand.Connection.Close()


    Here's the exception : "Procedure or function 'uspInsertExpenseType' expects parameter '@Type', which was not supplied."


    Monday, June 15, 2009 5:40 PM

Answers

  • SqlParameter class does not have a constructor to match the inputs your are passing.
    The 4th string parameter in the SqlParameter constructor is the name of the column and not the value of the object
    The params are missing the values and hence the exception.

    Instead of code:

            Dim type As New Global.System.Data.SqlClient.SqlParameter("@Type", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.Type)
            type.Direction = ParameterDirection.Input

            Dim description As New SqlParameter("@Description", Global.System.Data.SqlDbType.NVarChar, 200, expenseType.Description)
            description.Direction = ParameterDirection.Input

            Dim createdOn As New SqlParameter("@CreatedOn", Global.System.Data.SqlDbType.DateTime, 8, expenseType.CreatedOn)
            createdOn.Direction = ParameterDirection.Input

            Dim modifiedOn As New SqlParameter("@ModifiedOn", Global.System.Data.SqlDbType.DateTime, 8, expenseType.ModifiedOn)
            modifiedOn.Direction = ParameterDirection.Input

            Dim createdBy As New SqlParameter("@CreatedBy", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.CreatedBy)
            createdBy.Direction = ParameterDirection.Input

            Dim modifiedBy As New SqlParameter("@ModifiedBy", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.ModifiedBy)
            createdBy.Direction = ParameterDirection.Input

    Use Code:
            Dim type As New Global.System.Data.SqlClient.SqlParameter("@Type", Global.System.Data.SqlDbType.NVarChar, 50)
            type.Value = expenseType.Type ' Or what ever is the value that is passed into the Stored Proc as Input
            type.Direction = ParameterDirection.Input

            Dim description As New SqlParameter("@Description", Global.System.Data.SqlDbType.NVarChar, 200)
            description.Value = expenseType.Description ' Or what ever is the value that is passed into the Stored Proc as Input
            description.Direction = ParameterDirection.Input

            Dim createdOn As New SqlParameter("@CreatedOn", Global.System.Data.SqlDbType.DateTime, 8)
            createdOn.Value = expenseType.CreatedOn ' Or what ever is the value that is passed into the Stored Proc as Input
            createdOn.Direction = ParameterDirection.Input

            Dim modifiedOn As New SqlParameter("@ModifiedOn", Global.System.Data.SqlDbType.DateTime, 8)
            modifiedOn.Value = expenseType.ModifiiedOn ' Or what ever is the value that is passed into the Stored Proc as Input
            modifiedOn.Direction = ParameterDirection.Input

            Dim createdBy As New SqlParameter("@CreatedBy", Global.System.Data.SqlDbType.NVarChar, 50)
            createdBy.Value = expenseType.CreatedBy ' Or what ever is the value that is passed into the Stored Proc as Input
            createdBy.Direction = ParameterDirection.Input

            Dim modifiedBy As New SqlParameter("@ModifiedBy", Global.System.Data.SqlDbType.NVarChar, 50)
            modifiedBy.Value = expenseType.ModifiedBy ' Or what ever is the value that is passed into the Stored Proc as Input
            createdBy.Direction = ParameterDirection.Input

    Monday, June 15, 2009 6:29 PM
    Moderator
  • Where do you assign the value to each Parameter object? If it's supposed to be the fourth argument, I believe that would be parameter direction and not the parameter value based upon the available argument signatures.


    Edit: Looks like my fingers aren't fast enough. ;-)


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by hks Monday, June 15, 2009 7:18 PM
    Monday, June 15, 2009 6:35 PM

All replies

  • SqlParameter class does not have a constructor to match the inputs your are passing.
    The 4th string parameter in the SqlParameter constructor is the name of the column and not the value of the object
    The params are missing the values and hence the exception.

    Instead of code:

            Dim type As New Global.System.Data.SqlClient.SqlParameter("@Type", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.Type)
            type.Direction = ParameterDirection.Input

            Dim description As New SqlParameter("@Description", Global.System.Data.SqlDbType.NVarChar, 200, expenseType.Description)
            description.Direction = ParameterDirection.Input

            Dim createdOn As New SqlParameter("@CreatedOn", Global.System.Data.SqlDbType.DateTime, 8, expenseType.CreatedOn)
            createdOn.Direction = ParameterDirection.Input

            Dim modifiedOn As New SqlParameter("@ModifiedOn", Global.System.Data.SqlDbType.DateTime, 8, expenseType.ModifiedOn)
            modifiedOn.Direction = ParameterDirection.Input

            Dim createdBy As New SqlParameter("@CreatedBy", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.CreatedBy)
            createdBy.Direction = ParameterDirection.Input

            Dim modifiedBy As New SqlParameter("@ModifiedBy", Global.System.Data.SqlDbType.NVarChar, 50, expenseType.ModifiedBy)
            createdBy.Direction = ParameterDirection.Input

    Use Code:
            Dim type As New Global.System.Data.SqlClient.SqlParameter("@Type", Global.System.Data.SqlDbType.NVarChar, 50)
            type.Value = expenseType.Type ' Or what ever is the value that is passed into the Stored Proc as Input
            type.Direction = ParameterDirection.Input

            Dim description As New SqlParameter("@Description", Global.System.Data.SqlDbType.NVarChar, 200)
            description.Value = expenseType.Description ' Or what ever is the value that is passed into the Stored Proc as Input
            description.Direction = ParameterDirection.Input

            Dim createdOn As New SqlParameter("@CreatedOn", Global.System.Data.SqlDbType.DateTime, 8)
            createdOn.Value = expenseType.CreatedOn ' Or what ever is the value that is passed into the Stored Proc as Input
            createdOn.Direction = ParameterDirection.Input

            Dim modifiedOn As New SqlParameter("@ModifiedOn", Global.System.Data.SqlDbType.DateTime, 8)
            modifiedOn.Value = expenseType.ModifiiedOn ' Or what ever is the value that is passed into the Stored Proc as Input
            modifiedOn.Direction = ParameterDirection.Input

            Dim createdBy As New SqlParameter("@CreatedBy", Global.System.Data.SqlDbType.NVarChar, 50)
            createdBy.Value = expenseType.CreatedBy ' Or what ever is the value that is passed into the Stored Proc as Input
            createdBy.Direction = ParameterDirection.Input

            Dim modifiedBy As New SqlParameter("@ModifiedBy", Global.System.Data.SqlDbType.NVarChar, 50)
            modifiedBy.Value = expenseType.ModifiedBy ' Or what ever is the value that is passed into the Stored Proc as Input
            createdBy.Direction = ParameterDirection.Input

    Monday, June 15, 2009 6:29 PM
    Moderator
  • Where do you assign the value to each Parameter object? If it's supposed to be the fourth argument, I believe that would be parameter direction and not the parameter value based upon the available argument signatures.


    Edit: Looks like my fingers aren't fast enough. ;-)


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by hks Monday, June 15, 2009 7:18 PM
    Monday, June 15, 2009 6:35 PM
  • Thank God you guys saw this, i dont know why i didnt see it.................................


    Thanks again............
    Monday, June 15, 2009 7:18 PM