I have seen this problem before, I cannot find a solution.
I am using ASP with an access database. I have a form that is has a combo box called resno displaying school names and resno IDs the Resno is the stored value and is a text field.

<form action="<%=MM_editAction%>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right">School:</td>
<td><select name="REsNo">
While (NOT rs_SchoolsAll.EOF)
%><option value="<%=(rs_SchoolsAll.Fields.Item("DESTNo").Value)%>"><%=(rs_SchoolsAll.Fields.Item("SchoolSelect").Value)%></option>
If (rs_SchoolsAll.CursorType > 0) Then
End If
</select> </td>
<tr valign="baseline">
<td nowrap="nowrap" align="right">&nbsp;</td>
<td><input type="submit" value="Invite School to join Initiative Proposal" /><input name="nmPostback" type="hidden" id="nmPostback" value="True" /> </td>
<input type="hidden" name="NetworkID" value="<%=(rs_AvailablePlans.Fields.Item("NetworkID").Value)%>" />
<input type="hidden" name="Year" value="<%=(rs_UserYear.Fields.Item("Year").Value)%>" />
<input type="hidden" name="MM_insert" value="form1" />


By the click of the button on the form it inserts a record into a table (no worries) an then goes to the next page that has a script with values from a recordset that displays information about the selected school. (Doing this before sending an automated email) I get the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'DestNo='.

/network_initiativeADDSelectedSchoolsCheck.asp, line 261

Using this Code
Recordset Code:
Dim rs_BlurbDisplay
Dim rs_BlurbDisplay_cmd
Dim rs_BlurbDisplay_numRows

Set rs_BlurbDisplay_cmd = Server.CreateObject ("ADODB.Command")
rs_BlurbDisplay_cmd.ActiveConnection = MM_conn_Sidecounter_STRING
rs_BlurbDisplay_cmd.CommandText = "SELECT * FROM qrySchoolInviteEmailSend WHERE DestNo="& Request.Form("REsNo") &""
rs_BlurbDisplay_cmd.Prepared = true

Set rs_BlurbDisplay = rs_BlurbDisplay_cmd.execute
rs_BlurbDisplay_numRows = 0

If I replace DestNo="& Request.Form("REsNo") &" with DestNo='999991' (actual destno value) it works no problem. If I have no where statement works no problem.

I cannot pick the problem. Please help


sometimes these queries act funny doing it this way. Try setting the request.Form("REsNo") to a value then inputting it into the destno query that way. Also check the REsNo is spelled correctly in your code on both pages. Then make sure that one is selected by Default.

Actually, your problem is this:
You did not put ' before your Request.Form and after it. It seems that if DestNo='999991' works, then the column needs the ' before and after your command. Use this below:

rs_BlurbDisplay_cmd.CommandText = "SELECT * FROM qrySchoolInviteEmailSend WHERE DestNo='" & Request.Form("REsNo") & "'"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.