[thelist] ASP - SQL Date Time Help

[Michael Pack]

Thank you all for your help.  AND thanks Matt for bringing up using OR
in the if statement. Since there is no client side validation on those
fields that could have caused problems.

I finally got the insert to work but NULL is being converted to
"1/1/1900" .  That led me to finding this
http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp
. Now I'm searching for the ASP/VB way to do it.

Peter, I use & and AND and have no problems. I think AND is a bit more
legible.

Many thanks, I'm getting closer!

MP

>>> "Peter Brunone (EasyListBox.com)" <peter at easylistbox.com> 8/4/2004
12:53:22 PM >>>
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


-- 
* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/ 

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt !