Unable to insert null values in nullable column via stored procedure in C#

Question

I have a SQL table where column is set to null. Now I am passing the null value from code via stored procedure and it throws an error which is, Procedure or function 'MyStoredProcedure' expects parameter '@Parameter', which was not supplied.

Now Confusing part for me is, if I add null condition and than pass DBNull.Value, it works fine but if I pass direct parameter which could be null than it throws error. I am trying to understand why? Am I missing something here?

Note: Just for information, passing parameter could be null based on the requirement.

Code:

string connString = _dbContext.Database.Connection.ConnectionString;
using (SqlConnection connection = new SqlConnection(connString))
{
    connection.Open();

    SqlCommand cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    scheduledEndDateTime = !string.IsNullOrEmpty(bulkUpdateSchedule.EndDate) && !string.IsNullOrEmpty(bulkUpdateSchedule.EndTime) 
    ? (DateTime?) 
    Convert.ToDateTime(bulkUpdateSchedule.EndDate + " " + bulkUpdateSchedule.EndTime)
    : null;

    //This Fails
    cmd.Parameters.AddWithValue("@scheduledEndDateTime", scheduledEndDateTime);

    //This Works
    if (scheduledEndDateTime != null)
    {
        cmd.Parameters.AddWithValue("@scheduledEndDateTime", scheduledEndDateTime);
    }
    else
    {
        cmd.Parameters.AddWithValue("@scheduledEndDateTime", DBNull.Value);
    }

    cmd.ExecuteReader();

    connection.Close();
}

Stored Procedure:

ALTER PROCEDURE [dbo].[InsertScheduledBulkUpdateRecords]
    @scheduledEndDateTime DATETIME,
AS
BEGIN
    SET NOCOUNT ON;
        INSERT INTO ScheduledBulkUpdate (ScheduledEndDateTime)
        VALUES (@scheduledEndDateTime)
END

Table Structure screen shot:

you can assign default value is SP though: @scheduledEndDateTime DATETIME = null — Ehsan Sajjad
– Ehsan Sajjad, Commented Feb 12, 2016 at 21:12
@EhsanSajjad So I can only go through this if...else route. — GThree
– GThree, Commented Feb 12, 2016 at 21:12
instead of if...else you can potentially use (object)scheduledEndDateTime ?? DBNull.Value to coalese the null value. See ?? on MSDN. Note: you need the object cast to make the two sides comparable. — NikolaiDante
– NikolaiDante, Commented Feb 12, 2016 at 21:22

NikolaiDante · Accepted Answer · 2016-02-12 21:13:20Z

5

You've pretty much answered your own question null and DBNull.Value are different things. Where DBNull.Value means something to the database provider than null doesn't.

From MSDN on DBNull:

Do not confuse the notion of null in an object-oriented programming language with a DBNull object. In an object-oriented programming language, null means the absence of a reference to an object. DBNull represents an uninitialized variant or nonexistent database column.

answered Feb 12, 2016 at 21:13

NikolaiDante

18.7k14 gold badges84 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to insert null values in nullable column via stored procedure in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related