Assign NULL into SQL Server datetime column using vb.net in asp.net

Question

I have 3 textboxes in my app. the 3rd one is for date of birth which user can keep empty.

The app saves the values entered by the user into a SQL Server database that has a table with 3 columns. The birthday column's datatype is set to Date and it is defined as nullable.

If the user keeps the birthday textbox empty, I want to assign NULL into the birthday column, so I tried this code:

If txtBirthday.Text = "" Then
    command.Parameters.AddWithValue("@Field3", DBNull.value)
End If

But that didn't work. I also tried (after adding Imports System.Data.SqlTypes):

If txtBirthday.Text = "" Then
   command.Parameters.AddWithValue("@Field3", SqlDateTime.Null)
End If

But that also didn't work.

I looked for a solution for the problem, but no success.

Please help

Here Is my full code:

Dim Query As String = "INSERT INTO UserInfo (fName, lName, bDay) VALUES (@Field1, @Field2, @Field3);"
Dim connectionString = ConfigurationManager.ConnectionStrings("myapp_con_string").ConnectionString
Using connection As New SqlConnection(connectionString)
    Dim command As New SqlCommand(Query, connection)
    With command
       .Connection = connection
       .CommandType = CommandType.Text
       .CommandText = Query
       .Parameters.AddWithValue("@Field1", txtFirstname.Text)
       .Parameters.AddWithValue("@Field2", txtLastname.Text)
       If txtBirthday.Text = "" Then
           command.Parameters.AddWithValue("@Field3", DBNull.value)
       Else
           command.Parameters.AddWithValue("@Field3", Convert.ToDateTime(txtBirthdat.Text).ToString("yyyy/MM/dd"))
       End If
   End With
   Try
       connection.Open()
       command.ExecuteNonQuery()
       lblMessages.Text = "Data Saved"
   Catch ex As SqlException
       lblMessages.Text = "Data Not Saved"
   End Try
End Using

This is the exception I got:

{System.Data.SqlClient.SqlError: Conversion failed when converting date and/or time from character string.}

Looks like it should work. Could you please show us the stored procedure or command text? — NoAlias, Dec 16 '17 at 01:07
Just for grins try cmd.Parameters.Add("@Field3", SqlDbType.Date).Value = DBNull.Value — Mary, Dec 16 '17 at 05:16
Does the text box have a mask?...is it truly empty? Trace out the value replacing spaces to verify it's truly blank — Ctznkane525, Dec 16 '17 at 02:41
(N0Alias) - I Updated my question including the command text...... (John Kane) - No it does not has any masks..... (Mary) - Tryed your suggestion, but did'nt work. It gives the same exception — Masarwa, Dec 16 '17 at 09:29

score 0 · Answer 1 · answered Dec 16 '17 at 05:26

0

If you are using a stored procedure, then try the following.

Modify the SP Parameters to set default parameter values

CREATE PROCEDURE
(
@Name VARCHAR(50),
@Email VARCHAR(255),
@DOB DATE = NULL
)
AS
BEGIN
<Your Code>
END

This means. @Name and @Email are mandatory but @DOB will be NULL if you don't pass a value for that.

Now Don't bind the 3rd parameter if the user hasn't entered a value for Date of Birth and it will be taken as NULL by SQL

answered Dec 16 '17 at 05:26

Jayasurya Satheesh

7,826
3
22
39

am not using a stored procedure, but your approach is a nice way to solve the problem... I can manipulate the sql query to not include date fields that are empty, and by not refering to these fields in the query, the database fields will hold the NULL value... I'll try it' and if it works I'll put my code in an answer... THANKS!!!! – Masarwa Dec 16 '17 at 12:51

Assign NULL into SQL Server datetime column using vb.net in asp.net

1 Answers1