[thelist] ASP - SQL Date Time Help

Peter Brunone (EasyListBox.com) peter at easylistbox.com
Wed Aug 4 14:35:26 CDT 2004


   Hmm... that's really weird that it's putting in that date.  Have you done a Response.Write on your SQL string to make sure you're inserting what you think you're inserting?  Also, any chance there's a default value on that field?

Cheers,

Peter

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

>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 ! 
>-- 
>* * 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 ! 
>
>




More information about the thelist mailing list