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:</stron g> <cfoutput>#form.experiment #<input type="hidden" name="experiment" value="#experiment#"></cfo utput>
<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(cha racter)#<c fset group = '#chr(character)#'></cfout put><br>
<cfinsert datasource="animal" tablename="expergroup" formfields="expername,grou p">
</CFLOOP>
[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:</stron
<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
<cfinsert datasource="animal" tablename="expergroup" formfields="expername,grou
</CFLOOP>
you can only pass the form scope to cfinsert, so you need to create one
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(cha racter)#<c fset form.group = '#chr(character)#'></cfout put><br>
<cfinsert datasource="animal" tablename="expergroup" formfields="expername,grou p">
</CFLOOP>
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
<cfinsert datasource="animal" tablename="expergroup" formfields="expername,grou
</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>
<cfquery name="doInsert" datasource="animal">
insert into expergroup
(fieldname1,fieldname2)
values
('#form.expername#','#form
</cfquery>
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.
[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.
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?
ASKER
page is not online.
expername is text
group is text
expername is text
group is text
is debugging turned on? you should see the actual sql statement that was attempted on the insert.
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.cf m: 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
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\
46 : (expername,group)
47 : values
48 : ('#form.expername#','#form
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that was it, thanks, switched name to ngroup, it works.
><cfset group = '#chr(character)#'>
<cfset form.group = '#chr(character)#'>
kyle