Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Inserting Records strange problem

Status
Not open for further replies.
Chipsman

Chipsman

Programmer
May 26, 2005
44
GB
Hello guys,

I am having an error message that I can't get rid off.

I am designing a Website that uses ASP pages bound to a Database.

What I am trying to do is to make a form (with the help of the Dreamweaver Function: Insert > Application Objects > Record Insertion Form).

Doing this, there shouldn't be any problems... Nevertheless, when I test the page, I enter a new record to insert in the Database, press the [Insert] button, and it doesn't work!

I get that error message:
=============
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
=============

I don't know what to do.. Can you help me please?
 
Sort by date Sort by votes
Is there any way you can print the insert statement so we can SEE what the problem might be?


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard. [dragon]
 
Upvote 0 Downvote
Ok here is the code. (sorry I donno how to post it like you do guys, in some kind of small windows.)

Sorry I am posting the entire page as I have no idea what part might contain an error.

Note: That page is entirely created by Dreamweaver when I used the option of Record Insertion Form mentioned above, that's why it's strange that there is an error. I haven't changed anything in the code myself.

========================================
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Database.asp" -->
<%
// *** Edit Operations: declare variables

// set the form action variable
var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
if (Request.QueryString) {
MM_editAction += "?" + Request.QueryString;
}

// boolean to abort record edit
var MM_abortEdit = false;

// query string to execute
var MM_editQuery = "";
%>
<%
// *** Insert Record: set variables

if (String(Request("MM_insert")) == "form1") {

var MM_editConnection = MM_Database_STRING;
var MM_editTable = "[Trade Merchandising Guidelines]";
var MM_editRedirectUrl = "News.htm";
var MM_fieldsStr = "Ref|value|Name|value|Requires|value|Preview|value|DL_Path|value";
var MM_columnsStr = "Ref#|none,none,NULL|Name|',none,''|Requires|',none,''|Preview|',none,''|[DL Path]|',none,''";

// create the MM_fields and MM_columns arrays
var MM_fields = MM_fieldsStr.split("|");
var MM_columns = MM_columnsStr.split("|");

// set the form values
for (var i=0; i+1 < MM_fields.length; i+=2) {
MM_fields[i+1] = String(Request.Form(MM_fields));
}

// append the query string to the redirect URL
if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + Request.QueryString;
}
}
%>
<%
// *** Insert Record: construct a sql insert statement and execute it

if (String(Request("MM_insert")) != "undefined") {

// create the sql insert statement
var MM_tableValues = "", MM_dbValues = "";
for (var i=0; i+1 < MM_fields.length; i+=2) {
var formVal = MM_fields[i+1];
var MM_typesArray = MM_columns[i+1].split(",");
var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";
var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";
var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";
if (formVal == "" || formVal == "undefined") {
formVal = emptyVal;
} else {
if (altVal != "") {
formVal = altVal;
} else if (delim == "'") { // escape quotes
formVal = "'" + formVal.replace(/'/g,"''") + "'";
} else {
formVal = delim + formVal + delim;
}
}
MM_tableValues += ((i != 0) ? "," : "") + MM_columns;
MM_dbValues += ((i != 0) ? "," : "") + formVal;
}
MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";

if (!MM_abortEdit) {
// execute the insert
var MM_editCmd = Server.CreateObject('ADODB.Command');
MM_editCmd.ActiveConnection = MM_editConnection;
MM_editCmd.CommandText = MM_editQuery;
MM_editCmd.Execute();
MM_editCmd.ActiveConnection.Close();

if (MM_editRedirectUrl) {
Response.Redirect(MM_editRedirectUrl);
}
}

}
%>
<%
var rsTest = Server.CreateObject("ADODB.Recordset");
rsTest.ActiveConnection = MM_Database_STRING;
rsTest.Source = "SELECT * FROM [Trade Merchandising Guidelines]";
rsTest.CursorType = 0;
rsTest.CursorLocation = 2;
rsTest.LockType = 1;
rsTest.Open();
var rsTest_numRows = 0;
%>
<html xmlns="<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>

<body>
<form method="post" action="<%=MM_editAction%>" name="form1">
<table align="center">
<tr valign="baseline">
<td nowrap align="right">Ref#:</td>
<td> <input type="text" name="Ref" value="" size="32"> </td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Name:</td>
<td> <input type="text" name="Name" value="" size="32"> </td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Requires:</td>
<td> <input type="text" name="Requires" value="" size="32"> </td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Preview:</td>
<td> <input type="text" name="Preview" value="" size="32"> </td>
</tr>
<tr valign="baseline">
<td nowrap align="right">DL Path:</td>
<td> <input type="text" name="DL_Path" value="" size="32"> </td>
</tr>
<tr valign="baseline">
<td nowrap align="right">&nbsp;</td>
<td> <input type="submit" value="Insert Record"> </td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1">
</form>
<p>&nbsp;</p>
</body>
</html>
<%
rsTest.Close();
%>
=======================================

Thanks
 
Upvote 0 Downvote
Click the "Process TGML" link right above the submit button to see how to make your code appear in the little box.

Code: 
    MM_editCmd.ActiveConnection = MM_editConnection;
    MM_editCmd.CommandText = MM_editQuery;
    [red]Response.Write("The SQL: " + MM_editQuery);[/red]
    MM_editCmd.Execute();

Try adding the line in red above. It will not fix your problem but it will print the SQL statement to the screen so you can debug the syntax error.
 
Upvote 0 Downvote
Thanks Sheco but it doesn't work for some reason....

I'm desperating... I spent my day trying to figure out why it's not working.....
 
Upvote 0 Downvote
It isnt supposed to fix your problem, it is supposed to print the SQL so that you can have more details about the problem.
 
Upvote 0 Downvote
I know, but it doesn't print it I meant. Sorry I wasn't clear enought.

So it doesn't print the SQL basically.

I entered some testing values in the text fields again (1,test,test,test,....)

Here is the entire error message (maybe it is clearer?):

=================================================
Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/Dior Intranet/Test.asp, line 110


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)

Page:
POST 71 bytes to /Dior Intranet/Test.asp

POST Data:
Ref=1&Name=test&Requires=test&Preview=test&DL_Path=test&MM_insert=form1

Time:
01 July 2005, 17:19:30


More information:
Microsoft Support
===============================

 
Upvote 0 Downvote
If the error is preventing you from seeing the SQL, you can put a little Response.End in there to halt execution of the page before the error is fired:
Code: 
    MM_editCmd.ActiveConnection = MM_editConnection;
    MM_editCmd.CommandText = MM_editQuery;
[red]    Response.Write("The SQL: " + MM_editQuery);
    Response.End;[/red]
    MM_editCmd.Execute();

 
Upvote 0 Downvote
All right Sheco, it displayed something this time :)

Any idea what to do next?

The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.


--------------------------------------------------------------------------------

Invalid at the top level of the document. Error processing resource ' Line 2, ...

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
Click to expand...

Thanks for your help
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
443
leifoet
leifoet
leifoet
  • Locked
  • Question
How to log in without a popup problem?
Replies
3
Views
146
ggriffit
ggriffit
ZeeHashmi
  • Locked
  • Question
ASP : Email Body UTF-8 Problem
Replies
3
Views
332
ZeeHashmi
ZeeHashmi
leifoet
  • Locked
  • Question
Problem with updating database reords.
2
Replies
31
Views
403
leifoet
leifoet
leifoet
  • Locked
  • Question
Convert Word text with images to html (problems)
Replies
1
Views
158
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top