help with sql statement and grid display 1

[frogggg]

I have 2 pages - one accepts all sorts of criteria from the user to search by, and the second has a sql statement which retrieves any records which match all criteria.
Here's the statement:
SELECT JobCategory.JobCatName, CareerLevel.CareerName, CandidateContactInfo.CandidateID, Locations.LocationName, SkillCategory.SkillName " _
& "FROM CandidateContactInfo, CandidateJobCategory, JobCategory, CareerLevel, Locations, CandidateLocations, SkillCategory, CandidateSkills " _
& "WHERE CandidateJobCategory.JobCatID = " & session("SearchJobCategory&quot _
& "AND CandidateJobCategory.JobCatID = JobCategory.JobCatID " _
& "AND CandidateContactInfo.CareerLevel = CareerLevel.CareerID " _
& "AND CandidateContactInfo.CareerLevel = " & session("SearchCareer&quot _
& "AND CandidateLocations.LocationID = " & session("SearchLocation&quot _
& "AND CandidateLocations.LocationID = Locations.LocationID " _
& "AND CandidateSkills.SkillID = SkillCategory.SkillID " _
& "AND CandidateSkills.SkillID IN (" & skills & &quot" _
& "AND CandidateContactInfo.CandidateID = CandidateJobCategory.CandidateID

This works fine. Then I display the results of the query in a grid. The problem is that I would like to allow multiple select in each of the categories. I already did it for skills, as you can (sort of) see. But when displayed in the grid, I get 2 entries, one for each selected skill.

What do I need to change in my statement, or in my grid display, to get it to show one line for each candidate with all the information about that candidate:
job categories, careers, locations, skills, candidateID
job categories, etc. next candidateID
instead of
job category, etc. candidateID
job category, etc. same candidateID

I think this sounds pretty complicated, but I hope someone can figure out what I want and point me in the right direction! Thanks!

 

[MerlinB]

What you (and every SQL user, if only they knew) want is a SUM feature for string columns - that concatenate the values together, rather than just to add them up.

If you have sqlserver 2000, then you might be able to use user-defined functions.

You could create a Stored Procedure, with a cursor that looped thru this recordset and built a temp table with the comma-separated summaries for categories, careers, locations etc.

However, probably the easiest method is to do this in your ASP page - rather than have a DTC grid, build the grid manually with TR, TD etc.. Within each TR, loop around the recordset gathering up the various values until you get another candidate, then dump out the results separated by TD tags - use Response.Write for the whole table rather than loads of <%=...%> type entries which are really difficult to understand in a few months time.

You can base the TABLE, TR and TD attributes on the ones that are currently generated by your grid (use ViewSource in the browser).

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[frogggg]

Thanks. That sounds like just what I need to do. I need a little help with the loop though. First of all, I think what I have now is an infinite loop. Here's my code:

do while not rsDisplayResumes.EOF
Response.Write &quot;<td WIDTH='105'><font Size='2' Face='Arial' Color='Black'>&quot; & rsDisplayResumes.fields.getValue(&quot;JobCatName&quot & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='97'><font Size='2' Face='Arial' Color='Black'>&quot; & rsDisplayResumes.fields.getValue(&quot;CareerName&quot & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='68'><font Size='2' Face='Arial' Color='Black'>&quot; & rsDisplayResumes.fields.getValue(&quot;LocationName&quot & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='68'><font Size='2' Face='Arial' Color='Black'>&quot; & rsDisplayResumes.fields.getValue(&quot;SkillName&quot & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='68'><font Size='2' Face='Arial' Color='Black'><a href='viewResumes.asp?CandidateID=&quot; & rsDisplayResumes.fields.getValue(&quot;CandidateID&quot & &quot;'>View Resumes</a></td>&quot;
loop

Besides the infinite loop problem, I'm sure this will not do anything different than the grid was doing. How do I get it to list more than one of the various categories (I don't mind if they're in the same cell of the table separated by commas) for each candidateID?

Thanks for your help.

 

[MerlinB]

Try a rsDisplayResumes.moveNext before the loop.

And yes, it would do nothing different yet. You need two loops. The inner loop adding each column to a string variable until the candidate ID changes. The outer loop writes the variables to the table for that candidate - and so on until the recordset has ended.

dim strJobCat
dim intCurrentCandidate
with rsDisplayResumes
do while not .EOF
intCurrentCandidate = .fields.getValue(&quot;CandidateID&quot
strJobCat = &quot;&quot;
do while intCurrentCandidate = .fields.getValue(&quot;CandidateID&quot
strJobCat = strJobCat & &quot;, &quot; & .fields.getValue(&quot;JobCatName&quot
.etc.
MOVE NEXT!!!
if .EOF then exit loop
loop
Response.Write &quot;<td WIDTH='105'><font Size='2' Face='Arial' Color='Black'>&quot; & strJobCat & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='68'><font Size='2' Face='Arial' Color='Black'><a href='viewResumes.asp?CandidateID=&quot; & intCurrentCandidate & &quot;'>View Resumes</a></td>&quot;
loop
end with

Does this make sense now?
You will need to do something a bit better so that the leading comma is removed from the strJobCat variable.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[frogggg]

Thanks a bunch! I've still got a few problems:
on the
if .EOF then exit loop
line, I get Invalid 'exit' statement.
so I took out that line and probably caused this error:
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Here's my code. Can you help?

dim strJobCat
dim strCareerLev
dim strLocation
dim strSkill
dim intCurrentCand
with rsDisplayResumes
do while not .EOF
intCurrentCand = .fields.getValue(&quot;CandidateID&quot
strJobCat = &quot;&quot;
strCareerLev = &quot;&quot;
strLocation = &quot;&quot;
strSkill = &quot;&quot;
do while intCurrentCand = .fields.getValue(&quot;CandidateID&quot
strJobCat = strJobCat & &quot;,&quot; & .fields.getValue(&quot;JobCatName&quot
strCareerLev = strCareerLev & &quot;,&quot; & .fields.getValue(&quot;CareerName&quot
strLocation = strLocation & &quot;,&quot; & .fields.getValue(&quot;LocationName&quot
strSkill = strSkill & &quot;,&quot; & .fields.getValue(&quot;SkillName&quot
.moveNext
if .EOF then exit loop
loop
Response.Write &quot;<tr bgcolor='White' align='Left' bordercolordark='Navy' bordercolorlight='Silver'>&quot;
Response.Write &quot;<td WIDTH='105'><font Size='2' Face='Arial' Color='Black'>&quot; & strJobCat & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='97'><font Size='2' Face='Arial' Color='Black'>&quot; & strCareerLev & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='68'><font Size='2' Face='Arial' Color='Black'>&quot; & strLocation & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='68'><font Size='2' Face='Arial' Color='Black'>&quot; & strSkill & &quot;</td>&quot;
Response.Write &quot;<td WIDTH='68'><font Size='2' Face='Arial' Color='Black'><a href='viewResumes.asp?CandidateID=&quot; & intCurrentCandidate & &quot;'>View Resumes</a></td></tr>&quot;

rsDisplayResumes.moveNext
loop
end with

 

[frogggg]

I fixed the Invalid 'exit' statement. problem, it should have been
if .EOF then exit do
But, it did not do what I wanted it to do.
It prints the same info as many times as criteria there are, i.e. if one field has one criteria, it prints it 4 times, if another field has two criteria, it prints each one twice. I know it sounds confusing, but it is!

So what do I need to do to get it to go through each type of criteria, spit out all selected ones, and then go to the next criteria? The only benefit of this code over the dtc grid is that it has only one entry for the same candidate, which is a start.

Thanks for your help.

 

[MerlinB]

While you could try adding DISTINCT to the SQL select clause, or to use a GROUP BY clause, I suspect that this will not help.

You could, however, apply the DISTINCT or GROUP BY to each of the FROM tables - converting each table to a SELECT clause:

SELECT a.Cat, b.Role, c.ID
FROM Candidate c
INNER JOIN (SELECT DISTINCT Cat, ID FROM Cats) a
ON a.ID = c.ID
INNER JOIN (SELECT DISTINCT Role, ID FROM Roles) b
ON b.ID = c.ID
..etc..

This will ensure each list for each candidate is not repeated - yet will be brought together in a single SQL clause.

TTFN

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[frogggg]

I'm not sure I understand - do you mean I should change all the WHERE clauses to INNER JOIN clauses with SELECT DISTINCT inside them? Also, I'm not exactly sure how to apply your generic names to mine.

Thanks for your help!