Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-12
6
Medium Priority
?
1,539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
Viewers will learn how to use the Hootsuite Dashboard.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

715 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