providing a search by two drop down menu's containing variables ;o

gavray · Nov 21, 2000

Hi, this is a tricky one, ;O

I can successfully provide a search for say 'region' but when trying to combine this with a search for say 'industry' at the same time. e.g 'region' louisiana 'jobs' health care. I'd be really gratefull cause I've been surfing all day for an answer. Thanks

It gives me the error

Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: "SELECT JobTable.Comp"]'

/industrysearchregion.asp, line 19

the code is as follows

form-

<html>
<head>
<title>Untitled</title>
</head>

<body>
<%
Dim oRSv
Set oRSv=Server.CreateObject("adodb.Recordset&quot

oRSv.Open "IndustryTable", "dsn=50on"
oRSv.MoveFirst
%>

<form method="post" action="industrysearchregion.asp">
<h5>Select region</h5>

<input type="submit" value="search">
<select name="industry" size="1">

<%

Do While NOT oRSv.EOF
Response.Write "<option value='" & oRSv("industry&quot

& "'>"
Response.Write oRSv("industry&quot

& "</option>"
oRSv.MoveNext
loop
oRSv.Close
Set oRSv=nothing
%>
</select>
<%
Dim oRSr
Set oRSr=Server.CreateObject("adodb.Recordset&quot

oRSr.Open "RegionTable", "dsn=50on"
oRSr.MoveFirst
%>

<h5>Select region</h5>
<input type="hidden">
<select name="region" size="1">

<%
Do While NOT oRSr.EOF
Response.Write "<option value='" & oRSr("region&quot

& "'>"
Response.Write oRSr("region&quot

& "</option>"
oRSr.MoveNext
loop
oRSr.Close
Set oRSr=nothing
%>
</select>

</body>
</html>

response page-

<html>
<head>
<title>search a job</title>
</head>
<body>

<%
varindustry=Request.Form("industry&quot

varregion=Request.Form("region&quot

Dim oRS2
dim iRowCounter
Set oRS2=Server.CreateObject("adodb.Recordset&quot

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

oRS2.open sSQL, "DSN=50on"

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

else oRS2.MoveFirst

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

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

Do while Not oRS2.EOF
''<a href='" & oRS2 ("ID&quot

& "'>
Response.Write "<tr><td>" & oRS2 ("Jobtitle&quot

& "</td>"
Response.Write "<td>" & oRS2 ("industry&quot

& "</td>"
response.write "<td>" & oRS2 ("Company&quot

& "</td>"
response.write "<td>" & oRS2 ("region&quot

& "</td></tr>"

oRS2.MoveNext

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

</font>
</body>
</html>

cheeeeers!

Gavin

khorjak · Nov 21, 2000

In your response page, the following line:

Code:

sSQL=sSQL & &quot;'&quot; AND &quot;WHERE industry='&quot; & varindustry & &quot;'&quot;

should be:

Code:

sSQL=sSQL & &quot; AND WHERE industry='&quot; & varindustry & &quot;'&quot;

You might also want to rethink the way you're building your SQL statement. What happens if someone leaves 'region' blank and only enters 'industry'? You'd most probably return no records.

Choo Khor
choo.khor@intelebill.com

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

providing a search by two drop down menu's containing variables ;o

gavray

Programmer

khorjak

Programmer

Similar threads

Part and Inventory Search

Sponsor