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

  • 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

    Wednesday, June 19, 2019 8:16 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 19, 2019 8:32 PM

All replies

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 19, 2019 8:32 PM
  • User1028962535 posted

    Thanks alot, code is working now!

    Thursday, June 20, 2019 5:21 AM