from cfinsert/cfupdate to cfquery: Now Getting Syntax Error on UPDATE? 2

[newcow]

Ok I am ready to change out the cfupdate/cfinsert. Hopefully this will take car of the AC checkbox field not taking.
This is what I have to process the cancel/save buttons on my caradd.cfm and caredit.cfm :
<!---CANCEL BUTTON--->
<cfif IsDefined("Form.btnCancel")>
<cfif IsDefined("Form.RecordID")>
<cflocation url="cardetail.cfm?ID=#Form.RecordID#">
<cfelse>
<cflocation url="cardetail.cfm">
</cfif>
<cfelse>
<!---PROCESS SAVE BUTTON--->
<cfset isOk = "Yes">
<cfif isOk EQ "Yes">
<cfif isdefined("form.ID")>
<!---update record--->
<cfupdate datasource="Cardata" tablename="tblCars">
<cflocation url="cardetail.cfm?ID=#Form.ID#">
<cfelse>
<!---create new record--->
<cfinsert datasource="Cardata" tablename="tblCars">
<cflocation url="cardetail.cfm">
</cfif>
</cfif>

Ok I going ahead and am changing the UPDATE:
<!---update record--->
<cfquery name="UpdateQuery" datasource="Cardata">
UPDATE tblCars
SET Status=#form.Status#, Year=#form.Year#, Make=#form.Make#, Model=#form.Model#, VN=#form.VN#, Style=#form.Style#, Price=#form.Price#, Mileage=#form.Mileage#, Cylinder=#form.Cylinder#,
ect...
WHERE tblCars.ID = #Form.ID#
</cfquery>
<cfelse>
<!---create new record--->

I am getting a error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '6 CYL'.

This is the SQL statement:

UPDATE tblCars SET Status=SOLD, Year=2002, Make=Mercedes, Model=E320W4M, VN=1, Style=Sedan, Price=49000, Mileage=44000, Cylinder=6 CYL, Displacement=3.5L, Fuel=Gas, Transmission=Automatic, Doors=2, ExtColor=Silver, IntColor=Black, DateAdded=02/14/2004, VIN=45835923484800948, Warranty=3 Year, ThumbPic=800948.jpg, Drive=Rear Wheel, Dealer=Goodwills, AC=1, ABSBrakes=1, AlloyWheels=1, Alarm=0, BabySeat=0, CentralLocking=1, CruiseControl=1, DriverAirBag=1, FrontFogLights=1, HeadlightWipers=0, HeatedSeats=1, Immobiliser=1, KeylessEntry=1, LeatherSeats=1, ManualSunRoof=0, MetallicPaint=1, PassengerAirBag=1, PowerAntenna=1, PowerDoorLocks=1, PowerMirrors=1, PowerSeats=1, PowerSteering=1, PowerSunRoof=0, PowerWindows=1, QuadSeats=0, RearHeadRests=1, RemoteAlarm=1, RemoteLocking=1, RemoteStart=0, RoofRack=0, Spoiler=0, SportSuspension=0, TiltSteering=1, TractionControl=1, Radio=1, Cass=1, CDPlayer=1, MultiCD=1 WHERE tblCars.ID = 30

Any ideas on this one?
newcow

 

[newcow]

Ok here is the update on my convertion from using cfinsert/cfupdate to using cfquery. Here is my old code:
<!---update record--->
<cfupdate datasource="Cardata" tablename="tblCars">
<cflocation url="cardetail.cfm?ID=#Form.ID#">
<cfelse>
<!---create new record--->
<cfinsert datasource="Cardata" tablename="tblCars">
<cflocation url="cardetail.cfm">

my new code in full is at:

http://rafb.net/paste/results/V2263799.html

