INPUT and UPDATE generate 80040e14

I am using the standard Dreamweaver MX INPUT and UPDATE scripts (since I am not a javascriptexpert). This script executes correctly on my local testing server. The following error is generated at the Discountasp.net server:

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/RegisterUser.asp, line 120

where line 120 (in the source file) is
MM_editCmd.Execute();

Ialso knowthat there is this "all I did" syndrome - can anyone offer some suggestions. Read data accesses work fine. It's just when I try to write to the database when this error occurs.

Werner

 

Thanks Bruce,
this will definitely help identifying the problem ? please excuse my inexperience, but, how do I dump out the SQL string from the string generator code:
<%</o>
// *** Insert Record: construct a sql insert statement and execute it
if (String(Request("MM_insert")) != "undefined") {
// create the sql insert statement</o>
var MM_tableValues = "", MM_dbValues = "";</o>
for (var i=0; i+1 < MM_fields.length; i+=2) {</o>
var formVal = MM_fields[i+1];</o>
var MM_typesArray = MM_columns[i+1].split(",");</o>
var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";</o>
var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";</o>
var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";</o>
if (formVal == "" || formVal == "undefined") {</o>
formVal = emptyVal;</o>
} else {</o>
if (altVal != "") {</o>
formVal = altVal;</o>
} else if (delim == "'") { // escape quotes</o>
formVal = "'" + formVal.replace(/'/g,"''") + "'";</o>
} else {</o>
formVal = delim + formVal + delim;</o>
}</o>
}</o>
MM_tableValues += ((i != 0) ? "," : "") + MM_columns;</o>
MM_dbValues += ((i != 0) ? "," : "") + formVal;</o>
}</o>
MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";
if (!MM_abortEdit) {</o>
// execute the insert</o>
var MM_editCmd = Server.CreateObject('ADODB.Command');</o>
MM_editCmd.ActiveConnection = MM_editConnection;</o>
MM_editCmd.CommandText = MM_editQuery;</o>
MM_editCmd.Execute();</o>
MM_editCmd.ActiveConnection.Close();</o>
</o>
if (MM_editRedirectUrl) {</o>
Response.Redirect(MM_editRedirectUrl);</o>
}</o>
}
}</o>
%>

Werner

 

Thanks Bruce,


I have dumped the SQL statement with the Response.write line and got the following results (without the line breaks):


insert into Members
(CellPhone,Designation,Email,Fax,Fellows,FirstName,Gender,HomeAdd1,
HomeCity,HomePhone,HomeState,HomeZIP,LastName,MajPracFocus,MiddleName,
MinPracFocus,Password,Referred,Tools1,Tools2,Tools3,Tools4,Tools5,Tools6,
UserName,WorkPhone)
values ('none','','[email protected]','','','werner','','','','','','','kraemer','','','',
'two','','','','','','','','one','')


Both the field identifiers and value number 26 (to my count).


After commenting out the Response.write command I still get the
'syntax error in the INSERT INTO statement' report

Werner

 

http://wormatiaco1.web113.discountasp.net/TestingIO/Add.asp
is the current test page page link. After the DNS has been transferred to Discountasp.net it will be www.wormatia.com/TestIO/add.asp

I tested the SQL dump for the single or double quotes by inserting spaces intoempty fields - they are indeed single quotes as the script intends.

Thanks
Werner

 

I believe password is a reserved word in SQL. I copy pasted your sql command into my mySQL control panel and password is highlighted in blue. After a google search, it looks like that may be the flaw.


Try changing variable names and let us know if that works.





Mike Reilly, Secretary/Webmaster
Kiwanis Club of Rancho Penasquitos
"Serving the Children of the World"
[email protected]
(760) 419-7429

 

Thanks Mike for spending time on this problem


the reserved column name was the cause.


Werner Kraemer

 

Glad I could help. Had me stumped to. Thanks to mySQL for the color-coding



Mike Reilly, Secretary/Webmaster
Kiwanis Club of Rancho Penasquitos
"Serving the Children of the World"
[email protected]
(760) 419-7429