Solved

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

Posted on 2004-08-17
2
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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 show result of a NULL value at random? 33 59
Help with query 3 37
ASP server side get value 15 47
html Uncheck Checkbox 2 30
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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