CODEWITHSUNDEEP
.netvb.netentity-framework
Chris
Chris

Reputation: 815

Entity Frameworks ExecuteSqlCommand Null parameter giving error

I am using Entity Framework 5. When I call a stored procedure where the parameter is null, I get

"stored proc" expects parameter x which was not supplied.

I need to be able to use ExecuteSqlCommand even if its parameters are null:

 Sub SaveMANUALPosting(ByVal ExpiryDate AS DateTime)

    db.Database.ExecuteSqlCommand("exec spLC_STAGEPostingLCAmendment @argExpiryDate", 
     New SqlParameter("argExpiryDate", If((IsNothing(ExpiryDate)), DBNull.Value, ExpiryDate)))

 end Sub

The above code does not work of course, but illustrates what I am trying to do. How can I pass a DbNull value if the incoming parameter is null?

Upvotes: 2

Views: 2863

Answers (1)

Chris
Chris

Reputation: 815

Got it to work by creating a function. Here is the code:

 Sub SaveMANUALPosting(ByVal ExpiryDate AS DateTime)

     db.Database.ExecuteSqlCommand("exec spLC_STAGEPostingLCAmendment @argExpiryDate", 
      New SqlParameter("argExpiryDate", FormatDBValue(ExpiryDate))

 end Sub

 Protected Function FormatDBValue(ByVal pIn As Object) As Object
    If IsNothing(pIn) Then
        Return DBNull.Value
    Else
        Return pIn
    End If
 End Function

The function will return either dbnull.value or the parmeter value if it is not null. The second parm of "SqlParameter" expects an object, so the function provides that.

Thanks for the replies. Got me thinking...

Upvotes: 2

Related Questions