search by two drop downs new error

[gavray]

Hi,

With my previous post I got some help and changed the syntex slightly I know get this error. Any suggestions gratefully received, thanks

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'region='brighton' AND WHERE
industry='accounting''.

form code is-

<%
Dim oRSv
Set oRSv=Server.CreateObject(&quot;adodb.Recordset&quot
oRSv.Open &quot;IndustryTable&quot;, &quot;dsn=50on&quot;
oRSv.MoveFirst
%>

<form method=&quot;post&quot; action=&quot;industrysearchregion.asp&quot;>
<h5>Select region</h5>

<input type=&quot;submit&quot; value=&quot;search&quot;>
<select name=&quot;industry&quot; size=&quot;1&quot;>

<%

Do While NOT oRSv.EOF
Response.Write &quot;<option value='&quot; & oRSv(&quot;industry&quot & &quot;'>&quot;
Response.Write oRSv(&quot;industry&quot & &quot;</option>&quot;
oRSv.MoveNext
loop
oRSv.Close
Set oRSv=nothing
%>
</select>
<%
Dim oRSr
Set oRSr=Server.CreateObject(&quot;adodb.Recordset&quot
oRSr.Open &quot;RegionTable&quot;, &quot;dsn=50on&quot;
oRSr.MoveFirst
%>

<h5>Select region</h5>
<input type=&quot;hidden&quot;>
<select name=&quot;region&quot; size=&quot;1&quot;>

<%
Do While NOT oRSr.EOF
Response.Write &quot;<option value='&quot; & oRSr(&quot;region&quot & &quot;'>&quot;
Response.Write oRSr(&quot;region&quot & &quot;</option>&quot;
oRSr.MoveNext
loop
oRSr.Close
Set oRSr=nothing
%>
</select>

response page-

<%
varindustry=Request.Form(&quot;industry&quot
varregion=Request.Form(&quot;region&quot
Dim oRS2
dim iRowCounter
Set oRS2=Server.CreateObject(&quot;adodb.Recordset&quot

sSQL=&quot;SELECT JobTable.Company, JobTable.industry, JobTable.Jobtitle, JobTable.region&quot;
sSQL=sSQL & &quot; FROM JobTable&quot;
sSQl=sSQL & &quot; WHERE region='&quot; & varregion & &quot;'&quot;
sSQL=sSQL & &quot; AND WHERE industry='&quot; & varindustry & &quot;'&quot;
sSQL=sSQL & &quot; ORDER BY JobTable.Jobtitle;&quot;


oRS2.open sSQL, &quot;DSN=50on&quot;

if oRS2.EOF=True then
Response.write (&quot;Sorry, no jobs were found in this sector&quot

else oRS2.MoveFirst

response.write &quot;<table bgcolor=Silver valign=top width=500 bordercolordark=Gray border=0>&quot;

response.write &quot;<th bgcolor=white> Job title </th>&quot; & &quot;<th bgcolor=white> Industry </th>&quot; & &quot;<th bgcolor=white>Company</th>&quot; & &quot;<th bgcolor=white> Region </th>&quot;


Do while Not oRS2.EOF
''<a href='&quot; & oRS2 (&quot;ID&quot & &quot;'>
Response.Write &quot;<tr><td>&quot; & oRS2 (&quot;Jobtitle&quot & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS2 (&quot;industry&quot & &quot;</td>&quot;
response.write &quot;<td>&quot; & oRS2 (&quot;Company&quot & &quot;</td>&quot;
response.write &quot;<td>&quot; & oRS2 (&quot;region&quot & &quot;</td></tr>&quot;

oRS2.MoveNext

Loop
end if
oRS2.Close
set oRS2=Nothing
response.write &quot;</table>&quot;
%>

 

[culshaja]

The following code is what is wrong:

sSQl=sSQL & " WHERE region='" & varregion  &  "'" 
sSQL=sSQL & " AND WHERE industry='" & varindustry & "'"
sSQL=sSQL & " ORDER BY JobTable.Jobtitle;"

You put a second WHERE in there. Try the follwoing:

sSQl=sSQL & " WHERE region='" & varregion  &  "'" 
sSQL=sSQL & " AND industry='" & varindustry & "'"
sSQL=sSQL & " ORDER BY JobTable.Jobtitle;"

James

James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305