"Procedure expects parameter which was not supplied..." SQL Trace indicates procedure gets the parameter

Question

I am at a loss here, I don't understand why the procedure is not getting this parameter...

Procedure (procInfoColor):

ALTER PROCEDURE [dbo].[procInfoColor]
    (@ID int)
AS
BEGIN
    SELECT Id, Code, [Description], IsActive FROM tblColor WHERE Id = @ID
END

VB.Net code:

Public Function infoColor(ID As Integer) As List(Of Colors) Implements iMaintenance.infoColor
    Dim p = New DynamicParameters()
    p.Add("@ID", ID, DbType.Int32)

    Try
        Return DbConn.Query(Of Colors)("procInfoColor", p, Nothing, 60, CommandType.StoredProcedure).ToList()
    Catch ex As Exception
    End Try
End Function

SQL Trace command (Text Data) - Command is generated by the VB.NET procedure call:

exec sp_executesql N'procInfoColor',N'@ID int',@ID=9

Error:

Msg 201, Level 16, State 4, Procedure procInfoColor, Line 2 Procedure or function 'procInfoColor' expects parameter '@ID', which was not supplied.

I can run this just fine when executing the procedure through a right click, but when I call the procedure from my VB.Net application I am getting this error message stating @ID is not being supplied. I checked the SQL trace and it sure looks like it is being supplied to me?

Any help at all would be appreciated.

Assuming you have cmd as your sql command then you would need to add it with cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID again assuming you have declared ID as integer etc dim ID as integer = 1 — Chicken
@Steve just added my call to show how its being supplied, like I said the trace shows "@ID=9" so its definitely making it there — haag1
@Chicken that was the entire proc haha, its very simple. All its used for is selecting a color from a dropdown and those 4 fields populate some text-box's that are then used as a way the user can change the record. The table structure doesn't have any duplicating ID fields nor does the procedure. I've asked around the office as well and everyone is just like "welp I have no idea" lol — haag1
The trace shows that the parameter is being passed to sp_executesql, but not to procInfoColor. The correct trace would be exec sp_executesql N'procInfoColor @ID',N'@ID int',@ID=9. — GSerg

Forty3 Forty3 · Accepted Answer · 2019-02-25T19:02:46

Possible duplicate of: Execute stored procedure w/parameters in Dapper

Try using the named parameter name for the Command Type when making the Dapper call:

Public Function infoColor(ID As Integer) As List(Of Colors) 
    Implements iMaintenance.infoColor

    Dim p = New DynamicParameters()
    p.Add("@ID", ID, DbType.Int32)

    Try
        Return DbConn.Query(Of Colors)("procInfoColor", 
                                       p, 
                                       Nothing, 
                                       60,
                                       commandType:=CommandType.StoredProcedure ).ToList()
    Catch ex As Exception
    End Try

End Function

"Procedure expects parameter which was not supplied..." SQL Trace indicates procedure gets the parameter

1 Answers