Skip to main content
Known Participant
October 9, 2009
Question

Simple Insert Query

  • October 9, 2009
  • 1 reply
  • 789 views

Hi everyone. I know this is going to sound elementary, but I've tried for almost a day to get this simple query to work, but I've had no luck. I've gone through it piece by piece, and recoded it from scratch, but haven't been able to figure it out. When I submit my form, I receive the "Syntax error in INSERT INTO statement" error. The datasource is defined, the table exists and the fields match up. The only thing to note is that there are two fields in my table, between ACT and PersonalU, that I am inserting data into.

I appreciate anyone's help. Thanks!

<cfquery name="scheduleApp" datasource="mainds">
INSERT INTO GUID:Appointments
(FirstName,LastName,EmailAddress,Counselor,ACT,PersonalU,PersonalNU,Schedule,Drop,Add,Transcript,School,OtherCheck,Other,Submitted)

VALUES ('#Trim(Form.FirstName)#','#Trim(Form.LastName)#','#Trim(Form.EmailAddress)#','#Form.Counselor#','#Form.ACT#','#Form.PersonalU#','#Form.PersonalNU#','#Form.Schedule#','#Trim(Form.Drop)#','#Trim(Form.Add)#','#Form.Transcript#','#Trim(Form.School)#','#Form.OtherCheck#','#Trim(Form.Other)#','#Today#')
</cfquery>

Table field properties
FirstName: Text
LastName: Text
EmailAddress: Text
Counselor: Text
ACT: Yes/No
PersonalU: Yes/No
PersonalNU: Yes/No
Schedule: Yes/No
Drop: Text
Add: Text
Transcript: Yes/No
School: Text
OtherCheck: Yes/No
Other: Memo
Submitted: Date/Time

    This topic has been closed for replies.

    1 reply

    Inspiring
    October 9, 2009

    Your code is too simple.  It will crash whenever any field contains an apostrophe.  cfqueryparam will solve this.

    Also, you say that some of your datatypes are yes/no.  You are trying to insert text into those fields.  I don't know if that is possible or not because I never use those datatypes.

    I debug problems like these as follows.  First run this:

    insert into table (

    field1

    <!--- , field2

    , field3

    etc

    , fieldx

    --->

    )

    values (

    value1

    <!--- , value2

    , value3

    etc

    , valuex

    --->

    )

    If that works, uncomment the fields and values one at a time until it crashes.  Then you'll know what field caused the problem.

    straffenpAuthor
    Known Participant
    October 10, 2009

    Thanks for the help, Dan. I found out that the problem was the name of the table. GUID is reserved, so once I changed the table name, the code worked fine.