Link to home
Start Free TrialLog in
Avatar of RussoMA
RussoMA

asked on

Syntax error in INSERT INTO statement.

Error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

This is the second page of a multiple stage form, the first page passes the experiment and nogroups arguments, which nogroups is an integer.

This is the coded loop section in which i am generating database entries from A to whatever character is the last group needed based on the integer passed from the first form.  This is to create groups in the database based off each experiment and have a different database entry with no primary key for each time the loop executes.

I have a feeling that there is a better way to insert a new line of data into the database other than the way I am doing in this code, which is why i am getting the syntax error that I am getting in trying to set variables to the formfields.

<<CODE>>

<strong>Experiment:</strong> <cfoutput>#form.experiment#<input type="hidden" name="experiment" value="#experiment#"></cfoutput>
<br><br>
Created groups named:<br><input type="text" name="nogroups"><br><br>
<cfset numgrp = (65+#form.nogroups#)>
<cfset expername = '#form.experiment#'>
<CFLOOP Index="character"
FROM="65"
TO="#numgrp#"
STEP="1">
<cfoutput>#form.experiment#_#chr(character)#<cfset group = '#chr(character)#'></cfoutput><br>
<cfinsert datasource="animal" tablename="expergroup" formfields="expername,group">
</CFLOOP>
Avatar of kyle1830
kyle1830

change this to a "form" field.
><cfset group = '#chr(character)#'>
<cfset form.group = '#chr(character)#'>

kyle
you can only pass the form scope to cfinsert, so you need to create one
Avatar of RussoMA

ASKER

Updated Code, still same problem:

Created groups named:<br><br>
<cfset numgrp = (65+#form.nogroups#)>
<cfset form.expername = '#form.experiment#'>
<CFLOOP Index="character"
FROM="65"
TO="#numgrp#"
STEP="1">
<cfoutput>#form.experiment#_#chr(character)#<cfset form.group = '#chr(character)#'></cfoutput><br>
<cfinsert datasource="animal" tablename="expergroup" formfields="expername,group">
</CFLOOP>
post the entire error including the failed sql, but as a work around for now:
<cfquery name="doInsert" datasource="animal">
    insert into expergroup
    (fieldname1,fieldname2)
    values
    ('#form.expername#','#form.group#')
</cfquery>
Avatar of RussoMA

ASKER

that is the entire error,

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

the heading is: Error Executing Database Query.

same error with updated cfquery.
Avatar of RussoMA

ASKER

it is Access database, not SQL.
what are the data types of the two fields?  is the page on line, that we can look at?
Avatar of RussoMA

ASKER

page is not online.

expername is text
group is text
is debugging turned on?  you should see the actual sql statement that was attempted on the insert.
Avatar of RussoMA

ASKER

Experiment: BMT_001_EER

Created groups named:

BMT_001_EER_A


Error Occurred While Processing Request  
Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.  
 
The error occurred in C:\Inetpub\wwwroot\animal\defgrp2.cfm: line 48
 
46 :     (expername,group)
47 :     values
48 :     ('#form.expername#','#form.group#')
49 : </cfquery>
50 : </CFLOOP><br>
 

--------------------------------------------------------------------------------
 
SQL    insert into expergroup (expername,group) values ('BMT_001_EER','A')  
DATASOURCE   animal
VENDORERRORCODE   -3502
SQLSTATE   42000
 
 
ASKER CERTIFIED SOLUTION
Avatar of kyle1830
kyle1830

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RussoMA

ASKER

that was it, thanks, switched name to ngroup, it works.