Solved

multiple combo box SQL

Posted on 2002-04-01
11
315 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 5

Accepted Solution

by:
kcm76 earned 100 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 run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now