G_Wilkie
asked on
Registration form Misbehaving....missing operator) in query expression
Dear all
i have a registration form which wants to capture some information from our users. It is created with DMX2004 and all the settings are correct. Although every time i try to review it in ie6 it come up with the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Job Title'.
/SimpsonV5/SimpsonWebsiteR egistratio n.asp, line 95
The code i am using is the following (sorry for its length):
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/SimpsonW ebsiteRegi stration.a sp" -->
<%
// *** Edit Operations: declare variables
// set the form action variable
var MM_editAction = Request.ServerVariables("S CRIPT_NAME ");
if (Request.QueryString) {
MM_editAction += "?" + Server.HTMLEncode(Request. QueryStrin g);
}
// 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_SimpsonWebsiteRegistrat ion_STRING ;
var MM_editTable = "Customers";
var MM_editRedirectUrl = "";
var MM_fieldsStr = "Name|value|Job_Title|valu e|Company_ Name|value |Company_A ddress|val ue|City|va lue|StateO rProvince| value|Coun try|value| Post_Code| value|Phon e_Number|v alue|Exten sion|value |Email_Add ress|value |Subscribe _on_6040_M agazine|va lue|Unsubs cribe_on_6 040_Magazi ne|value";
var MM_columnsStr = "Name|',none,''|[Job Title]|',none,''|[Company Name]|',none,''|[Company Address]|',none,''|City|', none,''|St ateOrProvi nce|',none ,''|Countr y|',none,' '|[Post Code]|',none,''|[Phone Number]|',none,''|Extensio n|',none,' '|[Email Address]|',none,''|[Subscr ibe on 6040 Magazine]|none,1,0|[Unsubs cribe on 6040 Magazine]|none,1,0";
// 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_fie lds[i]));
}
// append the query string to the redirect URL
if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.index Of('?') == -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[i];
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.ActiveConnectio n = MM_editConnection;
MM_editCmd.CommandText = MM_editQuery;
MM_editCmd.Execute();
MM_editCmd.ActiveConnectio n.Close();
if (MM_editRedirectUrl) {
Response.Redirect(MM_editR edirectUrl );
}
}
}
%>
<%
var SimpsonWebsiteRegistration = Server.CreateObject("ADODB .Recordset ");
SimpsonWebsiteRegistration .ActiveCon nection = MM_SimpsonWebsiteRegistrat ion_STRING ;
SimpsonWebsiteRegistration .Source = "SELECT Name, Job Title, Company Name, Company Address, City, StateOrProvince, Country, Post Code, Phone Number, Extension, Email Address, Subscribe on 6040 Magazine, Unsubscribe on 6040 Magazine FROM Customers";
SimpsonWebsiteRegistration .CursorTyp e = 0;
SimpsonWebsiteRegistration .CursorLoc ation = 2;
SimpsonWebsiteRegistration .LockType = 1;
SimpsonWebsiteRegistration .Open();
var SimpsonWebsiteRegistration _numRows = 0;
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Registration Form</title>
</head>
<body>
<form method="post" action="<%=MM_editAction%> " name="form1">
<table align="center">
<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">Job Title:</td>
<td><input type="text" name="Job_Title" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Company Name:</td>
<td><input type="text" name="Company_Name" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Company Address:</td>
<td><input type="text" name="Company_Address" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">City:</td>
<td><input type="text" name="City" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">StateOrProvi nce:</td>
<td><input type="text" name="StateOrProvince" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Country:</td >
<td><input type="text" name="Country" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Post Code:</td>
<td><input type="text" name="Post_Code" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Phone Number:</td>
<td><input type="text" name="Phone_Number" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td align="right" nowrap bgcolor="#FFFFFF">Extensio n:</td>
<td><input type="text" name="Extension" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Email Address:</td>
<td><input type="text" name="Email_Address" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Subscribe on 6040 Magazine:</td>
<td><input type="checkbox" name="Subscribe_on_6040_Ma gazine" value=1 >
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Unsubscribe on 6040 Magazine:</td>
<td><input type="checkbox" name="Unsubscribe_on_6040_ Magazine" value=1 >
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right"> </td>
<td><input type="submit" value="Insert record">
</td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1">
</form>
<p> </p>
</body>
</html>
<%
SimpsonWebsiteRegistration .Close();
%>
I have tried to place some Brackets around the variables past in the SOURCE bit of the code but still does not work....i have tried to leave some space between them and other or use "" but nothing has worked so far. Any help would be appreciated
i have a registration form which wants to capture some information from our users. It is created with DMX2004 and all the settings are correct. Although every time i try to review it in ie6 it come up with the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Job Title'.
/SimpsonV5/SimpsonWebsiteR
The code i am using is the following (sorry for its length):
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/SimpsonW
<%
// *** Edit Operations: declare variables
// set the form action variable
var MM_editAction = Request.ServerVariables("S
if (Request.QueryString) {
MM_editAction += "?" + Server.HTMLEncode(Request.
}
// 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
var MM_editConnection = MM_SimpsonWebsiteRegistrat
var MM_editTable = "Customers";
var MM_editRedirectUrl = "";
var MM_fieldsStr = "Name|value|Job_Title|valu
var MM_columnsStr = "Name|',none,''|[Job Title]|',none,''|[Company Name]|',none,''|[Company Address]|',none,''|City|',
// 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_fie
}
// append the query string to the redirect URL
if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.index
}
}
%>
<%
// *** Insert Record: construct a sql insert statement and execute it
if (String(Request("MM_insert
// 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[i];
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
MM_editCmd.ActiveConnectio
MM_editCmd.CommandText = MM_editQuery;
MM_editCmd.Execute();
MM_editCmd.ActiveConnectio
if (MM_editRedirectUrl) {
Response.Redirect(MM_editR
}
}
}
%>
<%
var SimpsonWebsiteRegistration
SimpsonWebsiteRegistration
SimpsonWebsiteRegistration
SimpsonWebsiteRegistration
SimpsonWebsiteRegistration
SimpsonWebsiteRegistration
SimpsonWebsiteRegistration
var SimpsonWebsiteRegistration
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Registration Form</title>
</head>
<body>
<form method="post" action="<%=MM_editAction%>
<table align="center">
<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">Job Title:</td>
<td><input type="text" name="Job_Title" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Company Name:</td>
<td><input type="text" name="Company_Name" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Company Address:</td>
<td><input type="text" name="Company_Address" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">City:</td>
<td><input type="text" name="City" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">StateOrProvi
<td><input type="text" name="StateOrProvince" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Country:</td
<td><input type="text" name="Country" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Post Code:</td>
<td><input type="text" name="Post_Code" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Phone Number:</td>
<td><input type="text" name="Phone_Number" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td align="right" nowrap bgcolor="#FFFFFF">Extensio
<td><input type="text" name="Extension" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Email Address:</td>
<td><input type="text" name="Email_Address" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Subscribe on 6040 Magazine:</td>
<td><input type="checkbox" name="Subscribe_on_6040_Ma
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Unsubscribe on 6040 Magazine:</td>
<td><input type="checkbox" name="Unsubscribe_on_6040_
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right"> </td>
<td><input type="submit" value="Insert record">
</td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1">
</form>
<p> </p>
</body>
</html>
<%
SimpsonWebsiteRegistration
%>
I have tried to place some Brackets around the variables past in the SOURCE bit of the code but still does not work....i have tried to leave some space between them and other or use "" but nothing has worked so far. Any help would be appreciated
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is the field (Job Title) or (JobTitle) or (Job_Title)
Your select statement has a space in it, think this is where the problem lies
SimpsonWebsiteRegistration
more troubleshooting on http://www.adopenstatic.com/faq/80040e14.asp