Once Again, Is there an easier way to code this?

[Trope]

I think I am taking the long way to do this. Does anyone have an suggestions for me how I could clean up this code - it's getting quite long.

This is the process page for an advanced search page for a recruiting site I am doing.

'------------------ CODE START
'.................................
' Associative Array of Field Names
' and Table Names for Searching
'.................................
DIM fieldName(34)
DIM tableName(34)

fieldName(1) = "People_BusinessName"
tableName(1) = "tbl_people"

fieldName(2) = "People_FirstName"
tableName(2) = "tbl_people"

fieldName(3) = "People_LastName"
tableName(3) = "tbl_people"

fieldName(4) = "People_Phone_Home"
tableName(4) = "tbl_people"

fieldName(5) = "People_Phone_Business"
tableName(5) = "tbl_people"

fieldName(6) = "People_Email"
tableName(6) = "tbl_people"

fieldName(7) = "People_Address"
tableName(7) = "tbl_people"

fieldName(8) = "People_City"
tableName(8) = "tbl_people"

fieldName(9) = "People_State"
tableName(9) = "tbl_people"

fieldName(10) = "People_ZipCode"
tableName(10) = "tbl_people"

fieldName(11) = "People_PositionDesired"
tableName(11) = "tbl_people"

fieldName(12) = "People_HourlyRate"
tableName(12) = "tbl_people"

fieldName(13) = "Resume_Title"
tableName(13) = "tbl_resumes"

fieldName(14) = "Resume_Exp_Location1"
tableName(14) = "tbl_resumes"

fieldName(15) = "Resume_Exp_Position1"
tableName(15) = "tbl_resumes"

fieldName(16) = "Resume_Exp_Company1"
tableName(16) = "tbl_resumes"

fieldName(17) = "Resume_Exp_Location2"
tableName(17) = "tbl_resumes"

fieldName(18) = "Resume_Exp_Position2"
tableName(18) = "tbl_resumes"

fieldName(19) = "Resume_Exp_Company2"
tableName(19) = "tbl_resumes"

fieldName(20) = "Resume_Exp_Position3"
tableName(20) = "tbl_resumes"

fieldName(21) = "Resume_Exp_Location3"
tableName(21) = "tbl_resumes"

fieldName(22) = "Resume_Exp_Company3"
tableName(22) = "tbl_resumes"

fieldName(23) = "Resume_Comment"
tableName(23) = "tbl_resumes"

fieldName(24) = "Resume_Job_Wanted"
tableName(24) = "tbl_resumes"

fieldName(25) = "Resume_Salary_Range"
tableName(25) = "tbl_resumes"

fieldName(26) = "Resume_Exp_Resp1"
tableName(26) = "tbl_resumes"

fieldName(27) = "Resume_Exp_Resp2"
tableName(27) = "tbl_resumes"

fieldName(28) = "Resume_Exp_Resp3"
tableName(28) = "tbl_resumes"

fieldName(29) = "Resume_Exp_Comm1"
tableName(29) = "tbl_resumes"

fieldName(30) = "Resume_Exp_Comm2"
tableName(30) = "tbl_resumes"

fieldName(31) = "Resume_Exp_Comm3"
tableName(31) = "tbl_resumes"

fieldName(32) = "targetpay"
tableName(32) = "tbl_resumes"

fieldName(33) = "travelpreference"
tableName(33) = "tbl_resumes"

fieldName(34) = "relocate"
tableName(34) = "tbl_resumes"


'.................................
' Retrieve All Form Variables
'.................................
search_keyword = Request.Form("search_keyword&quot
job_category = Request.Form("job_category&quot
search_city = Request.Form("job_category&quot
search_state = Request.Form("job_category&quot
search_zip = Request.Form("job_category&quot
CID = Request.Form("job_category&quot
EmpType = Request.Form("job_category&quot
Freshness = Request.Form("job_category&quot
CMTP = Request.Form("job_category&quot
minsalary = Request.Form("job_category&quot
maxsalary = Request.Form("job_category&quot
ExcludeResumes = Request.Form("job_category&quot

'.................................
' Build Complex Select Statement!
'.................................

strSQL = "SELECT tbl_people.*, tbl_resumes.* FROM tbl_people, tbl_resumes "
strSQL = strSQL + " WHERE "

'.................................
' Are We Checking For Keywords?
'.................................
IF search_keyword<>&quot;&quot; THEN

' Set Flag
bolAddAnd = true

' Loop through Associative Array
FOR x = 1 to UBound(FieldName)
strSQL = strSQL & tableName(x) & &quot;.&quot; & fieldName(x) & &quot; = '&quot; & search_keyword & &quot;' &quot;
IF x<>UBound(FieldName) THEN
strSQL = strSQL + &quot; OR &quot;
END IF
NEXT

'Response.Write(strSQL)
'Response.End()
END IF

'.............................
' Check Categories
'.............................
IF job_category <> &quot;&quot; AND job_category<>&quot;All&quot; THEN
' Set the AND flag
bolAddAnd = true
IF bolAddAnd = true THEN
strSQL = strSQL & &quot; AND tbl_resumes.Category = '&quot; & job_category & &quot;'&quot;
ELSE
strSQL = strSQL & &quot; tbl_resumes.Category = '&quot; & job_category & &quot;'&quot;
END IF
END IF

'.............................
' Check City
'.............................
IF search_city<>&quot;&quot; THEN
bolAddAnd = true
IF bolAddAnd = true THEN
strSQL = strSQL & &quot; AND tbl_people.people_city = '&quot; & search_city & &quot;'&quot;
ELSE
strSQL = strSQL & &quot; tbl_people.people_city = '&quot; & search_city & &quot;'&quot;
END IF
END IF

'.............................
' Check State
'.............................
IF search_state<>&quot;&quot; THEN
bolAddAnd = true
IF bolAddAnd = true THEN
strSQL = strSQL & &quot; AND tbl_people.people_state = '&quot; & search_state & &quot;'&quot;
ELSE
strSQL = strSQL & &quot; tbl_people.people_state = '&quot; & search_state & &quot;'&quot;
END IF
END IF

'---------- END SNIPPET

As you can see, this is going to go ON and ON for about another 20 or 30 fields! Ugh.

I am hoping one of you SQL guru's has a few ideas for me. Although this MAY work (have not tested yet) - I just have this feeling I am coding in a very inefficient manner.

Thanks everyone.
Giovanni

 

[WebNickson]

I am not too good trying to understand your code but i think you can reduce a lot of lines by converting ur Array declaration to

fieldName = Array (&quot;People_BusinessName.tbl_people&quot;, &quot;...&quot;, &quot;...&quot

I joined the 2 fields together to 1 array because the code does not seem to need to differentiate.

Also, you need to change you looping to start from 0.

rgds,
Nickson