Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 1st, 2007, 10:35 AM
ppenn
Guest
 
Posts: n/a
Default Syntax error (missing operator)

Hi I am fairly new to asp - I have create two asp pages one that shows data for amendment and the other to update the database - I am getting all sorts of errors but at the moment it is "Microsoft JET Database Engine (0x80040E14) Syntax error (missing operator) in query expression 'OPERATOR_Unid ='." I have searched the internet for two days now and I am pulling out what little hair I have - HELP please.

The code for my first page is:

<%@ Language=VBScript %>
<%Option explicit
Dim oRs, conn, connect, strSQL
Dim varOpID
%>

<HTML>
<HEAD>

<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>Update Customer Information</TITLE>
</HEAD>
<BODY>

<TABLE width=848 border=0>

    <TR>
        <TD width="31%"><a href="ContractStart.asp"><IMG height=90 src="Default.gif" width=136 border=0 ></a>
        </TD>
        <TD width="32%">
            <P align=center><B>Contract Database</B></P>
            <P align=center><B>Update Customer Information</B></P>
        </TD>
        <TD width="1%">
        </TD>
        <TD width="36%">
            <P align=right><IMG height=90 src="MCTF.gif" width=136 border=0></P>
        </TD>
    </TR>
</TABLE>

<table border="0" width="848">
    <tr>
        <td width="10%"><input type="button" value="Go Back" onClick="history.go(-1)"id=button1 name=button1 style="background-color: #C5E6F7; color: #00000"></td>
    </tr>
</table>

<%

'Create an instance of the connection Object
set conn=server.CreateObject ("adodb.connection")

'Open a connection to the Contract File
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("ContractDataWeb.mdb") & ";Persist Security Info=False"
conn.Open connect
%>

<%

'Create an Instance of the Recordset Object - from above method="Post" action="ContractStart.asp">ROWS ="2">
    Set oRs=Server.CreateObject("adodb.recordset")

varOpID=Request.QueryString ("objOpID")

'The Open method of the Recordset Object is used to retrieve '"& varOpID &"'
'data from the Contract Database
strSQL = "SELECT DISTINCT tblOperatorFile.OPERATOR_Unid,tblOperatorFile.OPER ATOR, tblOperatorFile.Contact_1," _
        & " tblOperatorFile.Contact_2, tblOperatorFile.TELEPHONE, tblOperatorFile.Telephone_2," _
        & " tblOperatorFile.Telephone_OutofHours, tblOperatorFile.Fax_No, tblOperatorFile.Email," _
        & " tblOperatorFile.Paperwork_Proc, tblOperatorFile.Order_Number_Proc, tblOperatorFile.General_Notes_Requests" _
        & " FROM tblOperatorFile INNER JOIN tblVehicleContract ON tblOperatorFile.Operator_unid = tblVehicleContract.Operator_ID" _
          & " WHERE tblOperatorFile.Operator_Unid = "& varOpID &""

oRs.Open strSQL, conn

%>
<FORM name="form" method="post" action="ContractUpdate.asp">
<table>
    <tr>
        <td> Contact No.1: </td>
        <td><input type="text" name="contact1" value="<%= oRs("Contact_1") %>" SIZE=100></td>
    </tr>

    <tr>
        <td> Telephone No.1: </td>
        <td><input type="text" name="TELEPHONE 1" value="<%= oRs("TELEPHONE") %>" SIZE=100></td>
    </tr>

    <tr>
        <td> Out of Hours: </td>
        <td><input type="text" name="Telephone_OutofHours" value="<%= oRs("Telephone_OutofHours") %>" SIZE=100></td>
    </tr>

        <tr>
        <td> Contact No.2: </td>
        <td><input type="text" name="Contact_2" value="<%= oRs("Contact_2") %>" SIZE=100></td>
    </tr>

    <tr>
        <td> Telephone No.2: </td>
        <td><input type="text" name="Telephone_2" value="<%= oRs("Telephone_2") %>" SIZE=100></td>
    </tr>

    <tr>
        <td> Fax Number: </td>
        <td><input type="text" name="Fax_No" value="<%= oRs("Fax_No") %>" SIZE=100></td>
    </tr>

        <tr>
        <td> Email address: </td>
        <td><input type="text" name="Email" value="<%= oRs("Email") %>" SIZE=100></td>
    </tr>

    <tr>
        <td> Order Number Procedure: </td>
        <td><input type="text" name="Order_Number_Proc" value="<%= oRs("Order_Number_Proc") %>" SIZE=100></td>
    </tr>

    <tr>
        <td> Paperwork Procedure: </td>
        <td><input type="text" name="Paperwork_Proc" value="<%= oRs("Paperwork_Proc") %>" SIZE=100></td>
    </tr>
    <tr>
        <td><input type="text" name="ID" value="<%=oRs("OPERATOR_Unid")%>"></td>
        <td><input type="submit" name="Submit" value="Submit Changes"></td>
    </tr>
<%
oRs.Close
set oRs=Nothing
set conn=Nothing
%>


</BODY>
</HTML>


*************************************

and the second page is:

