How can I pass a null value for date in vb.net to sql stored procedure?

Question

My application is asp.net with vb.

In my page I have a textbox for passing date. If I didn't enter date and on clicking submit, I have to pass null value to the stored procedure.

I tried following codes such as DBNull.Value and DateTime.MinValue. In that case instead of null, "#12:00:00#" is passing. I have to pass Null.

Binoj Antony · Accepted Answer · 2009-03-16T07:53:23.787

21

Just assign the parameter the value DBNull.Value

[EDIT]

If you are using SqlParameter (thanks Jon) then you can set it like this if it gives any error

parameter.Value = DBNull.Value

edited Mar 16 '09 at 07:53

answered Mar 16 '09 at 06:32

Binoj Antony

15,886
25
88
96

it gives a conversion problem.can it be solved by Convert.ToDateTime(DBNull.Value) – Nandini Mar 16 '09 at 06:35
DBNull.Value cannot be used as it generates an error "Object cannot be cast from DBNull.Value to other types" – Nandini Mar 16 '09 at 06:40
The code given should be fine, if "parameter" is a SqlParameter. – Jon Skeet Mar 16 '09 at 07:44

score 0 · Answer 2 · answered Apr 01 '11 at 10:35

Dim pflt_dateto As SqlParameter = cmd.CreateParameter
        pflt_dateto.Direction = ParameterDirection.Input
        pflt_dateto.DbType = SqlDbType.Int
        pflt_dateto.IsNullable = True
        pflt_dateto.ParameterName = "flt_dateto"

        If String.IsNullOrEmpty(dateto) Then
            pflt_dateto.SqlValue = DBNull
        Else
            pflt_dateto.SqlValue = dateto
        End If

        cmd.Parameters.Add(pflt_dateto)

Since you set up the parameter nullable, the error will disappear. Now the issue is that .NET pass null integer as 0 so we need to deal with that.

Chao

Shouldn't this line be `pflt_dateto.SqlValue = DBNull.Value` ? I got the error: "DBNull is a type and cannot be used as an expression" without `.Value` — AllisonC, May 02 '17 at 12:35

How can I pass a null value for date in vb.net to sql stored procedure?

2 Answers2

Linked