coldfusion mysql error

[windsorben]

Anyone see what I'm doing wrong?
Here's the form field:

<input type="hidden" name="date" value="<cfoutput>#DateFormat(Now(), "MM/DD/YY")# At #TimeFormat(Now(),"hh:mm tt")#</cfoutput>" size="32">
 

Here's the sql insert:

  <cfif IsDefined("FORM.date") AND #FORM.date# NEQ "">
    '#FORM.date#'
      <cfelse>
    NULL
  </cfif>
 

,

(NOTE: I've tried with and without the single quotes around #FORM.date#

Here's the error.

14 : <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
15 :   
16 :   <cfquery datasource="">
17 :     INSERT INTO scoretable (puzzleid, studentid, pointpossible, score, time, date, percentage)
18 :     VALUES (
 
 

--------------------------------------------------------------------------------

SQL    INSERT INTO scoretable (puzzleid, studentid, pointpossible, score, time, date, percentage) VALUES ( NULL , 111 , 9 , 9 , 23 , '12/16/06 At 12:47 AM' , 100% )  
DATASOURCE    
VENDORERRORCODE   1064 
SQLSTATE   42000

[bishwadeep]

Try the following code:
I think you have error while inserting the date
do you have separate field for date and time?

<form action="" method="post">
<input type="hidden" name="date" value="<cfoutput>#DateFormat(Now(), "MM/DD/YY")#</cfoutput>">
<input type="hidden" name="time" value="<cfoutput>#TimeFormat(Now(),"hh:mm tt")#</cfoutput>">
</form>
<cfif IsDefined("FORM.date") AND #FORM.date# NEQ "">
'#FORM.date#'
<cfelse>
NULL
</cfif>
 
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfquery datasource="">
INSERT INTO scoretable (puzzleid, studentid, pointpossible, score, time, date, percentage)
VALUES (NULL , 111 , 9 , 9 , 23 ,'#form.time#','#form.date#',100% )
</cfquery>
</cfif>

[acoder]

Anyone see what I'm doing wrong?
Here's the form field:
<input type="hidden" name="date" value="<cfoutpu t>#DateFormat(N ow(), "MM/DD/YY")# At #TimeFormat(Now (),"hh:mm tt")#</cfoutput>" size="32">

Here's the sql insert:
<cfif IsDefined("FORM .date") AND #FORM.date# NEQ "">
'#FORM.date#'
<cfelse>
NULL
</cfif>
,

(NOTE: I've tried with and without the single quotes around #FORM.date#

Here's the error.

14 : <cfif IsDefined("FORM .MM_InsertRecor d") AND FORM.MM_InsertR ecord EQ "form1">
15 :
16 : <cfquery datasource="">
17 : INSERT INTO scoretable (puzzleid, studentid, pointpossible, score, time, date, percentage)
18 : VALUES (



--------------------------------------------------------------------------------

SQL INSERT INTO scoretable (puzzleid, studentid, pointpossible, score, time, date, percentage) VALUES ( NULL , 111 , 9 , 9 , 23 , '12/16/06 At 12:47 AM' , 100% )
DATASOURCE
VENDORERRORCODE 1064
SQLSTATE 42000

A number of points:

1. Why are you passing the date as a hidden field if you're using today's date anyway. Just use the code on the page that contains the query.
2. Date should be in the recognised format for the mysql database. I doubt that the format you are using is correct. Search for acceptable date formats. Remove the 'At' - surely that is not needed.
3. In you query, you have Null for puzzleid which is definitely incorrect - you can't have a null value for a primary key.
4. For time, you have a value of 23. what does that mean?
5. As I explained, the date is of the wrong format.
6. The percentage sign % holds special significance in SQL. Just use 100 instead.
7. Perhaps, you could consider changing your database, e.g. no need to store percentage because it can easily be calculated. also, the puzzle table will contain the pointpossible value which you can easily obtain so no need to store that in the scoretable either.
8. Date/time should be one field.

Quite a few things there for you to think over..

[RedSon]

Next time, please remember that this is a website so posting sensitive information about your company or posting code/text that can be easily googled by your company managers is probably not a good idea.