still having a problem with insert
Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: still having a problem with insert
Subject: still having a problem with insert Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) You'll need to delimit non-numeric values with single quotes: INSERT INTO ... VALUES('#Form.subject#') Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: still having a problem with insert
You need to put single or double quotes (I can't remember which in access) around string data in the query, so #form.note# should become '#form.note#', or for a better solution, use cfqueryparam cf_sql_type=cf_sql_varchar value=#form.note# for this and all other fields. -- marlon And Bobby you are right, I am being selfish, but the last time I checked, we don't have a whole lot of songs that feature the cowbell! -Original Message- From: Steven Sharko [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 8:38 AM To: CF-Talk Subject: still having a problem with insert Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: still having a problem with insert
Steven, you may need to surrround some of your insert values with single quotes as it seems judging from variable names that they are strings. - Original Message - From: Steven Sharko To: CF-Talk Sent: Wednesday, February 25, 2004 9:37 AM Subject: still having a problem with insert Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: still having a problem with insert
like ppl keep saying the word type is a reserved word and WONT work u will have 2 rename that field in your database, no ands, ifs or buts about it. read this, straight from the mothership http://www.macromedia.com/support/ultradev/ts/documents/insert_into.htm Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({tsapos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: still having a problem with insert
Steven, You need single quotes around all char data... as in '#form.subject#'. You will need to correctly format the date as well. Try using #createodbcdate(form.thisdate)#for that item.Also - as has been suggested a couple of times - using CFQUERYPARAM would be a good idea - faster, and safer - like the promise of mass rail transit. -Mark -Original Message- From: Steven Sharko [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 8:38 AM To: CF-Talk Subject: still having a problem with insert Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: still having a problem with insert
I tried that and still got an error. Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\netpacq\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values ('#form.thisdate#','#form.subject#','#form.note#','#form.type#',#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values (apos;{ts apos;2004-02-25 09:49:00apos;}apos;,apos;Service Ticket 3173 - apos;,apos;brSubmitted by apos;,apos;tapos;,) At 09:49 AM 2/25/2004, you wrote: Subject: still having a problem with insert Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) You'll need to delimit non-numeric values with single quotes: INSERT INTO ... VALUES('#Form.subject#') Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: still having a problem with insert
Steven, I would add one more thing. Rather than beat your head agains the wall in CF, get a quick book - the teach yourself SQL in24 hours would do nicely - and use the query analyzer or some other raw tool where you can pass in queries and view results. Take a half a day and learn SELECT , INSERT, UPDATE and possible JOIN - and a few other tricks. Simple SQL is not hard to grasp.It reads pretty easily... you can get what is happening in most queries pretty quickly. -Mark -Original Message- From: Steven Sharko [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 8:38 AM To: CF-Talk Subject: still having a problem with insert Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: still having a problem with insert
Yup, that's a mess alright. Looks like your form might be screwy as well, or had some html in a field, or something. Check your form, fix your text field values in the sql (wrap them with single quotes). And once you've done that, don't use that SQL :P Instead, use the cfqueryparam version that I added to your last thread, but adjust the sql types from my assumptions to the real datatypes of your DB's columns. Jamie On Wed, 25 Feb 2004 09:37:57 -0500, in cf-talk you wrote: Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: still having a problem with insert
Steven, yes... if you use createodbcdatetime() or createodbcdate() to massage the variable thisdate into something that looks like {ts '2004-02-25 09:49:00'}Then you must REMOVE the single quotes around that one... also, is the column name really thisdate?Remember the name of the column and the name of the variable don't have to match - that's a nuance of cfinsert (and a limitation).The name of the column is dictated by the table. -Mark -Original Message- From: Steven Sharko [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 8:50 AM To: CF-Talk Subject: RE: still having a problem with insert I tried that and still got an error. Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\netpacq\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values ('#form.thisdate#','#form.subject#','#form.note#','#form.type#',#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values (apos;{ts apos;2004-02-25 09:49:00apos;}apos;,apos;Service Ticket 3173 - apos;,apos;brSubmitted by apos;,apos;tapos;,) At 09:49 AM 2/25/2004, you wrote: Subject: still having a problem with insert Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. The error occurred in D:\websites\serviceorders.cfm: line 127 125 : insert into clntnote (thisdate,subject,note,type,compid) 126 : values (#form.thisdate#,#form.subject#,#form.note#,#form.type#,#form.compid#) 127 : /cfquery 128 :/CFIF 129 :CFIF ParameterExists(sta) eq 'true' SQL insert into clntnote (thisdate,subject,note,type,compid) values ({ts apos;2004-02-25 09:36:45apos;},Service Ticket 3172 - ,brSubmitted by ,t,) You'll need to delimit non-numeric values with single quotes: INSERT INTO ... VALUES('#Form.subject#') Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: still having a problem with insert
A trick I used in the my youth to help me learn: Since you are using Access, use the query builder in Access to build the query, then change the view to SQL view and copy the SQL code from there and paste into your Cold Fusion cfquery... tag.A few simple modifications to incorporate the form variables and you should be good to go. HTH, helped me a lot in the early days. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: still having a problem with insert
From: [EMAIL PROTECTED] like ppl keep saying the word type is a reserved word and WONT work u will have 2 rename that field in your database, no ands, ifs or buts about it. read this, straight from the mothership http://www.macromedia.com/support/ultradev/ts/documents/insert _into.htm Not if you put square brackets around the field name: insert into clntnote (thisdate,subject,note,[type],compid) Does nobody read my emails? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]