multiple combo box SQL

Hello EE'ers,
I have this job search site I am doing that needs to allow the user to select multple cities and multiple job titles in their search (using 2 combo boxes set to multiple). http://tentonweb.com/dentstaff then click on permanent jobs to see what I mean.

I need to know how to iterate through both of these combo boxes to write the appropriate SQL statement for the job search.
--------------------------
Using Access2000 database - 3 tables:
tblJobOpenings (consist of joID joTitle and joLocation fields)
tblJobTitles
tblLocation
--------------------------
The tblJobOpenings has
joTitle
joLocation

fields which need to relate to the

tblJobTitles (consist of jobID and jobTitle fields)
tblLocation (consist of locID and locCity fields)

tables
--------------------------

Thanks,
JimS.
LVL 2
TenTonJimAsked:
Who is Participating?
 
Chandramouli kArchitectCommented:
Try this Query


DIM Objrs, Objcn
DIM strJobCity, strJobTitle, strQuery

strJobCity = Replace(Request.Form("combocity"),",","','")
strJobCity = "'" & strJobCity & "'"

strJobTitle = Replace(Request.Form("comboTitle"),",","','")
strJobTitle = "'" & strJobTitle & "'"

strQuery = "SELECT JO.* FROM tblJobOp AS JO, tblJobTitles AS JT, tblLocation AS JL WHERE JO.JobOpTitle = JT.JobTitID " _
          & " AND JO.JobOpLocation = JL.locID AND JT.JobTitTitle in (" &  strJobTitle & ") AND JL.locCity in (" & strJobCity  & ") "


SET Objrs = Server.CreateObject("ADODB.RECORDSET")
SET Objcn = Server.CreateObject("ADODB.CONNECTION")

Objcn.Open "***********"
Objrs.Open strQuery, Objcn, 0, 1

IF NOT (Objrs.BOF AND Objrs.EOF) THEN
      'DISPLAY THE RECORDS IN DESIRED FORMAT
ELSE
       Response.Write "No Job Found"
END IF

Objrs.Close
SET Objrs = NOTHING

Objcn.Close
SET Objcn = NOTHING


ALL THE BEST
KCM
0
 
fritz_the_blankCommented:
This should get you started:

strList = Request.Form("ComboFieldName")

arrList = split(strList,";")

for i=LBound(arrList) to UBound(arrList)
   Response.write(arrList(i))
next


Fritz the Blank
0
 
ASPGuruCommented:
fritz, that's almost right....

if you want to use split, then it should be:
arrList = split(strList,", ")

but you can also do this:
for each f in Request.Form("ComboFieldName")
  response.write f & "<br>"
next

this means you can either treat Request.Form("ComboFieldName") as a String or as an Array.

If you treat it as a String, then it looks like "option1, option2, option3"...

so you could even do something like this, if the values of the options are IDs:

sql="select * from table where id in (" & Request.Form("ComboFieldName") & ")"


hope that helps,

ASPGuru
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
TenTonJimAuthor Commented:
Thanks guys let me check it out I am busy as heck may be a day or two.

How do I get the 2 combo boxes (both are multiple) to work in conjunction with one another? (in the sql query)

Thanks,
JimS.
0
 
Chandramouli kArchitectCommented:
i have submitted the code in brief:

dim strJobCity, strJobTitle, strQuery
strJobCity = Replace(Request.Form("combocity"),",","','")
strJobCity = "'" & strJobCity & "'"

strJobTitle = Replace(Request.Form("comboTitle"),",","','")
strJobTitle = "'" & strJobTitle & "'"

'********Query*******
strQuery = " select JO.* from tbljobopenenings as JO, tbljobTitles as JT, tblLocation AS JL WHERE JO.Jobid = JT.Jobid AND JO.JoLocation = JL.locid AND JL.location in (" & strJobcity & ") AND JT.Titles IN (" & strJobTitle &")
'*********************

'********pass the query to a recordset********
Objrs.Open strQuery, Objcn
'*********************************************

if the query doesnot work please give the table structure in detail.

ALL THE BEST
KCM

0
 
TenTonJimAuthor Commented:
ok I will check it out.

the db can be downloaded at http://tentonweb.com/dentstaff/vault/master.mdb

only 1 db with 4 tables in it

Thanks,
Jim
0
 
TenTonJimAuthor Commented:
OK I am going to split the points...
100 for kcm76 since he did the most work
50 for fritz the blank
50 for asp guru


parts of each helped but did not totally nail it.

Here is the finished code that produces the job search at http://tentonweb.com/dentstaff

Thanks folks!

JimS.
_________________________________________________

<%
Else
      jrs3.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath

cities = request.form("cities")
jobs = request.form("positions")
showall=request.form("showall")

If showall = "on" Then
jrs3.Open "SELECT [tblJobOp].[jobOpID], [tblJobOp].[jobOpDescription], [tblJobOp].[jobOpPerm], [tblJobOp].[jobOpPT], [tblJobOp].[jobOpFT], [tblJobOp].[jobOpStartDate], [tblJobOp].[jobOpEndDate], [tblJobOp].[jobOpActive], [tblJobTitles].[jobTitTitle], [tblLocation].[locCity] FROM tblLocation INNER JOIN (tblJobTitles INNER JOIN tblJobOp ON [tblJobTitles].[jobTitID]=[tblJobOp].[jobOpTitle]) ON [tblLocation].[locID]=[tblJobOp].[jobOpLocation] WHERE ([tblJobOp].[jobOpActive]=True AND [tblJobOp].[jobOpPerm]=True) ORDER BY [tblLocation].[locCity]"

