CODEWITHSUNDEEP
c#sql-server
rigamonk
rigamonk

Reputation: 1181

Adding Null Value To DATE Field In SQL SERVER

I have a table that has a field that is a Date. It allows nulls. I am trying to use a stored procedure to add a value to that field, but when I pass null, I get a

"Stored Procedure expects a value for @ReadyDate."

Here is the stored procedure:

Create PROCEDURE [dbo].[sp_UpdateStatus]
@id int,
@status varchar(20),
@ReadyDate Date
AS
BEGIN
    UPDATE PMF_ToolingRequests
        SET status = @status,
        ReadyDate = @ReadyDate
    WHERE ID = @id
END

Here is the C# code I am using to update the field. It balks when I send an empty string as well:

if (setReadyTime)
{
    cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = DateTime.Today.ToString("yyyy/MM/dd");
}
else
{
    cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = (DateTime?)null;
}

cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;

I also tried:

cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = null;

There is no value in that field at certain times. I would like to fill it in when the condition is met.

Upvotes: 0

Views: 1040

Answers (2)

psoshmo
psoshmo

Reputation: 1550

do

cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = DBNull.Value;

in essence, null in an object oriented language has a slightly different meaning than NULL in your DB, which is why

cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = null;

doesn't work.

Here is an MSDN article talking about DBNull if you want to do some reading.

Upvotes: 6

Dgan
Dgan

Reputation: 10285

try this:

Pass ReadyDate with Default value as null

Create PROCEDURE [dbo].[sp_UpdateStatus]
@id int,
@status varchar(20),
@ReadyDate Date=null
AS
BEGIN
    UPDATE PMF_ToolingRequests
        SET status = @status,
        ReadyDate = @ReadyDate
    WHERE ID = @id
END

Upvotes: 2

Related Questions