Setting a Date Variable to nothing or null?

[andycharger]

Im my code, i am assigning a declared Date Variable to a text box. However, if the text box is empty, I need to assign the variable to a null or empty.

 

How do I set a date variable to null so I can insert it into my db?

 

Here is my code:

 

Dim strDate as Date

if  Request.Form("date") = "" then

'code to go here

else

strDate = request.form("date")

end if

 

help!!!

[Heiko]

If you are working with strongly typed datasets, there should be a method for each property that allows to you to set it to null.

 

If you are submitting SQL directly you can just use the word "NULL" in SQL.

[andycharger]

Hmmm....

 

That does not really answer my question.

 

It just repeats what I have already said. i just need to know the right term to set the value of a date string to Null.

[Jay1b]

Why?

[andycharger]

Because I need to know

 

I need to set a datatype of DATE to a null in .net. Please answer it with an answer and not more questions!!!!

[NK2000]

first you need to know the type

lets say it is an int then just use

 

SqlInt32.Null

 

you can do that with all other types

just look at: System.Data.SqlTypes-Namespace

hope that link helps: http://ms-help://MS.VSCC/MS.MSDNVS.1031/cpref/html/frlrfsystemdatasqltypes.htm

[andycharger]

The link does not work.

 

However, I added the system.data.sqltypes to my project and did the following in my page:

 

						         if request.Form("txtPhoneDate") = "" then
strPhoneDate = SqlInt32.Null
else
strPhoneDate = request.Form("txtPhoneDate")
end if

 

When I run the project, I get the following error:

 

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

 

Compiler Error Message: BC30451: Name 'SqlInt32' is not declared.

[Derek Stone]

There is no such thing as a null [msdn=System.DateTime]DateTime[/msdn] value in .NET. The DateTime class is a value type, and as such it can not contain a null value. Your best bet would be to use [msdn=System.DateTime]DateTime[/msdn].MinValue instead.

[NK2000]

yeah it is like saying to find a time as no time existed

 

so maybe just set it to a particular date and if you read that date in a script then it is just like null, you might choose the 1.1.1970 (because of unix time format)

[Jay1b]

>I need to set a datatype of DATE to a null in .net. Please answer it with an answer and not more questions!!!!

 

There is no real reason to set it to a state of null. Hence the question, Why?. If you stated why you wanted to do it, we could suggest other (possible) work arounds to your problem.

[andycharger]

Jay!!!

The reason I want to set the date to null is that I have loads of date fields on my form. Some of which may not be filled in. However, if a date is not filled in, I do not want to enter it into the SQL Server database. Therefore, I want to insert NULL into the column.

 

How can I structure it to avoid this?

The DateTime.Parse just sets the DateTime variable to "00:00:00" then SQL server says this is an incorrect syntax.

 

Incorrect syntax near ':'.

 

So that does not work!

 

HELP!

[Jay1b]

Do you mean textboxes? There arent any form based date fields. Or am i missing something?

 

if textbox1.text = "" then...................

 

Or if i am missing something, couldnt you set the dates to an unrealistic date as default, and only enter them if they are not 0500AD?

[andycharger]

Stop the clock!

 

Ive rewritten my entire code now. I have set the DateTime to "00:00:00" in the asp.net code and then performed a load of if statements. If the value is = "00:00:00" then it leave that part of the sql statement out and the value to enter. Dynamic SQL statement basically.

WHAT A MESS!

Here is what I have done if anyone is interested:

  Public Sub DoComplaintInsert(ByVal strPrefix As String, ByVal strFirstname As String, ByVal strLastname As String, ByVal strBrokerRef As String, ByVal strPolNO As String, ByVal strDateRec As DateTime, ByVal strPostDate As DateTime, ByVal strPhoneDate As DateTime, ByVal strPhoneTime As DateTime, ByVal str1stRespDate As DateTime, ByVal str2ndRespDate As DateTime, ByVal str3rdRespDate As DateTime, ByVal strResolutionDate As DateTime, ByVal str3rdPartyDate As DateTime, ByVal strClaimsOfficerDate As DateTime, ByVal str3rdWho As String, ByVal strReason As String, ByVal strOtherREason As String, ByVal strAgainst As String, ByVal strInsurer As String)
       Dim strSQL As String
       Dim sConn As String
       Dim strTitle As String
       Dim strBody As String
       Dim local1 As String
       Dim local2 As String
       Dim local3 As String
       Dim local4 As String
       Dim local5 As String
       Dim local6 As String
       Dim local7 As String
       Dim local8 As String
       Dim local9 As String

       Dim local1a As String
       Dim local2a As String
       Dim local3a As String
       Dim local4a As String
       Dim local5a As String
       Dim local6a As String
       Dim local7a As String
       Dim local8a As String
       Dim local9a As String

       Dim oConn As SqlConnection

       If strPostDate = "00:00:00" Then
           local1 = ""
           local1a = ""


       Else
           local1 = "post_date,"
           local1a = "" & strPhoneDate & ","

       End If
       If strPhoneDate = "00:00:00" Then
           local2 = ""
           local2a = ""


       Else
           local2 = "phone_date,"
           local2a = "" & strPhoneDate & ","

       End If

       If str1stRespDate = "00:00:00" Then
           local4 = ""
           local4a = ""


       Else
           local4 = "first_resp_date,"
           local4a = "" & str1stRespDate & ","

       End If
       If str2ndRespDate = "00:00:00" Then
           local5 = ""
           local5a = ""


       Else
           local5 = "second_resp_date,"
           local5a = "" & str2ndRespDate & ","

       End If
       If str3rdRespDate = "00:00:00" Then
           local6 = ""
           local6a = ""


       Else
           local6 = "third_resp_date,"
           local6a = "" & str3rdRespDate & ","

       End If
       If strResolutionDate = "00:00:00" Then
           local7 = ""
           local7a = ""


       Else
           local7 = "resolution_date,"
           local7a = "" & strResolutionDate & ","

       End If
       If str3rdPartyDate = "00:00:00" Then
           local8 = ""
           local8a = ""


       Else
           local8 = "third_party_date,"
           local8a = "" & str3rdPartyDate & ","

       End If
       If strClaimsOfficerDate = "00:00:00" Then
           local9 = ""
           local9a = ""


       Else
           local9 = "claims_officer_date,"
           local9a = "" & strClaimsOfficerDate & ","

       End If
       'do the insert into the database
       strSQL = "insert into complaints (prefix, first_name, last_name, broker_ref, pol_no, date_comp_rec," & local1 & "" & local2 & "" & local4 & "" & local5 & "" & local6 & "" & local7 & "" & local8 & "" & local9 & " third_who, reason, other_reason, against, insurer, date_time) values ('" & strPrefix & "', '" & strFirstname & "', '" & strLastname & "', '" & strBrokerRef & "', '" & strPolNO & "', " & strDateRec & ", " & local1a & "" & local2 & "" & local4 & "" & local5 & "" & local6 & "" & local7 & "" & local8 & "" & local9 & "'" & str3rdWho & "' , '" & strReason & "', ' " & strOtherREason & "', '" & strAgainst & "', '" & strInsurer & "', getDate())"

[Jay1b]

Couldnt of done it messier, if i done it myself ;)