Date is saving with 01/01/1900 00:00:00 even though textbox is null/empty

Question

User1028962535 posted

Hello..I am using my web form to save some data, the typical text data saves fine, if the value in the textbox is empty or null, nothing is saved to the SQL table. Dates on the other hand default to 01/01/1900 00:00:00 when the textbox is empty. How can I prevent this from happening please?

The dob field is set to date data type in SQL and is set to allow NULLS in the table and also set to default to Null is the stored procedure - @Dateofbirth date = Null,

Any help is appreciated..

My code so far:

Dim conn As SqlConnection
Dim comm As SqlCommand
Dim connectionstring As String = ConfigurationManager.ConnectionStrings("Locust").ConnectionString
conn = New SqlConnection(connectionstring)
comm = New SqlCommand("spaddcart", conn)
comm.CommandType = System.Data.CommandType.StoredProcedurecomm.Parameters.Add(New SqlParameter("@Firstname", txtfirstname.Text))
comm.Parameters.Add(New SqlParameter("@Lastname", txtlastname.Text))
comm.Parameters.Add(New SqlParameter("@Fullname", txtfullname.Text))
comm.Parameters.Add(New SqlParameter("@Dateofbirth", txtdob.Text))    --------------------THIS FIELD IS THE PROBLEM

Try
conn.Open()
comm.ExecuteNonQuery()

Catch ex As Exception
Response.Write("Error" & ex.ToString())
errorlabel.Text = "Error retrieving user data"
Finally

Answer 1

User475983607 posted

You are not passing a null.  Your passing an empty string and SQL casts the empty string to zero.  Write a bit of code to test the txtdob input.

If(!string.IsNullOrEmpty(txtdob.Text)) Then
	comm.Parameters.Add(New SqlParameter("@Dateofbirth", txtdob.Text))
Else
	comm.Parameters.Add(New SqlParameter("@Dateofbirth", DBNull.Value))
End If

You might need to fix the stored procedure input parameter to accept null as well.  Not sure can't see that part of the code.

Answer 2

User1028962535 posted

Thanks alot, code is working now!