Assigning null values to SqlParameter DateTime

Question

I have a table like this

CREATE TABLE [dbo].[tblVolunteers]
(
    [VolID] [int] IDENTITY(1,1) NOT NULL,
    [VolFName] [varchar](20) NULL,

    [DLExpiration] [smalldatetime] NULL,

    CONSTRAINT [PK_tblVolunteers] PRIMARY KEY CLUSTERED 
    ([VolID] ASC),
    CONSTRAINT [uniqueUserID] UNIQUE NONCLUSTERED ([UserID] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And I have a stored procedure to update data on this table

ALTER PROCEDURE [dbo].[volUpdate]
    @StaffID INT,
    @ModifiedBy VARCHAR(100),
    @NickName VARCHAR(20),
    @FirstName VARCHAR(20), 
    @DLExpiration SMALLDATETIME
AS
    UPDATE tblVolunteers 
    SET
        VolNickName = @NickName, 
        VolFName = @FirstName, 
        DLExpiration = @DLExpiration
    WHERE 
        VolID = @StaffID

In some cases I don't have a valid DLExpiration to update at that time I want to pass null value to table. And I am doing it like this

new SqlParameter("@DLExpiration", null)

But still it throws an error

Procedure or function 'volUpdate' expects parameter '@DLExpiration', which was not supplied.

Can anyone point out What I am doing wrong here?

Could we see some more of your C# code? Just enough to give some context to the creation of your SqlParameter. — Matthew Haugen
– Matthew Haugen, Commented Aug 2, 2014 at 7:25

marc_s · Accepted Answer · 2014-08-02 08:00:56Z

2

You need to pass DBNull.Value to your stored procedure:

new SqlParameter("@DLExpiration", DBNull.Value)

answered Aug 2, 2014 at 8:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Assigning null values to SqlParameter DateTime

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