"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, Feb 25 '19 at 16:25
@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, Feb 25 '19 at 16:26
Just out of curiosity, ID DEFINATELY is a int right? As in, within the SQL Db table too...? Tbh I've not seen connection to SQL done this way before. — Chicken, Feb 25 '19 at 16:36
`exec sp_executesql N'procInfoColor @ID',N'@ID int', @ID = 9` this will work. I guess you are using Dapper wrong. — Zohar Peled, Feb 25 '19 at 16:37
@Chicken yes it is an int, I like doing it this way because it makes it very easy to read (especially when there are multiple parameters), it isn't an issue that there is only one either because I am using it elsewhere with no issues — haag1, Feb 25 '19 at 16:39
@haag1 It's good, I am intruiged to see how it works. The only thing I can 'Guess' at is there are two `@ID`'s and maybe is requesting one of them from the parameter and not the other? I'm just guessing completely, can you post any more code, maybe show the entire proc at all? — Chicken, Feb 25 '19 at 16:46
@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, Feb 25 '19 at 16:56
Have you tried assigning the `@ID` outside of the execute, so set it first and then just use the `@ID`? — Trevor, Feb 25 '19 at 17:59
@Çöđěxěŕ that exec is what is being produced and sent to my server via the .net code, i can't change the format it is writing — haag1, Feb 25 '19 at 18:43
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, Feb 25 '19 at 19:11

Forty3 · Accepted Answer · 2019-02-25T19:10:29.277

4

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

edited Feb 25 '19 at 19:10

answered Feb 25 '19 at 19:02

Forty3

2,199
1
14
19

Oh wow, that did the trick! I can't believe that is necessary, thank you very much! Though I do have to say this is odd, definitely not something I've had to do at my last job where we used Dapper all the time, does it depend on your versions of either .NET or SqlServer? – haag1 Feb 25 '19 at 19:14
I think it depends on how the compiler interprets the matching method signature. I can't find a good reference for Dapper to see the various method signatures to see if the `CommandType` enum is being interpreted as an `int` for some other signature. – Forty3 Feb 25 '19 at 19:16
1

@haag1 There are [more parameters](https://dapper-tutorial.net/query) than you are specifying, and without the `commandType:=` your `CommandType.StoredProcedure` gets passed to a wrong parameter. You probably have `Option Strict Off`, turn that to `On`. – GSerg Feb 25 '19 at 19:19
@Forty3 looking closer, there is one inbetween Transaction (nothing) and timeout (60) that is called "Buffered" with a default value of true... I assumed this wasn't needed to be specified since it had a default. When I added in a true it allowed me to still use `CommandType.StoredProcedure` without using the named parameter. Just one of those quarky things, I guess it couldn't tell what `CommandType.StoredProcedure` belonged to since I didn't have everything defined. – haag1 Feb 25 '19 at 19:19
@GSerg yeah you are absolutely correct. Thanks for the help! – haag1 Feb 25 '19 at 19:19

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

1 Answers1