|
| |
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
 |
Date field insert problem - 4/1/2008 10:48:58
Hey all...
I have a page with 5 calendar fields on it. Depending on the user he/she should be able to submit any of the dates to the database. The problem I am running into is when a user does not submit all. When that happens my database gives me an error such as the one below. It changes depending on what field was left blank.
Database Results Error
Description: [MySQL][ODBC 3.51 Driver][mysqld-5.0.51a-community-nt]Incorrect date value: '' for column 'Vacation1' at row 1
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers
I added code to the top of the submit page to show you what values are being passed.
StartVacation:2008-03-01
EndVacation:2008-07-01
Vacation1:
Vacation2:
Vacation3:
vacation:vacation
Submit1:submit
My database is MySQL 5.0 community and the fields are all set to DATE.
This is my SQL code from my insert page.
INSERT INTO vacation2 (StartVacation,EndVacation,Vacation1,Vacation2,Vacation3,UserID,DateEntered) VALUES ('" & request.form("StartVacation") & "','" & request.form("EndVacation") & "','" & request.form("Vacation1") & "','" & request.form("Vacation2") & "','" & request.form("Vacation3") & "', '" & user & "',curdate())
Any help is greatly appreciated!
Thank you
_____________________________
No matter where you go, there you are.
|
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Date field insert problem - 4/1/2008 11:27:18
I usually fix those kinds of issues with a function. You can kinda' do it 2 ways; with Nulls (if the DB allows) or a 'dummy date' like 1/1/1900.
I build a function like this:
<%Function fixTheDate(dateIn)
If IsDate(dateIn) Then
fixTheDate = "'" & dateIn & "'"
Else
fixTheDate = "Null"
End If%>
And your SQL string would look like so:
...) VALUES (" & fixTheDate(request.form("StartVacation")) & "," & fixTheDate(request.form("EndVacation")) & ",...
Notice I removed the apostrophes from the SQL statement and put them in the function since if we're sending Null, we *don't* want the apostrophes.
If you want to use the 'dummy date' method, change the line in the function from:
fixTheDate = "Null"
To:
fixTheDate = "'1900-1-1'"
or whatever format is appropriate.
That any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: Date field insert problem - 4/1/2008 13:06:42
rdouglass....
Thanks !!
That made a lot of sense! I tried it out and received this error.
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/_fpclass/fpdblib.inc, line 3
Any ideas?
_____________________________
No matter where you go, there you are.
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Date field insert problem - 4/1/2008 13:09:54
Can you response.write the SQL to the screen to see exactly what we're passing?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
