Date field insert problem (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

sentinel -> 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



rdouglass -> 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?



sentinel -> 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?



rdouglass -> 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?



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625