Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

multiple combo box SQL

Posted on 2002-04-01
11
Medium Priority
?
322 Views
Last Modified: 2011-04-14
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.
0
Comment
Question by:TenTonJim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6910667
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
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6910755
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
 
LVL 2

Author Comment

by:TenTonJim
ID: 6910807
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Expert Comment

by:kcm76
ID: 6910838
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
 
LVL 2

Author Comment

by:TenTonJim
ID: 6911013
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
 
LVL 5

Accepted Solution

by:
kcm76 earned 400 total points
ID: 6911086
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
 
LVL 2

Author Comment

by:TenTonJim
ID: 6921233
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
 
LVL 2

Author Comment

by:TenTonJim
ID: 6921279
dammit it won't let me decrease the points
0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6921307
post a request in the community support section.
they will decrease the points for you.

ASPGuru
0
 
LVL 2

Author Comment

by:TenTonJim
ID: 6931565
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
 
LVL 1

Expert Comment

by:Computer101
ID: 6932608
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question