Solved

Registration form Misbehaving....missing operator) in query expression

Posted on 2004-08-17
2
317 Views
Last Modified: 2011-04-14
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
0
Comment
Question by:G_Wilkie
2 Comments
 
LVL 1

Expert Comment

by:fgict
ID: 11820640
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
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 11820691
try changing your sql

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";

to

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";


enclose all the field names with embedded spaces inside brackets[]  thyat will solve your problem.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to hide hopscotch tour popup page reload? 2 94
Auto Submit on dropdown box 3 79
Hide Table in merge 3 31
XML and version of MS XML Dom Document versions 4 9
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question