And here it is in shortened form:
<cfquery name="UpdateQuery" datasource="Cardata">
UPDATE tblCars
SET Status=#form.Status#, Year=#form.Year#, Make=#form.Make#, Model=#form.Model#,
VN=#form.VN#, Style=#form.Style#, Price=#form.Price#, Mileage=#form.Mileage#,
Cylinder=#form.Cylinder#, Displacement=#form.Displacement#, Fuel=#form.Fuel#, etc.
WHERE tblCars.ID = #Form.ID#
</cfquery>
<cflocation url="cardetail.cfm?ID=#Form.ID#">
<cfelse>
<cfquery name="UpdateQuery" datasource="Cardata">
INSERT INTO tblCars (Status, Year, Make, Model,
VN, Style, Price, Mileage, Cylinder, Displacement, Fuel, etc.)
VALUES (#form.Status#, #form.Year#, #form.Make#, #form.Model#,
#form.VN#, #form.Style#, #form.Price#, #form.Mileage#, #form.Cylinder#,
#form.Displacement#, #form.Fuel#, etc.)
</cfquery>
<cflocation url="cardetail.cfm">
</cfif>
</cfif>
</cfif>

When I run an ADD or an EDIT I am geting the following errors:
Edit error:
Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '6 CYL'.

Add error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
It is showing the error to be on the last line
106 #form.MultiCD#)
(I moved the "#form.MultiCD#)" to be on the last line.

If I limit the cfquery UPDATE to 8 fields it works fine.
(It is the 9th field that is giving the error Syntax error (missing operator) in query expression '6 CYL'.)
Also if I remove that field Cylinder=#form.Cylinder# it just gives an error on the next field.

When I limit the cfquery UPDATE to 8 fields I am getting a further error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4.
This is the SQL statement:
UPDATE tblCars SET Status=SOLD, Year=2005, Make=Ford, Model=F150, VN=2, Style=Truck, Price=6000, Mileage=67000 WHERE tblCars.ID = 53

I am completely new to cf, sql and programing : )

And one know what my problems are here?
newcow

 

[TruthInSatire]

Right off the bat any fields that are not number fields in the database (that means numbers that go into text fields too so if vin is a text field it needs single quotes even though you are entering a number) need to be surrounded by single quotes.

<cfquery name="UpdateQuery" datasource="Cardata">
UPDATE tblCars
SET Status=#form.Status#, Year=#form.Year#, Make='#form.Make#', Model='#form.Model#',
VN='#form.VN#', Style='#form.Style#', Price='#form.Price#', Mileage=#form.Mileage#,
Cylinder='#form.Cylinder#', Displacement='#form.Displacement#', Fuel='#form.Fuel#', etc.
WHERE tblCars.ID = #Form.ID#
</cfquery>

http://thereptilian.com/

 

[newcow]

bombboy: Ok that took care of the UPDATE. I tried the same thing in the INSERT and getting the following error:
Syntax error in INSERT INTO statement.
Any Ideas what I am missing on the INSERT code?

caredit.cfm

http://rafb.net/paste/results/a1015029.html

after I made the '' changes.
UPDATE is working but INSERT is not working?

newcow

 

[ecobb]

You need to show us your insert statement so we can see where the problem is. Remember, put quotes around everything EXCEPT numeric datatypes.



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[newcow]

Ecobb: Good point thanks on the SQL statement.
My db is Access and I had a currency field and it couldn't be left blank, and I found 2 other typos in my INSERT INTO statement. All up and running now. Thanks!

Now I have most of my fields set to Text. Should I be putting number fields like Year to be a Number datatype? Also are Yes/No fields 1/0 fields considered number fields.
I just put '' around every thing : )

Thanks Ecobb and bombboy on this one.

newcow

 

[TruthInSatire]

If you are certian that a field is going to be a number make it a number field. yes/no fields: I can't remember the syntax to use for that I generaly don't use them myself. I usualy use a text field and input "Yes" or "No" that way the formatting is already done when I retrieve the data. No need to mess with the <cfif query.yesNo eq 0>No<cfelse>Yes</cfif> every time i output the data in a nice pretty format for the customer.

That's just me though, we could get into a big discussion about DB data type simantics which I hope we don't. I think most is personal preferance unless you're a DBA hell bent on performance and db size to save .00004 of a second in process time.

http://thereptilian.com/

 

[newcow]

I am not displaying YES/NO anyway, I just display a checkbox. The db for this project will not go over 300 records in most cases so performance is not going to be an issue.
newcow

 

[TruthInSatire]

If you ever need to display a report for your customers you probably wouldn't be showing them check boxes. perhaps the name and yes or no next to it. like i said simantics.

http://thereptilian.com/