PROBLEM with CFML text fields inserting dates in MySQL!

[kelani]

Take this simple little script with 3 fields:

id
name
birthday

How can I get it so a user can simply enter their birthday into a text field, And have have MySQL accept it? When I try the following it shows success, but the record is not saved. If I remove the birthday field, everything works normally, of course. Here's the relevant (partial) code

<CFINPUT TYPE=&quot;text&quot; NAME=&quot;birthday&quot; VALUE=&quot;&quot;> and then when the form is submitted through:

<cfquery name=&quot;saveme&quot; datasource=&quot;dbase&quot; dbtype=&quot;ODBC&quot; username=&quot;username&quot; password=&quot;password&quot; dbserver=&quot;localhost&quot; dbname=&quot;database&quot;>
INSERT into foo values (#id#,#name#,#birthday#</cfquery>

I don't use Validation because it required downloading the CAB file, and lots of MSIE users have ActiveX turned off. Plus, when I did try it, with both date and eurodate validation, it still didn't work.

CFINSERT does the same thing.

I've tried hundreds of combinations of DATEFORMATs and such and they all give errors. I know I could have 3 more fields, one for month, day and year, but for my purposes this is the most efficient way. In other tables, pulling the data out,or setting a NOW() date is no problem, just this user input scenario.

Any masters out there care to part with a bit of wisdom?

Many thanks in advance

Kelani

 

[SQAlisha]

I think having three combo box is efficent........in this way, there in no way it can create a error..nor u have to do a error checking...

In this way u can solve the problem....
U juz insert in #DateFormat(mm/dd/yyy)# into the cfinsert query...(mm->month,dd->day,yyyy-year)
remember to put the month first.

Cheers
Alisha

 

[rac13]

I think there is something wrong in this query:

<cfquery name=&quot;saveme&quot; datasource=&quot;dbase&quot; dbtype=&quot;ODBC&quot; username=&quot;username&quot; password=&quot;password&quot; dbserver=&quot;localhost&quot; dbname=&quot;database&quot;>
INSERT into foo values (#id#,#name#,#birthday#</cfquery>

Are you using this?
If you are, try this:

<cfquery name=&quot;saveme&quot; datasource=&quot;dbase&quot; dbtype=&quot;ODBC&quot; username=&quot;username&quot; password=&quot;password&quot; dbserver=&quot;localhost&quot; dbname=&quot;database&quot;>
INSERT into foo
(FieldName_ID, FieldName_Name, FieldName_birthday)
values (#id#,#name#,#birthday#</cfquery>

 

[SQAlisha]

I think rac13 is rit..I didn't notice that........


Cheers
Alisha

 

[kelani]

Actually, a developer on the cold fusion suppport board posted another answer that helped. There was nothing wrong with my query, per se, but in getting the date to jive with MySQL

He suggested doing a normal CFQUERY - insert into table (fields) VALUES (values) but with this instead:

'#DateFormat(CreateODBCDate(form.bday), &quot;yyyy/mm/dd&quot#')

which works perfectly.

Thanks!