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: 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