Insert Data into Access DB using CFQUERY

[nsukari]

I have an application that is working well except for one thing. The submitted data will not insert into the Acess table. I have checked all of the field names, I have tried other applications running on this same DB and they work, and I have checked each field in the table to ensure that there are in special requirements before inserting the data. Here is the query:

<CFQUERY NAME=&quot;addexpense&quot; DATASOURCE=&quot;WMRA&quot;>
INSERT INTO travel(req_name, req_email, organization, SPM_ID, bus_reason, num_travel, name_travel, from, to, overnight, num_nights, travel_cost, lodging_cost, meal_cost, meeting_cost, entertainment_cost, other_cost, alternate, approve, rstatus)
VALUES('#req_name#', '#req_email#', '#organization#', '#SPM_ID#', '#bus_reason#', '#num_travel#', '#name_travel#', '#from#', '#to#', '#overnight#', '#num_nights#', '#travel_cost#', '#lodging_cost#', '#meal_cost#', '#meeting_cost#', '#entertainment_cost#', '#other_cost#', '#alternate#', 'no', 'pending')
</CFQUERY>

Here is the error I keep getting

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (10:1) to (10:45).


Date/Time: 08/28/03 14:44:29
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
Remote Address: 136.151.237.33
HTTP Referrer:

http://it-net-sys.verizon.com/ragsd/approval/

Please help!!!

 

[ecobb]

I see that you have single quotes around all of your VALUES in the insert statement. If any of these values are being inserted into a &quot;Numeric&quot; data type, you can't have quotes around the value. So, if the value '#num_nights#' (for example) is a number field in the database, it should be #num_nights#

Hope this Helps!

 

[nsukari]

No. That still doesn't work.
It keeps saying there is something wrong with this line: <CFQUERY NAME=&quot;addexpense&quot; DATASOURCE=&quot;WMRA&quot;>
Everything else is fine.

 

[ecobb]

No, it's saying that something is wrong WITH your Query, that STARTS on line 10:1. The Error specifically states: Syntax error in INSERT INTO statement.

A Syntax Error usually means that you have something quoted that shouldn't be, or an extra comma, or a missing comma, or a missing parentheses, etc...in your query.

Hope this Helps!

 

[nsukari]

Thanks for trying to help. I've tried changing the fields one at a time but to no avail. Even though I didn't want to use CFINSERT, it's working with so I'll just stick with that for now.

 

[krisbrixon]

When you start to get desprate then break the SQL statement into very small chunks that should work just fine for a beginner.

<CFQUERY NAME=&quot;addexpense&quot; DATASOURCE=&quot;WMRA&quot;>
INSERT INTO travel (req_name)
VALUES ('#req_name#')
</CFQUERY>

does this very simple query work?
If so, add one variable at a time. At some poin the thing will break and you have found your problem. (Note: you do have to do all you required fields at the same time.)

Also, I am a big fan of space between things in SQL. I don't remember why, but I probably had a problem in the past. Add a space after travel and VALUES

Kris

 

[r937]

&quot;from&quot; is a reserved word

the best way to test access syntax is to run the query statement in a local copy of access in the query design sql view

rudy

http://r937.com/