Solved

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

Posted on 2004-08-17
2
309 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now