<% @Language=VBScript %>
<%Option Explicit%>



<%
Dim oRs, conn, connect, strSQL, lngRecordNo, varOpID

lngRecordNo = Request.Form ("ID")

set conn=server.CreateObject ("adodb.connection")

connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("ContractDataWeb.mdb") & ";Persist Security Info=False"

conn.Open connect

Set oRs = Server.CreateObject("adodb.recordset")

strSQL="SELECT tblOperatorFile.* FROM tblOperatorFile WHERE OPERATOR_Unid =" & lngRecordNo

oRs.Open strSQL, conn, adOpenDynamic, adLockOptimistic

oRs("Contact_1")
oRs("Contact_1")
oRs("Contact_2")
oRs("TELEPHONE")
oRs("Telephone_2")
oRs("Telephone_OutofHours")
oRs("Fax_No")
oRs("Email")
oRs("Paperwork_Proc")
oRs("Order_Number_Proc")

oRs.Close

Set oRs=Nothing

conn.Close

set conn=Nothing

Response.Redirect "ContractOperator.asp"

%>

*********************************************

Any assistance will be greatfully received

Peter
 
Old November 2nd, 2007, 05:15 PM
Registered User
 
Join Date: Sep 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The error is in the SQL. Have you tried printing out the SQL? Most likely the ID somehow isn't getting passed on to the second script. Perhaps it's not entered into the text box before you click Submit. I also noticed you're missing a closing form tag (</form>).

Another thing is, you aren't sanitizing your input. This makes your code susceptible to SQL Injection Attacks.

I would change the following line

Code:
strSQL="SELECT tblOperatorFile.* FROM tblOperatorFile WHERE OPERATOR_Unid =" & lngRecordNo
to

Code:
strSQL="SELECT tblOperatorFile.* FROM tblOperatorFile WHERE OPERATOR_Unid =" & CLng(lngRecordNo)
...and then work on the error of the number not converting to a long.

I hope this helps!

 
Old November 5th, 2007, 05:06 AM
ppenn
Guest
 
Posts: n/a
Default

Thanks for your assistance I have done as you suggested and also changed the code that now works I have listed the working code below for anyone else who has a similar problem:

<% @Language=VBScript %>
<%Option Explicit%>



<%
Dim oRs, conn, connect, strSQL, lngRecordNo, varOpID

lngRecordNo = CLng(Request.Form ("ID"))

set conn=server.CreateObject ("adodb.connection")

connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/ContractDataWeb.mdb") & ";Persist Security Info=False"

conn.Open connect

Set oRs = Server.CreateObject("adodb.recordset")

strSQL="SELECT tblOperatorFile.* FROM tblOperatorFile WHERE OPERATOR_Unid = "& lngRecordNo &""

oRs.Open strSQL, conn, adOpenDynamic, adLockOptimistic

oRs.Fields("Contact_1") = request.Form("Contact_1")
oRs.Fields("Contact_2") = request.Form("Contact_2")
oRs.Fields("TELEPHONE") = request.Form("TELEPHONE")
oRs.Fields("Telephone_2") = request.Form("Telephone_2")
oRs.Fields("Telephone_OutofHours") = request.Form("Telephone_OutofHours")
oRs.Fields("Fax_No") = request.Form("Fax_No")
oRs.Fields("Email") = request.Form("Email")
oRs.Fields("Paperwork_Proc") = request.Form("Paperwork_Proc")
oRs.Fields("Order_Number_Proc") = request.Form("Order_Number_Proc")

oRs.Update

oRs.Close

Set oRs=Nothing

conn.Close

set conn=Nothing

%>

<html>
<Head><title>Update Records</title></head>
<body>
<table border="0" width="848">

    <tr>
        <TD width="31%"><IMG height=90 src="Default.gif" width=136 border=0></td>
        <td width="32%" align="right"><img border="0" src="MCTF.gif" width="136" height="90" align="right"></td>
    </tr>
    <tr>
        <td colspan="2")
        </td>
    </tr>
    <tr>
        <td width="848" colspan="4">
          <p align="center">You have successfully Updated the Record</p>
        </td>
</table>

<table border="0" width="848">
    </tr>
        <td>
        <p align="center"><input type="button" size="4" value="Click here to Continue" onClick="history.go(-1)" style="font-size: 12pt; font-weight: bold"></p>
        </td>
    </tr>
</table>


</body></html>
 
Old November 5th, 2007, 02:26 PM
Registered User
 
Join Date: Sep 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You're welcome!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax error (missing operator) in query expressio annwilly Classic ASP Databases 1 June 7th, 2006 09:53 AM
Error - Missing operator in query expression. akibaMaila VB.NET 2002/2003 Basics 1 January 18th, 2005 12:34 PM
Syntax Error /missing operator in Query Expression dsealer Classic ASP Databases 4 September 4th, 2004 07:19 PM
Syntax error (missing operator) in query expressio nidy_online Wrox Book Feedback 1 April 30th, 2004 06:40 PM
Syntax error (missing operator) in query expressio mvollmer Classic ASP Databases 7 December 5th, 2003 11:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.