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
Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
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.
This video shows how use content aware, what it’s used for, and when to use it over other tools.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

809 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