CODEWITHSUNDEEP
c#asp.net-web-apisql-server-2012entity-framework-6.net-4.5
user007
user007

Reputation: 1730

The parameterized query expects the parameter p1 which was not supplied

I have a stored proc as below:

CREATE PROCEDURE [dbo].[MyProc] 
    @p1 as int,
    @p2 as smalldatetime,
    @p3 as int,
    @p4 as varchar(255),
    @p5 as int = null,
    @p6 as numeric(18,2) = 0,
    @p7 as char(2) = null
AS

...

When I execute the below I get results:

EXEC dbo.MyProc
    @p1 = 0,
    @p2 = '5/29/2015',
    @p3 = NULL,
    @p4 = NULL,
    @p5 = 233,
    @p6 = 0,
    @p7 = NULL

But when I use Entity Framework's Database.SqlQuery, I get the The parameterized query '(@p1 bigint @p2 datetime @p3 nvarchar(4' expects the parameter '@p1' which was not supplied. Below is the code I used.

using (var context = new DbContext())
{   
    context.Database.ExecuteSqlCommand(
        @"EXEC dbo.MyProc @p1, @p2, @p3, @p4, @p5, @p6, @p7",
        new SqlParameter("p1", 0),
        new SqlParameter("p2", myDate), //myDate has value
        new SqlParameter("p3", DBNull.Value),
        new SqlParameter("p4", DBNull.Value),
        new SqlParameter("p5", myValue),//myValue has value
        new SqlParameter("p6", 0),
        new SqlParameter("p7", string.Empty));//I tried this with DBNull.Value; but no difference
}

Can anyone help?

Upvotes: 3

Views: 8713

Answers (3)

Umar Kayondo
Umar Kayondo

Reputation: 724

In addition to the above answers, I fixed this by sending in data as a string. For example;

context.Database.ExecuteSqlCommand(
                            "UPDATE event_triggers SET event_status=@event_status WHERE event_name=@event_name",
                            new SqlParameter("@event_name", $"{(int)triggerName}"),
                            new SqlParameter("@event_status", $"{(int)eventStatus}"));

Please take note of $"{your int value here}"

i.e

new SqlParameter("@event_status", $"{(int)eventStatus}")

Upvotes: 0

Slamj Jig
Slamj Jig

Reputation: 11

The "@" is optional when declaring a SqlParameter, with or without will work. When using the SqlParameter overload (string, object) you can cast a constant value such as 0 like this:

new SqlParameter("@p1", (object)0)

since it really does expect an object. This works with the newer Microsoft.Data.SqlClient assembly as well.

Upvotes: 1

user007
user007

Reputation: 1730

"For some reason when I pass 0, it is converted to BigInt. I do not know why. I parsed 0 to int and it worked.

using (var context = new DbContext())
{   
    context.Database.ExecuteSqlCommand(
        @"EXEC dbo.MyProc @p1, @p2, @p3, @p4, @p5, @p6, @p7",
        new SqlParameter("p1", int.Parse("0"),
        new SqlParameter("p2", myDate),
        new SqlParameter("p3", DBNull.Value),
        new SqlParameter("p4", DBNull.Value),
        new SqlParameter("p5", myValue),
        new SqlParameter("p6", int.Parse("0")),
        new SqlParameter("p7", DBNull.Value));
}

Upvotes: 9

Related Questions