Else

cities=request.form("cities")
jobs=request.form("positions")

 if jobs<>"" AND cities<>"" Then
  if instr(cities,",")>0 then
  ocities=split(cities,",")
  for i=0 to ubound(ocities)
    if i>0 then SQL_city=SQL_city&" OR " end if
    SQL_city=SQL_city&" [tblJobOp].[jobOpLocation]="&ocities(i)&""
  next
  else
  SQL_city="jobOpLocation="&cities&""
end if

 if instr(jobs,",")>0 then
  ojobs=split(jobs,",")
  for i=0 to ubound(ojobs)
    if i>0 then SQL_job=SQL_job&" OR " end if
    SQL_job=SQL_job&" [tblJobOp].[jobOpTitle]="&ojobs(i)&""
    next
 else
  SQL_job="jobOpTitle="&jobs&""
 end if
jrs3.Open "SELECT [tblJobOp].[jobOpID], [tblJobOp].[jobOpDescription], [tblJobOp].[jobOpPerm], [tblJobOp].[jobOpPT], [tblJobOp].[jobOpFT], [tblJobOp].[jobOpStartDate], [tblJobOp].[jobOpEndDate], [tblJobOp].[jobOpActive], [tblJobTitles].[jobTitTitle], [tblLocation].[locCity] FROM tblLocation INNER JOIN (tblJobTitles INNER JOIN tblJobOp ON [tblJobTitles].[jobTitID]=[tblJobOp].[jobOpTitle]) ON [tblLocation].[locID]=[tblJobOp].[jobOpLocation] WHERE ("&SQL_city&") AND ("&SQL_job&") AND ([tblJobOp].[jobOpActive]=True AND [tblJobOp].[jobOpPerm]=True) ORDER BY [tblLocation].[locCity], [tblJobTitles].[jobTitTitle]"
 End If

 if jobs<>"" AND cities="" Then
  if instr(jobs,",")>0 then
  ojobs=split(jobs,",")
  for i=0 to ubound(ojobs)
    if i>0 then SQL_job=SQL_job&" OR " end if
    SQL_job=SQL_job&" [tblJobOp].[jobOpTitle]="&ojobs(i)&""
  next
 else
  SQL_job="[tblJobOp].[jobOpTitle]="&jobs&""
 end if
jrs3.Open "SELECT [tblJobOp].[jobOpID], [tblJobOp].[jobOpDescription], [tblJobOp].[jobOpPerm], [tblJobOp].[jobOpPT], [tblJobOp].[jobOpFT], [tblJobOp].[jobOpStartDate], [tblJobOp].[jobOpEndDate], [tblJobOp].[jobOpActive], [tblJobTitles].[jobTitTitle], [tblLocation].[locCity] FROM tblLocation INNER JOIN (tblJobTitles INNER JOIN tblJobOp ON [tblJobTitles].[jobTitID]=[tblJobOp].[jobOpTitle]) ON [tblLocation].[locID]=[tblJobOp].[jobOpLocation] WHERE ("&SQL_job&") AND ([tblJobOp].[jobOpActive]=True AND [tblJobOp].[jobOpPerm]=True) ORDER BY [tblLocation].[locCity], [tblJobTitles].[jobTitTitle]"
 End If

 if jobs="" AND cities<>"" Then
 if instr(cities,",")>0 then
  ocities=split(cities,",")
  for i=0 to ubound(ocities)
    if i>0 then SQL_city=SQL_city&" OR " end if
    SQL_city=SQL_city&" [tblJobOp].[jobOpLocation]="&ocities(i)&""
  next
 else
  SQL_city="[tblJobOp].[jobOpLocation]="&cities&""
 end if
jrs3.Open "SELECT [tblJobOp].[jobOpID], [tblJobOp].[jobOpDescription], [tblJobOp].[jobOpPerm], [tblJobOp].[jobOpPT], [tblJobOp].[jobOpFT], [tblJobOp].[jobOpStartDate], [tblJobOp].[jobOpEndDate], [tblJobOp].[jobOpActive], [tblJobTitles].[jobTitTitle], [tblLocation].[locCity] FROM tblLocation INNER JOIN (tblJobTitles INNER JOIN tblJobOp ON [tblJobTitles].[jobTitID]=[tblJobOp].[jobOpTitle]) ON [tblLocation].[locID]=[tblJobOp].[jobOpLocation] WHERE ("&SQL_city&") AND ([tblJobOp].[jobOpActive]=True AND [tblJobOp].[jobOpPerm]=True) ORDER BY [tblLocation].[locCity], [tblJobTitles].[jobTitTitle]"
 End If
End If
%>
_________________________________________________
0
 
TenTonJimAuthor Commented:
dammit it won't let me decrease the points
0
 
ASPGuruCommented:
post a request in the community support section.
they will decrease the points for you.

ASPGuru
0
 
TenTonJimAuthor Commented:
ok folks I posted the link and suggested point split in the community support section

100 for kcm76
50 for fritz the blank
50 for asp guru

thanks again,
JimS
0
 
Computer101Commented:
Points reduced to 100.  Now you can accept a comment from an expert.  After that, make other questions in this topic area for the other experts.

Computer101
E-E Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.