Re: INSERT into error....
lol .. I never thought of update as a reserved word, since I've never used date or update as a field name .. especially the way you spelled it (UpDate .. which I intuitively thought of as Upload Date, considering what you were after ..). Bloody SQL hehe. At least CF has the common sense to prefix everything with CF ;) I had the same problem one time with 'Login' as a field name. It turns out that, in Sybase, 'Login' is a reserved word. Live and learn, heh. On a related note .. does anyone have a list of *all* reserved words on all database platforms? I imagine this would be rather helpful for many of us who will be exposed to multiple database platforms throughout our careers. If you know of such a list, or a partial list, please let me know and I will add it to my list of 'best practices' for my own CF methodology and also compile the list and add it to my CF technical database, and make it available to any other CF sites that want it :) Todd Ashworth - Original Message - From: "Chad Gray" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, November 27, 2000 6:35 PM Subject: Re: INSERT into error > AH... ok.. this works now.. i placed UpDate as the new Column name and of > course that did not work either > > I looked at the list of reserved names and guess what? :) > > update is reserved also.. i changed it to OPDate and it worked fine. > > What a PITA > > Thanks everyone for the help. I printed out the list of reserved names in > 20point type and hung it on my wall. :) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: INSERT into error....
AH... ok.. this works now.. i placed UpDate as the new Column name and of course that did not work either I looked at the list of reserved names and guess what? :) update is reserved also.. i changed it to OPDate and it worked fine. What a PITA Thanks everyone for the help. I printed out the list of reserved names in 20point type and hung it on my wall. :) At 06:24 PM 11/27/00 -0500, you wrote: >Um .. what what is the datatype of your date field? Change it to timestamp >if it isn't already, or do a > >DateFormat(#Now()#, "whatever") on your date instead of just dumping Now() >in there. Sybase stores dates as -mm-dd so see what Access stores them >as and just format Now() accordingly. > >Todd Ashworth > >- Original Message - >From: "Chad Gray" <[EMAIL PROTECTED]> >To: "CF-Talk" <[EMAIL PROTECTED]> >Sent: Monday, November 27, 2000 5:18 PM >Subject: Re: INSERT into error > > > > --=_1574282==_.ALT > > Content-Type: text/plain; charset="us-ascii"; format=flowed > > > > At 05:09 PM 11/27/00 -0500, you wrote: > > >Is the "date" field name a reserved word? Try changing that to something > > >besides date. > > > > > >What is the full error message? > > > > Here is the entire error message with most of the debugging records. I > > tried changing the column name to UpDate instead of Date. > > > > Thanks for the fast replies :) > > > > > > > > > > Queries > > > > > > maxjobnum (Records=1, Time=10ms) > > SQL = > > SELECT Max(JOBNUM) as NEWJOBNUM > > FROM JOBINFO > > > > getuploaddir (Records=1, Time=10ms) > > SQL = > > Select name, upload, customerID, loginID > > from login > > where 'chad' = login.name > > > > customerlist (Records=1, Time=10ms) > > SQL = > > SELECT * > > FROM CUSTOMERDATA > > WHERE CUSTOMERID = 35 > > > > addjob (Records=0, Time=10ms) > > SQL = > > INSERT INTO JOBINFO(JOBNUM, CUSTOMERID, COMPANYNAME, > > > > ) > > VALUES ( 6726 > > ,35, 'CAPITAL ONE', > > ) > > > > 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. SQL = "INSERT INTO > > JobInfo(JobNum,LoginID,UpDate,FileName) > > VALUES( 6727, 1, {ts '2000-11-27 17:20:37'},'calendar2.PDF' )" > > > > Data Source = "CJM.mdb" The error occurred while processing an element >with > > a general identifier of (CFQUERY), occupying document position (84:1) to > > (84:60) in the template file > > D:\Web\Capitol1interface\chad\uploadaction.cfm.Date/Time: 11/27/00 >17:20:37 > > Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98) > > Remote Address: 209.115.116.102 > > HTTP Referer: http://209.115.116.151/chad/upload.cfm?name=chad > > > > > > Execution Time > > 201 milliseconds > > > > Parameters > > Form Fields: > > > > [EMAIL PROTECTED] > > CSR_REQUIRED=You need to Select your CSR email. > > CUSTOMER=chad > > CUSTOMER_REQUIRED=You need to Enter your Customer Name. > > DATE=Nov 27 5:13 PM > > [EMAIL PROTECTED] > > EMAIL_REQUIRED=You need to Enter your Customer email. > > >FIELDNAMES=NAME,FILENAME_REQUIRED,CUSTOMER_REQUIRED,EMAIL_REQUIRED,CSR_REQUI > > RED,DATE,FILENAME,CUSTOMER,EMAIL,CSR > > FILENAME=C:\WINNT\ACF2E.tmp > > FILENAME_REQUIRED=You need to Select A File To Upload. > > NAME=chad > > > > --=_1574282==_.ALT > > Content-Type: text/html; charset="us-ascii" > > > > > > At 05:09 PM 11/27/00 -0500, you wrote: > > Is the "date" field name a reserved > > word? Try changing that to something > > besides date. > > > > What is the full error message? > > Here is the entire error message with most of the debugging > > records. I tried changing the column name to UpDate instead of > > Date. > > > > Thanks for the fast replies :) > > > > > > > > > > Queries > > > > > > > > > > maxjobnum (Records=1, Time=10ms) > > SQL = > > SELECT Max(JOBNUM) as NEWJOBNUM > > FROM JOBINFO > > > > getuploaddir (Records=1, Time=10ms) > > SQL = > > Select name, upload, customerID, loginID > > from login >
Re: INSERT into error....
Your error means something like this: You have 2 tables. 1 has customer info in it with a primary key. The second has order info based on the primary key from the customer table. You delete the customer, but leave the orders in the table. You just blew your database integrety to pieces because when you pull up the orders for that customer and go to grab their name from the contact table, they aren't there. That's bad .. And so was that example .. but you get the idea. It seams you kinda have the problem with inserting instead of deleteing. Maybe if you could post all of the source for the queries on this page and how they interract with one another, I would understand what's going on better.. Todd Ashworth - Original Message - From: "Chad Gray" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, November 27, 2000 5:36 PM Subject: RE: INSERT into error > --=_2669739==_.ALT > Content-Type: text/plain; charset="us-ascii"; format=flowed > > ok.. after hacking the SQL insert statement to bits until i just had one > column being inserted i got this error: > > Im not sure what it meansThe "login" and "JobInfo" tables do have a > relationship, but not with the column JobNum. > > > > > > Error Occurred While Processing Request > Error Diagnostic InformationODBC Error Code = 23000 (Integrity constraint > violation) > [Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record > because a related record is required in table 'login'. > > SQL = "INSERT INTO JobInfo(JobNum) VALUES( 6732 )" > > Data Source = "CJM.mdb" The error occurred while processing an element with > a general identifier of (CFQUERY), occupying document position (76:1) to > (76:60) in the template file > D:\Web\Capitol1interface\chad\uploadaction.cfm.Date/Time: 11/27/00 17:37:32 > Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98) > Remote Address: 209.115.116.102 > HTTP Referer: http://209.115.116.151/chad/upload.cfm?name=chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: INSERT into error....
Um .. what what is the datatype of your date field? Change it to timestamp if it isn't already, or do a DateFormat(#Now()#, "whatever") on your date instead of just dumping Now() in there. Sybase stores dates as -mm-dd so see what Access stores them as and just format Now() accordingly. Todd Ashworth - Original Message - From: "Chad Gray" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, November 27, 2000 5:18 PM Subject: Re: INSERT into error > --=_1574282==_.ALT > Content-Type: text/plain; charset="us-ascii"; format=flowed > > At 05:09 PM 11/27/00 -0500, you wrote: > >Is the "date" field name a reserved word? Try changing that to something > >besides date. > > > >What is the full error message? > > Here is the entire error message with most of the debugging records. I > tried changing the column name to UpDate instead of Date. > > Thanks for the fast replies :) > > > > > Queries > > > maxjobnum (Records=1, Time=10ms) > SQL = > SELECT Max(JOBNUM) as NEWJOBNUM > FROM JOBINFO > > getuploaddir (Records=1, Time=10ms) > SQL = > Select name, upload, customerID, loginID > from login > where 'chad' = login.name > > customerlist (Records=1, Time=10ms) > SQL = > SELECT * > FROM CUSTOMERDATA > WHERE CUSTOMERID = 35 > > addjob (Records=0, Time=10ms) > SQL = > INSERT INTO JOBINFO(JOBNUM, CUSTOMERID, COMPANYNAME, > > ) > VALUES ( 6726 > ,35, 'CAPITAL ONE', > ) > > 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. SQL = "INSERT INTO > JobInfo(JobNum,LoginID,UpDate,FileName) > VALUES( 6727, 1, {ts '2000-11-27 17:20:37'},'calendar2.PDF' )" > > Data Source = "CJM.mdb" The error occurred while processing an element with > a general identifier of (CFQUERY), occupying document position (84:1) to > (84:60) in the template file > D:\Web\Capitol1interface\chad\uploadaction.cfm.Date/Time: 11/27/00 17:20:37 > Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98) > Remote Address: 209.115.116.102 > HTTP Referer: http://209.115.116.151/chad/upload.cfm?name=chad > > > Execution Time > 201 milliseconds > > Parameters > Form Fields: > > [EMAIL PROTECTED] > CSR_REQUIRED=You need to Select your CSR email. > CUSTOMER=chad > CUSTOMER_REQUIRED=You need to Enter your Customer Name. > DATE=Nov 27 5:13 PM > [EMAIL PROTECTED] > EMAIL_REQUIRED=You need to Enter your Customer email. > FIELDNAMES=NAME,FILENAME_REQUIRED,CUSTOMER_REQUIRED,EMAIL_REQUIRED,CSR_REQUI > RED,DATE,FILENAME,CUSTOMER,EMAIL,CSR > FILENAME=C:\WINNT\ACF2E.tmp > FILENAME_REQUIRED=You need to Select A File To Upload. > NAME=chad > > --=_1574282==_.ALT > Content-Type: text/html; charset="us-ascii" > > > At 05:09 PM 11/27/00 -0500, you wrote: > Is the "date" field name a reserved > word? Try changing that to something > besides date. > > What is the full error message? > Here is the entire error message with most of the debugging > records. I tried changing the column name to UpDate instead of > Date. > > Thanks for the fast replies :) > > > > > Queries > > > > > maxjobnum (Records=1, Time=10ms) > SQL = > SELECT Max(JOBNUM) as NEWJOBNUM > FROM JOBINFO > > getuploaddir (Records=1, Time=10ms) > SQL = > Select name, upload, customerID, loginID > from login > where 'chad' = login.name > > customerlist (Records=1, Time=10ms) > SQL = > SELECT * > FROM CUSTOMERDATA > WHERE CUSTOMERID = 35 > > addjob (Records=0, Time=10ms) > SQL = > INSERT INTO JOBINFO(JOBNUM, CUSTOMERID, COMPANYNAME, > > ) > VALUES ( 6726 > ,35, 'CAPITAL ONE', > ) > > 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. SQL = "INSERT INTO > JobInfo(JobNum,LoginID,UpDate,FileName) > VALUES( 6727, 1, {ts '2000-11-27 17:20:37'},'calendar2.PDF' )" > > > Data Source = "CJM.mdb" The error occurred while processing an > element with a general identifier of (CFQUERY), occupying document > position (84:1) to (84:60) in the template file > D:\Web\Capitol1interface\chad\uploadaction.cfm.Date/Time: 11/27/00 > 17:20:37 > Browser: Mozilla/4
Re: INSERT into error....
Now I remember, I dad the same problem...CF allowed me to READ from a field called DATE, but not INSERT to it. I changed the field to dateX and it worked fine. - Original Message - From: Todd Ashworth <[EMAIL PROTECTED]> To: CF-Talk <[EMAIL PROTECTED]> Sent: Monday, November 27, 2000 2:09 PM Subject: Re: INSERT into error > Is the "date" field name a reserved word? Try changing that to something > besides date. > > What is the full error message? > > Todd Ashworth > > - Original Message - > From: "Chad Gray" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Monday, November 27, 2000 4:48 PM > Subject: INSERT into error > > > | I think i have lost my mind... can anyone see the syntax error in my > | CFQUERY? The Table name, and field names are spelled correct. None of > the > | columns im inserting into are The primary key. (the file.clientfile is > | from a CFFILE command) The values in the error message are correct. > | > | Im about to pull my hair out. :) > | > | > | ODBC Error Code = 37000 (Syntax error or access violation) > | [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO > | statement. > | SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, > | {ts '2000-11-27 16:47:31'},'calendar.PDF' )" > | Data Source = "CJM.mdb" > | > | > | > | > | INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) > | VALUES( > | #NEXTJOBNUM#, > | #loginID#, > | #now()#, > | '#File.ClientFile#' > | ) > | > | ~~ > | Structure your ColdFusion code with Fusebox. Get the official book at > http://www.fusionauthority.com/bkinfo.cfm > | > | Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > | Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists > | > > > ~~ > Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: INSERT into error....
See if this works. INSERT INTO JobInfo(JobNum, LoginID, Date, FileName) VALUES(#maxjobnum.NEXTJOBNUM#, #getuploaddir.loginID#, #createodbcdate(now())#, '#File.ClientFile#') - Original Message - From: Greg Wolfinger <[EMAIL PROTECTED]> To: CF-Talk <[EMAIL PROTECTED]> Sent: Monday, November 27, 2000 1:38 AM Subject: Re: INSERT into error > Try #CreateODBCDateTime(Now())# instead of Now() > - Original Message - > From: Chad Gray <[EMAIL PROTECTED]> > To: CF-Talk <[EMAIL PROTECTED]> > Sent: Monday, November 27, 2000 4:48 PM > Subject: INSERT into error > > > > I think i have lost my mind... can anyone see the syntax error in my > > CFQUERY? The Table name, and field names are spelled correct. None of > the > > columns im inserting into are The primary key. (the file.clientfile is > > from a CFFILE command) The values in the error message are correct. > > > > Im about to pull my hair out. :) > > > > > > ODBC Error Code = 37000 (Syntax error or access violation) > > [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO > > statement. > > SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, > > {ts '2000-11-27 16:47:31'},'calendar.PDF' )" > > Data Source = "CJM.mdb" > > > > > > > > > > INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) > > VALUES( > > #NEXTJOBNUM#, > > #loginID#, > > #now()#, > > '#File.ClientFile#' > > ) > > > > ~~ > > Structure your ColdFusion code with Fusebox. Get the official book at > http://www.fusionauthority.com/bkinfo.cfm > > > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists > > > > ~~ > Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: INSERT into error....
--=_2669739==_.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed ok.. after hacking the SQL insert statement to bits until i just had one column being inserted i got this error: Im not sure what it meansThe "login" and "JobInfo" tables do have a relationship, but not with the column JobNum. Error Occurred While Processing Request Error Diagnostic InformationODBC Error Code = 23000 (Integrity constraint violation) [Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'login'. SQL = "INSERT INTO JobInfo(JobNum) VALUES( 6732 )" Data Source = "CJM.mdb" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (76:1) to (76:60) in the template file D:\Web\Capitol1interface\chad\uploadaction.cfm.Date/Time: 11/27/00 17:37:32 Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98) Remote Address: 209.115.116.102 HTTP Referer: http://209.115.116.151/chad/upload.cfm?name=chad --=_2669739==_.ALT Content-Type: text/html; charset="us-ascii" ok.. after hacking the SQL insert statement to bits until i just had one column being inserted i got this error: Im not sure what it means The "login" and "JobInfo" tables do have a relationship, but not with the column JobNum. Error Occurred While Processing Request Error Diagnostic InformationODBC Error Code = 23000 (Integrity constraint violation) [Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'login'. SQL = "INSERT INTO JobInfo(JobNum) VALUES( 6732 )" Data Source = "CJM.mdb" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (76:1) to (76:60) in the template file D:\Web\Capitol1interface\chad\uploadaction.cfm.Date/Time: 11/27/00 17:37:32 Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98) Remote Address: 209.115.116.102 HTTP Referer: http://209.115.116.151/chad/upload.cfm?name=chad" eudora="autourl">http://209.115.116.151/chad/upload.cfm?name=chad --=_2669739==_.ALT-- ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: INSERT into error....
Here is a list of all of the reserved words for Microsoft Access. http://support.microsoft.com/support/kb/articles/Q109/3/12.asp chris.alvarado cold.fusion - developer [phone] 512.794.6563 [email] [EMAIL PROTECTED] [web] http://www.tmanage.com Privileged/Confidential Information may be contained in this message. It is not for use or disclosure outside TManage without a written proprietary agreement. If you are not the addressee indicated in this message, or agent responsible for delivery, you may not copy or deliver this message to anyone. Please notify the sender as soon as possible and immediately destroy this message and its attachments entirely. -Original Message- From: Dylan Bromby [mailto:[EMAIL PROTECTED]] Sent: Monday, November 27, 2000 4:17 PM To: CF-Talk Subject: RE: INSERT into error In Access, I don't think you can use Date as a field name. I used to have lots of trouble with that when I used to use Access. I always made it the_date or something. -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Monday, November 27, 2000 1:49 PM To: CF-Talk Subject: INSERT into error I think i have lost my mind... can anyone see the syntax error in my CFQUERY? The Table name, and field names are spelled correct. None of the columns im inserting into are The primary key. (the file.clientfile is from a CFFILE command) The values in the error message are correct. Im about to pull my hair out. :) ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, {ts '2000-11-27 16:47:31'},'calendar.PDF' )" Data Source = "CJM.mdb" INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( #NEXTJOBNUM#, #loginID#, #now()#, '#File.ClientFile#' ) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: INSERT into error....
> Try #CreateODBCDateTime(Now())# instead of Now() Just for future reference, Now returns an ODBC-compliant timestamp, just like CreateODBCDateTime does. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: INSERT into error....
--=_1574282==_.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed At 05:09 PM 11/27/00 -0500, you wrote: >Is the "date" field name a reserved word? Try changing that to something >besides date. > >What is the full error message? Here is the entire error message with most of the debugging records. I tried changing the column name to UpDate instead of Date. Thanks for the fast replies :) Queries maxjobnum (Records=1, Time=10ms) SQL = SELECT Max(JOBNUM) as NEWJOBNUM FROM JOBINFO getuploaddir (Records=1, Time=10ms) SQL = Select name, upload, customerID, loginID from login where 'chad' = login.name customerlist (Records=1, Time=10ms) SQL = SELECT * FROM CUSTOMERDATA WHERE CUSTOMERID = 35 addjob (Records=0, Time=10ms) SQL = INSERT INTO JOBINFO(JOBNUM, CUSTOMERID, COMPANYNAME, ) VALUES ( 6726 ,35, 'CAPITAL ONE', ) 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. SQL = "INSERT INTO JobInfo(JobNum,LoginID,UpDate,FileName) VALUES( 6727, 1, {ts '2000-11-27 17:20:37'},'calendar2.PDF' )" Data Source = "CJM.mdb" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (84:1) to (84:60) in the template file D:\Web\Capitol1interface\chad\uploadaction.cfm.Date/Time: 11/27/00 17:20:37 Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98) Remote Address: 209.115.116.102 HTTP Referer: http://209.115.116.151/chad/upload.cfm?name=chad Execution Time 201 milliseconds Parameters Form Fields: [EMAIL PROTECTED] CSR_REQUIRED=You need to Select your CSR email. CUSTOMER=chad CUSTOMER_REQUIRED=You need to Enter your Customer Name. DATE=Nov 27 5:13 PM [EMAIL PROTECTED] EMAIL_REQUIRED=You need to Enter your Customer email. FIELDNAMES=NAME,FILENAME_REQUIRED,CUSTOMER_REQUIRED,EMAIL_REQUIRED,CSR_REQUI RED,DATE,FILENAME,CUSTOMER,EMAIL,CSR FILENAME=C:\WINNT\ACF2E.tmp FILENAME_REQUIRED=You need to Select A File To Upload. NAME=chad --=_1574282==_.ALT Content-Type: text/html; charset="us-ascii" At 05:09 PM 11/27/00 -0500, you wrote: Is the "date" field name a reserved word? Try changing that to something besides date. What is the full error message? Here is the entire error message with most of the debugging records. I tried changing the column name to UpDate instead of Date. Thanks for the fast replies :) Queries maxjobnum (Records=1, Time=10ms) SQL = SELECT Max(JOBNUM) as NEWJOBNUM FROM JOBINFO getuploaddir (Records=1, Time=10ms) SQL = Select name, upload, customerID, loginID from login where 'chad' = login.name customerlist (Records=1, Time=10ms) SQL = SELECT * FROM CUSTOMERDATA WHERE CUSTOMERID = 35 addjob (Records=0, Time=10ms) SQL = INSERT INTO JOBINFO(JOBNUM, CUSTOMERID, COMPANYNAME, ) VALUES ( 6726 ,35, 'CAPITAL ONE', ) 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. SQL = "INSERT INTO JobInfo(JobNum,LoginID,UpDate,FileName) VALUES( 6727, 1, {ts '2000-11-27 17:20:37'},'calendar2.PDF' )" Data Source = "CJM.mdb" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (84:1) to (84:60) in the template file D:\Web\Capitol1interface\chad\uploadaction.cfm.Date/Time: 11/27/00 17:20:37 Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98) Remote Address: 209.115.116.102 HTTP Referer: http://209.115.116.151/chad/upload.cfm?name=chad" eudora="autourl">http://209.115.116.151/chad/upload.cfm?name=chad Execution Time 201 milliseconds Parameters Form Fields: [EMAIL PROTECTED] CSR_REQUIRED=You need to Select your CSR email. CUSTOMER=chad CUSTOMER_REQUIRED=You need to Enter your Customer Name. DATE=Nov 27 5:13 PM [EMAIL PROTECTED] EMAIL_REQUIRED=You need to Enter your Customer email. FIELDNAMES=NAME,FILENAME_REQUIRED,CUSTOMER_REQUIRED,EMAIL_REQUIRED,CSR_REQUIRED,DATE,FILENAME,CUSTOMER,EMAIL,CSR FILENAME=C:\WINNT\ACF2E.tmp FILENAME_REQUIRED=You need to Select A File To Upload. NAME=chad --=_1574282==_.ALT-- ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: INSERT into error....
In Access, I don't think you can use Date as a field name. I used to have lots of trouble with that when I used to use Access. I always made it the_date or something. -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Monday, November 27, 2000 1:49 PM To: CF-Talk Subject: INSERT into error I think i have lost my mind... can anyone see the syntax error in my CFQUERY? The Table name, and field names are spelled correct. None of the columns im inserting into are The primary key. (the file.clientfile is from a CFFILE command) The values in the error message are correct. Im about to pull my hair out. :) ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, {ts '2000-11-27 16:47:31'},'calendar.PDF' )" Data Source = "CJM.mdb" INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( #NEXTJOBNUM#, #loginID#, #now()#, '#File.ClientFile#' ) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: INSERT into error....
Is "Date" a reserved word? Try changing that column name. Bob -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: November 27, 2000 4:49 PM To: CF-Talk Subject: INSERT into error I think i have lost my mind... can anyone see the syntax error in my CFQUERY? The Table name, and field names are spelled correct. None of the columns im inserting into are The primary key. (the file.clientfile is from a CFFILE command) The values in the error message are correct. Im about to pull my hair out. :) ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, {ts '2000-11-27 16:47:31'},'calendar.PDF' )" Data Source = "CJM.mdb" INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( #NEXTJOBNUM#, #loginID#, #now()#, '#File.ClientFile#' ) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: INSERT into error....
Nope... The #CreateODBCDateTime(Now())# did not help. im starting to wonder about the possibility of a access violation I am doing two SQL inserts on two different databases in the same CF file The first SQL insert works fine... it hits the second insert and i error out. The databases are both MS Access right now. At 04:38 AM 11/27/00 -0500, you wrote: >Try #CreateODBCDateTime(Now())# instead of Now() >- Original Message - >From: Chad Gray <[EMAIL PROTECTED]> >To: CF-Talk <[EMAIL PROTECTED]> >Sent: Monday, November 27, 2000 4:48 PM >Subject: INSERT into error > > > > I think i have lost my mind... can anyone see the syntax error in my > > CFQUERY? The Table name, and field names are spelled correct. None of >the > > columns im inserting into are The primary key. (the file.clientfile is > > from a CFFILE command) The values in the error message are correct. > > > > Im about to pull my hair out. :) > > > > > > ODBC Error Code = 37000 (Syntax error or access violation) > > [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO > > statement. > > SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, > > {ts '2000-11-27 16:47:31'},'calendar.PDF' )" > > Data Source = "CJM.mdb" > > > > > > > > > > INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) > > VALUES( > > #NEXTJOBNUM#, > > #loginID#, > > #now()#, > > '#File.ClientFile#' > > ) > > > > ~~ > > Structure your ColdFusion code with Fusebox. Get the official book at >http://www.fusionauthority.com/bkinfo.cfm > > > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists > > > >~~ >Structure your ColdFusion code with Fusebox. Get the official book at >http://www.fusionauthority.com/bkinfo.cfm > >Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ >Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: INSERT into error....
Is the "date" field name a reserved word? Try changing that to something besides date. What is the full error message? Todd Ashworth - Original Message - From: "Chad Gray" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, November 27, 2000 4:48 PM Subject: INSERT into error | I think i have lost my mind... can anyone see the syntax error in my | CFQUERY? The Table name, and field names are spelled correct. None of the | columns im inserting into are The primary key. (the file.clientfile is | from a CFFILE command) The values in the error message are correct. | | Im about to pull my hair out. :) | | | ODBC Error Code = 37000 (Syntax error or access violation) | [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO | statement. | SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, | {ts '2000-11-27 16:47:31'},'calendar.PDF' )" | Data Source = "CJM.mdb" | | | | | INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) | VALUES( | #NEXTJOBNUM#, | #loginID#, | #now()#, | '#File.ClientFile#' | ) | | ~~ | Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm | | Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ | Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists | ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: INSERT into error....
Try #CreateODBCDateTime(Now())# instead of Now() - Original Message - From: Chad Gray <[EMAIL PROTECTED]> To: CF-Talk <[EMAIL PROTECTED]> Sent: Monday, November 27, 2000 4:48 PM Subject: INSERT into error > I think i have lost my mind... can anyone see the syntax error in my > CFQUERY? The Table name, and field names are spelled correct. None of the > columns im inserting into are The primary key. (the file.clientfile is > from a CFFILE command) The values in the error message are correct. > > Im about to pull my hair out. :) > > > ODBC Error Code = 37000 (Syntax error or access violation) > [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO > statement. > SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, > {ts '2000-11-27 16:47:31'},'calendar.PDF' )" > Data Source = "CJM.mdb" > > > > > INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) > VALUES( > #NEXTJOBNUM#, > #loginID#, > #now()#, > '#File.ClientFile#' > ) > > ~~ > Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
INSERT into error....
I think i have lost my mind... can anyone see the syntax error in my CFQUERY? The Table name, and field names are spelled correct. None of the columns im inserting into are The primary key. (the file.clientfile is from a CFFILE command) The values in the error message are correct. Im about to pull my hair out. :) ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. SQL = "INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( 6723, 1, {ts '2000-11-27 16:47:31'},'calendar.PDF' )" Data Source = "CJM.mdb" INSERT INTO JobInfo(JobNum,LoginID,Date,FileName) VALUES( #NEXTJOBNUM#, #loginID#, #now()#, '#File.ClientFile#' ) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists