?
Solved

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

Posted on 2004-08-17
2
Medium Priority
?
328 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

862 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