Solved

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

Posted on 2008-06-12
6
1,523 Views
Last Modified: 2013-12-17
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
Comment
Question by:123456789y
  • 2
6 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 21768762
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
 

Author Comment

by:123456789y
ID: 21776161
'.................................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
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 21777579
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
 

Expert Comment

by:kumar_svr
ID: 23032403
You can set the Default value for that Date column to Null.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now