INPUT and UPDATE generate 80040e14

[wormatia]

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

[bruce]

This error means that there is a syntax error in your insert statement.

Try dump out the SQL statement instead of executing it; it is usually easier to identify problem with the statement printed out.

If you still have problem, post your code.

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[wormatia]

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:p>
// *** Insert Record: construct a sql insert statement and execute it
if (String(Request("MM_insert")) != "undefined") {
// create the sql insert statement</o:p>
var MM_tableValues = "", MM_dbValues = "";</o:p>
for (var i=0; i+1 < MM_fields.length; i+=2) {</o:p>
var formVal = MM_fields[i+1];</o:p>
var MM_typesArray = MM_columns[i+1].split(",");</o:p>
var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";</o:p>
var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";</o:p>
var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";</o:p>
if (formVal == "" || formVal == "undefined") {</o:p>
formVal = emptyVal;</o:p>
} else {</o:p>
if (altVal != "") {</o:p>
formVal = altVal;</o:p>
} else if (delim == "'") { // escape quotes</o:p>
formVal = "'" + formVal.replace(/'/g,"''") + "'";</o:p>
} else {</o:p>
formVal = delim + formVal + delim;</o:p>
}</o:p>
}</o:p>
MM_tableValues += ((i != 0) ? "," : "") + MM_columns[i];</o:p>
MM_dbValues += ((i != 0) ? "," : "") + formVal;</o:p>
}</o:p>
MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";
if (!MM_abortEdit) {</o:p>
// execute the insert</o:p>
var MM_editCmd = Server.CreateObject('ADODB.Command');</o:p>
MM_editCmd.ActiveConnection = MM_editConnection;</o:p>
MM_editCmd.CommandText = MM_editQuery;</o:p>
MM_editCmd.Execute();</o:p>
MM_editCmd.ActiveConnection.Close();</o:p>
</o:p>
if (MM_editRedirectUrl) {</o:p>
Response.Redirect(MM_editRedirectUrl);</o:p>
}</o:p>
}
}</o:p>
%>

Werner

[bruce]

Add these after this line

MM_editQuery = 'insert into ' + MM_editTable + ' (' + MM_tableValues + ') values (' + MM_dbValues + ')';

--
response.write MM_editQuery
response.end

this should write out your query to the browser and stop execution

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[wormatia]

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,MajP racFocus,MiddleName,
MinPracFocus,Password,Referred,Tools1,Tools2,Tools 3,Tools4,Tools5,Tools6,
UserName,WorkPhone)
values ('none','','wkraemer@stny.rr.com','','','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

[bruce]

Your insert statement looks fine (but I can't be sure because I can't tell whether '' is a 2 single quote or a double quote)

Can you post the URL to the page that generate the error?

I want to see the exact error.

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[wormatia]

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 (http://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

[bluebeard96]

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"
Mike@KiwanisPQ.org
(760) 419-7429

[wormatia]

Thanks Mike for spending time on this problem


the reserved column name was the cause.


Werner Kraemer

[bruce]

Good catch.

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[bluebeard96]

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"
Mike@KiwanisPQ.org
(760) 419-7429