Solved

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

Posted on 2008-06-12
6
1,522 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PDF writer for SBS 2008 11 76
VTP LOG RUNTTIME ERROR 31 125
What does Survey Monkey cost? 7 82
Any free software for printscreens 7 65
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

747 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

11 Experts available now in Live!

Get 1:1 Help Now