I keep receiving this error:
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 (85:1) to (85:112).
I am using Ultra Dev to insert into the database. I have 2 other pages using the same
exact coding in the same directory and they work perfectly with no problems. Can
anyone offer any ideas? Thanks:
UD Code:
<cfinclude template="../Connections/gcstartrek.cfm">
<cfscript>
// *** Edit Operations: declare variables
// set the form action variable
MM_editAction = CGI.SCRIPT_NAME;
If (CGI.QUERY_STRING NEQ "") {
MM_editAction = MM_editAction & "?" & CGI.QUERY_STRING;
}
// boolean to abort record edit
MM_abortEdit = false;
// query string to execute
MM_editQuery = "";
</cfscript>
<cfscript>
// *** Insert Record: set variables
If (IsDefined("MM_insert")) {
MM_editDatasource = #MM_gcstartrek_DSN#;
MM_editUserName = #MM_gcstartrek_USERNAME#;
MM_editPassword = #MM_gcstartrek_PASSWORD#;
MM_editTable = "news";
MM_editRedirectUrl = "main.cfm";
MM_fieldsStr = "head|value|atuher|value|day|value|story|value";
MM_columnsStr =
"headline|',none,''|author|',none,''|date|',none,''|details|',none,''";
// create the MM_fields and MM_columns arrays
MM_fields = ListToArray(MM_fieldsStr,"|");
MM_columns = ListToArray(MM_columnsStr,"|");
// set the form values
For (i=1; i+1 LE ArrayLen(MM_fields); i=i+2) {
MM_fields[i+1] = IIf(IsDefined(MM_fields[i]),"FORM." & MM_fields[i],DE(""));
}
// append the query string to the redirect URL
If (MM_editRedirectUrl NEQ "" AND CGI.QUERY_STRING NEQ "") {
If (Find("?", MM_editRedirectUrl) EQ 0) {
MM_editRedirectUrl = MM_editRedirectUrl & "?" & CGI.QUERY_STRING;
} Else {
MM_editRedirectUrl = MM_editRedirectUrl & "&" & CGI.QUERY_STRING;
}
}
}
</cfscript>
<cfscript>
// *** Insert Record: construct a sql insert statement
If (IsDefined("MM_insert")) {
// create the insert sql statement
MM_tableValues="";
MM_dbValues="";
For (i=1; i+1 LE ArrayLen(MM_fields); i=i+2) {
FormVal = MM_fields[i+1];
MM_typesArray = ListToArray(MM_columns[i+1],",");
Delim = IIf(MM_typesArray[1] NEQ "none","MM_typesArray[1]",DE(""));
AltVal = IIf(MM_typesArray[2] NEQ "none","MM_typesArray[2]",DE(""));
EmptyVal = IIf(MM_typesArray[3] NEQ "none","MM_typesArray[3]",DE(""));
If (FormVal EQ "") {
FormVal = EmptyVal;
} Else {
If (AltVal NEQ "") {
FormVal = AltVal;
} Else If (Delim EQ "'") { // escape quotes
FormVal = "'" & Replace(FormVal,"'","''","ALL") & "'";
} Else {
FormVal = Delim & FormVal & Delim;
}
}
If (i GT 1) {
MM_tableValues = MM_tableValues & ",";
MM_dbValues = MM_dbValues & ",";
}
MM_tableValues = MM_tableValues & MM_columns[i];
MM_dbValues = MM_dbValues & FormVal;
}
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values ("
& MM_dbValues & ")";
}
</cfscript>
<cfif Not #MM_abortEdit# AND IsDefined("MM_insert")>
line 85) <cfquery name="MM_editCmd" datasource=#MM_editDatasource#
username=#MM_editUserName# password=#MM_editPassword#>
#PreserveSingleQuotes(MM_editQuery)#
</cfquery>
<cfif #MM_editRedirectUrl# NEQ "">
<cflocation url="#MM_editRedirectUrl#" addtoken="no">
</cfif>
</cfif>
Jeff Davis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
