declare variable in stored procedure sql 2008

Question

I am working on a project which uses a stored procedure. When I try to use the stored procedure in the project, I'm getting this error:

The formal parameter "@Mode" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

Please help me in solving this problem.

This is my stored procedure:

alter PROCEDURE [ITAssets_sp_IT_Assets]
-- Add the parameters for the stored procedure here

    (@Mode varchar(12)='ADD',
    @ID integer , @AssetCode nvarchar(20)=null, @Description nvarchar(70)=null,
    @Site nvarchar(10)=null)
AS
Begin

    IF @Mode='ADD'
        Begin
  Begin Tran
      INSERT INTO [IT_Assets]
          ([ID]
                ,[AssetCode]
                ,[Description]
                ,[Site])
                    values
    (@ID, @AssetCode, @Description, @Site
    )
     If @@ERROR <> 0  
            ROLLBACK TRAN 
        Else
            COMMIT TRAN

    Select  @ID
End
ELSE 
Begin
        Begin Tran
                UPDATE [IT_Assets]
                    SET 
    AssetCode = @AssetCode, Description = @Description, Site = @Site 
    WHERE ID = @ID      
    If @@ERROR <> 0  
            ROLLBACK TRAN 
        Else
            COMMIT TRAN
        Select  @ID 
End
End

Am calling the stored procedure in below code:

Dim ht As New Hashtable

ht.Add("@Mode", "ADD")
ht.Add("@ID", txtID.Text)
ht.Add("@AssetCode", txtAssetCode.Text)
ht.Add("@Description", txtDescription)
ht.Add("@Site", ddlSite.SelectedValue.ToString())

AppExecuteNonQuery(CommandType.StoredProcedure, "IT_Assets", , ht)

AppExecuteNonQuery code:

 Public Shared Function AppExecuteNonQuery(ByVal SQLCommandType As CommandType, ByVal Command As String, Optional ByVal ParamTable As Hashtable = Nothing, Optional ByRef OutputParamTable As Hashtable = Nothing, Optional ByVal UsePrimaryConnection As Boolean = True, Optional ByVal ConnName As String = "", Optional ByVal ConnString As String = "") As Integer
        Dim cmd As SqlCommand
        Try
            If UsePrimaryConnection Then
                cmd = AppGetSQLCommand(SQLCommandType, Command, ParamTable, OutputParamTable, AppProperties.ConnectionString, True, UsePrimaryConnection, ConnName, ConnString)
            Else
                cmd = AppGetSQLCommand(SQLCommandType, Command, ParamTable, OutputParamTable, AppProperties.ConnectionString1, True, UsePrimaryConnection, ConnName, ConnString)
            End If

            Dim res As Integer = cmd.ExecuteNonQuery

            For Each param As SqlParameter In cmd.Parameters
                If param.Direction = ParameterDirection.Output Then
                    OutputParamTable(param.ParameterName) = param.Value
                End If
            Next

            Return res
        Finally
            If Not cmd Is Nothing Then
                If Not cmd.Connection Is Nothing Then
                    If cmd.Connection.State = ConnectionState.Open Then
                        cmd.Connection.Close()
                    End If
                End If
            End If
        End Try
    End Function

Answer 1

You have this in your code:

AppExecuteNonQuery(CommandType.StoredProcedure, "IT_Assets", , ht)

Since ht is at position 4, it is getting assigned to OutputParamTable which adds OUTPUT parameters to the stored procedure. That's exactly what the error message says too.

To fix it, move it to position 3 i.e.

AppExecuteNonQuery(CommandType.StoredProcedure, "IT_Assets", ht)