Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1542
  • Last Modified:

Insert Null Value instead of 1/1/1900 into SQL Server TAble.

Hi
  I am Using ASP.NET2005 With SQLServer2000 As Back end. I am Inserting Empty value From Textbox into SmallDateTime DataType Column . But  By default it Takes 1/1/1900.
  so tell me how should i insert Null VAlue instead of 1/1/1900 into column.

Thanks
0
123456789y
Asked:
123456789y
  • 2
1 Solution
 
TimCotteeCommented:
Hello 123456789y,

Use DBNUll.Value from asp.net if using parameters, otherwise explicitly specify "Null".

If you can show the relevant portion of your code then it would be easier to explain.

Regards,

TimCottee
0
 
123456789yAuthor Commented:
'.................................Date Convertion From dd/MM/yyyy To MM/dd/yyyy..
            If txtdate.Text <> "" Then
                txtdate.Text = Convert.ToDateTime(txtdate.Text).ToString("MM/dd/yyyy")
            End If
            If txtdeparturedate.Text <> "" Then
                txtdeparturedate.Text = Convert.ToDateTime(txtdeparturedate.Text).ToString("MM/dd/yyyy")
            End If
'These Two Upper Lines Basically used for convert value 22/5/2008 to 5/22/2008.
  str = "INSERT INTO tbl_inquiry([inqdate],[departuredate])VALUES('" & txtdate.Text & "','" & txtdeparturedate.Text & "')
comm = New SqlCommand(str, conn)
            comm.ExecuteNonQuery()

so TimCottee.....
 plz tell me if i am not insert value into txtdeparturedate.text so it should be take Null bydefault instead of 1/1/1900

Thanks
0
 
TimCotteeCommented:
123456789y,

            If txtdate.Text <> "" Then
                txtdate.Text = Convert.ToDateTime(txtdate.Text).ToString("MM/dd/yyyy")
            End If
            If txtdeparturedate.Text <> "" Then
                txtdeparturedate.Text = Convert.ToDateTime(txtdeparturedate.Text).ToString("MM/dd/yyyy")
            Else
                txtdeparturedate.Text = "Null"
            End If
'These Two Upper Lines Basically used for convert value 22/5/2008 to 5/22/2008.
  str = "INSERT INTO tbl_inquiry([inqdate],[departuredate])VALUES('" & txtdate.Text & "','" & txtdeparturedate.Text & "')
comm = New SqlCommand(str, conn)
            comm.ExecuteNonQuery()

Using this method.

You may want to consider converting to using parameters for a number of reasons, partly because they help in restricting sql injection attacks and also because it improves readability and flexibility. A parameterised version of this would look somthing like:

  str = "INSERT INTO tbl_inquiry([inqdate],[departuredate])VALUES(@Date,@DepartureDate)"
comm = New SqlCommand(str, conn)
comm.Parameters.AddWithValue("@Date",Convert.ToDateTime(txtdate.Text).ToString("MM/dd/yyyy"))
comm.Parameters.AddWithValue("@DepartureDate",IIf(txtDepartureDate.Text = "",DBNull.Value,Convert.ToDateTime(txtdeparturedate.Text).ToString("MM/dd/yyyy")))
            comm.ExecuteNonQuery()

TimCottee
0
 
kumar_svrCommented:
You can set the Default value for that Date column to Null.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now