[thelist] ASP - SQL Date Time Help
Peter Brunone (EasyListBox.com)
peter at easylistbox.com
Wed Aug 4 11:53:22 CDT 2004
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
More information about the thelist
mailing list