[thelist] ASP - SQL Date Time Help

[Peter Brunone (EasyListBox.com)]

Hi Michael,

   If you insert an empty string into a field, the field will contain an empty string, not a null.

   You need to set strincome_no_enddate to null (just the word "null") so that the query looks like this:

INSERT INTO tblWhatever(field1, field2)
VALUES('thisone', null)

   Notice that there are no quotes around the null value.  If you put quotes around it (like you would if it were a datetime string), the DB will think you want to insert a string containing the word null, which will of course result in another error.

   Another approach is just not to insert anything for that field, but that may be a bit more coding to change the structure of your query.

   One last thing... are you using an ampersand (&) as an "And" operator in your If...Then statement?  I didn't think that was possible.

Regards,

Peter Brunone
_______________
EasyListBox.com

Original Message:
>From: "Michael Pack" <michaelpack at wvdhhr.org>

>Hi all, I'm having a problem inserting an empty value into a datetime
>field in SQL Server 2000 database. The field is not required in the form
>so I must allow for NULL.
>
>I am concatenating my string as follows
>
>if request.Form("income_no_end_month") = "" &
>request.Form("income_no_end_day") = "" &
>request.Form("income_no_end_year") = "" then
>strincome_no_enddate = ""
>else
>strincome_no_enddate = request.Form("income_no_end_month") & "/" &
>request.Form("income_no_end_day") & "/" &
>request.Form("income_no_end_year")
>end if
>
>I have the SQL Database field set to accept NULLS, it is a datetime
>field. 
>
>I keep getting the following error when I run the transaction without
>selecting an end date in the form:
>
>Microsoft OLE DB Provider for SQL Server (0x80040E07)
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value.
>
>I've done a response.Write for the string and it is empty.
>
>Any assistance is greatly appreciated.
>
>MP