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

And can you please share the code of `AppExecuteNonQuery` method too? The error seems to point to the statement calling your procedure to have issues. — shree.pat18, Jun 18 '14 at 07:57
It may be standardized, that doesn't mean it's not the source of the error. The error clearly indicates "the actual parameter passed in requested output" - so we need to see the code that generates the actual parameters. — Damien_The_Unbeliever, Jun 18 '14 at 08:00

score 2 · Accepted Answer · answered Jun 18 '14 at 08:05

2

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)

answered Jun 18 '14 at 08:05

shree.pat18

21,449
3
43
63

I got this error `No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type.` – 7alhashmi Jun 18 '14 at 08:08
1

@7alhashmi - that's because you're adding `txtDescription` rather than `txtDescription.Text` as the `@Description` parameter. – Damien_The_Unbeliever Jun 18 '14 at 10:03
@Damien_The_Unbeliever Thanks! I missed out on that. – shree.pat18 Jun 18 '14 at 10:07

declare variable in stored procedure sql 2008

1 Answers1