Link to home
Start Free TrialLog in
Avatar of G_Wilkie
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/SimpsonWebsiteRegistration.asp, line 95

The code i am using is the following (sorry for its length):

<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/SimpsonWebsiteRegistration.asp" -->
<%
// *** Edit Operations: declare variables

// set the form action variable
var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
if (Request.QueryString) {
  MM_editAction += "?" + Server.HTMLEncode(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_SimpsonWebsiteRegistration_STRING;
  var MM_editTable  = "Customers";
  var MM_editRedirectUrl = "";
  var MM_fieldsStr = "Name|value|Job_Title|value|Company_Name|value|Company_Address|value|City|value|StateOrProvince|value|Country|value|Post_Code|value|Phone_Number|value|Extension|value|Email_Address|value|Subscribe_on_6040_Magazine|value|Unsubscribe_on_6040_Magazine|value";
  var MM_columnsStr = "Name|',none,''|[Job Title]|',none,''|[Company Name]|',none,''|[Company Address]|',none,''|City|',none,''|StateOrProvince|',none,''|Country|',none,''|[Post Code]|',none,''|[Phone Number]|',none,''|Extension|',none,''|[Email Address]|',none,''|[Subscribe on 6040 Magazine]|none,1,0|[Unsubscribe 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_fields[i]));
  }

  // 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[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.ActiveConnection = MM_editConnection;
    MM_editCmd.CommandText = MM_editQuery;
    MM_editCmd.Execute();
    MM_editCmd.ActiveConnection.Close();

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

}
%>
<%
var SimpsonWebsiteRegistration = Server.CreateObject("ADODB.Recordset");
SimpsonWebsiteRegistration.ActiveConnection = MM_SimpsonWebsiteRegistration_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.CursorType = 0;
SimpsonWebsiteRegistration.CursorLocation = 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">StateOrProvince:</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">Extension:</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_Magazine" 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">&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>
<%
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
Avatar of fgict
fgict

Check that your fields are correct
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.Source = "SELECT Name, Job Title, Company Name,

more troubleshooting on http://www.adopenstatic.com/faq/80040e14.asp
ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial