p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server ASP (http://p2p.wrox.com/forumdisplay.php?f=101)
-   -   Searching Criteria Issue (http://p2p.wrox.com/showthread.php?t=30488)

phungleon June 15th, 2005 03:34 PM

Searching Criteria Issue
 
Hi all,
I have an issue with creating a search page. This should be easy, but i don't know which method to use. Please help.

First of all, i have an input page with 20 checkboxes; each with a unique Academic Majors(EX: computer science, electric engineer, doctor,etc.). Users can check up to 4 academic majors. When they hit 'submit' the data will be stored in a column(separated by a comma). For example let say a user checked NURSE, ACCOUNTANT, LAWYER. These 3 will be stored a field named "AMajor."

Here is my question. I need to create a SEARCH PAGE with the same 20 checkboxes. Let say I checked NURSE, ACCOUNTANT, and LAWYER. Anyone who selected these 3 majors in the input page should come up on my search result. How would I code my SELECT STATEMENT in my search page?

Thank you.

LEon




happygv June 15th, 2005 06:17 PM

Hi Leon,

May be you got to write something like this in your ASP code so that it creates as many AND conditions as that of the selected Checkboxes

NURSE ***
ACCOUNTANT ***
LAWUER ***
DOCTOR
ENGINEER
...
...

out of which the *** marked are selected. When you request for the Checkbox values, it would come as comma separated string, which you could split and have a loop from its lbound() to ubound() value and for each you got to construct an and condition for the where clause in select.

eg:
Code:

strsql = "Select * from TABLENAME where "

strList=Request.Form("chkList")
ArrChecked = split(strList)

For i=lbound(ArrChecked) to ubound(ArrChecked)
    strsql = strsql & CHARINDEX(ArrChecked(i), AMajor) > 0
    If i < ubound(ArrChecked) then strsql = strsql & " AND "
Next
Response.write strsql

Execute the output of strsql in your DB and see if the result is what you expect.

Hope that helps.
Cheers

_________________________
- Vijay G
Strive for Perfection

phungleon June 16th, 2005 04:40 PM

Hi Vijay,
Thank you very much for your help. I tried your method and I got this error message: "Microsoft VBScript runtime (0x800A000D) Type mismatch: 'CHARINDEX'"

HERE ARE MY CODES:
strList=Request.Form("fields_of_interest[]")
ArrChecked = split(strList)

For i=lbound(ArrChecked) to ubound(ArrChecked)
    mySQL = mySQL & CHARINDEX(ArrChecked(i), aMajor) > 0
    If i < ubound(ArrChecked) then mySQL = mySQL & " AND "
Next

Any idea of what is causing this? I know that the CHARINDEX function is to find the expressions of the selected text boxes, and look for it in the "AMajor" column. I don't understand where the 'type mismatch' comes from. I searched the Microsoft site, but couldn't find the answer. Please help.
Thank you.
Leon



happygv June 16th, 2005 04:49 PM

Hi Leon,

Looks like either CHARINDEX's return value or param is of other type which doesn't match with that of the comparable value. I would like to see the response.write output of the SQL statement that had resulted from this code too. Just curious to see if that has something fishy.

Also I am not sure if you got to use "[]" in here
Code:

strList=Request.Form("fields_of_interest[]")
Let me know how you are naming the check boxes in the html form. I would suggest you name them all as fields_of_interest, hope you have done so. But while requesting, you dont have to use "[]". Also do a response.write strList to see if the values CHECKED are coming fine. Let me know if that helps. This time if something is wrong you should be getting some other error and not the same.

Cheers

_________________________
- Vijay G
Strive for Perfection

phungleon June 16th, 2005 07:10 PM

hi Vijay,
Thanks for the quick response. I took off the [] and it got rid of the 'CHARINDEX' issue, but now I got the "Incorrect syntax near 'where'" error message. Below is my select stmt:

mySQL="SELECT ApplicantID, firstname, lastname, submitdate, email, fields_of_interest, bilingual_skills FROM internship_form where"

I named my checkboxes "fields_of_interest[]" This is why i had the [] in the previous code. I'll play around with the select stmt, maybe i can figure it out. I am getting off work right now, so I'll update you on this tomorrow.

Thanks again.

Leon


phungleon June 17th, 2005 01:42 PM

Hi Vijay,
I have a good news and a bad. Good news is, I was able to fix and ran the codes without any error messages. The bad news is, it seems like it is NOT querying only the items that I have checked. In other word, the query picks up everything. Below are my codes. Following that are the outputs from RESPONSE.WRITE. (NOTICE there is nothing coming out of the "response.write strList")

<% mySQL="SELECT ApplicantID FROM internship_form where isArchive=0"

strList=Request.Form("fields_of_interest")
ArrChecked = split(strList)

For i=lbound(ArrChecked) to ubound(ArrChecked)
    mySQL = mySQL & CHARINDEX(ArrChecked(i), fields_of_interest) > 0
    If i < ubound(ArrChecked) then mySQL = mySQL & " AND "
Next

Response.write mySQL
Response.write strList

set rstemp=conntemp.execute(mySQL)
do while not rstemp.eof
'This is to write out the searched results
%>


  <tr>
    <td><a href="<%=my_link%>"><%=rstemp("applicantID")%></a></td>
  </tr>

<%

rstemp.movenext
loop
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
End if
%>

HERE ARE THE SQL CODES AND RETURNED RECORDS. ITEMS IN BLUE ARE RETURNED RECORDS. NOTICE THE 'STRLIST' IS NOT THERE.
SELECT ApplicantID, firstname, lastname, submitdate, email, fields_of_interest, bilingual_skills FROM internship_form where isArchive=0
asdfasdfs30312 PhungL103246 PhungL103247 PhungL103248 PhungL103709 PhungL105905 SandersS41318 testT15900 testT20242 testT21626

Any idea why? Could it be I took off the [], and it is not recognizing the feid's name?

Thank you.

Leon



happygv June 17th, 2005 02:49 PM

Hi Leon,

So you mean to say that it is just the bad news, as expected result wasn't seen. ;)

I doubt the way your checkboxes are named. Can you also post the code from the FORM where you use the checkboxes? You should have all the checkboxes named as "fields_of_interest" and requesting that would result in values checked, separated by comma. If you say there is nothing coming from the "strList" then there is something wrong at the first step itself. Got to see more code to find where it goes wrong.

And what gets executed is just this - SELECT ApplicantID FROM internship_form where isArchive=0

If at all there is value in strList then you should atleast get close to the result expected. If you are gonna use an expression in the query before split and for loop, then AND should be there always, and your FOR loop code should look like.
Code:

For i=lbound(ArrChecked) to ubound(ArrChecked)
    mySQL = mySQL & " AND " & CHARINDEX(ArrChecked(i), fields_of_interest) > 0
Next

Hope that helps.

_________________________
- Vijay G
Strive for Perfection

phungleon June 17th, 2005 03:40 PM

Hi Vijay,
I tried this method but got the same exact result as described above:
For i=lbound(ArrChecked) to ubound(ArrChecked)
    mySQL = mySQL & " AND " & CHARINDEX(ArrChecked(i), fields_of_interest) > 0
Next

Below are the codes to my whole page. Note the IP address is fictious.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
</head>
<%
'************************************************* **********
'* Step 1: Display the entry form
'************************************************* **********
If Len(Request.form("FormAction")) = 0 Then
%>

<body topmargin="0" bottommargin="0" rightmargin="0" leftmargin="0" marginwidth="0" marginheight="0">
<table align="center" width="780" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td
  </tr>
  <tr>
    <td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td bgcolor="FF9F00"><img src="/images/spacer.gif" alt="" width="1" height="1"></td>
        </tr>
        <tr>
          <td></td>
        </tr>
        <tr>
          <td bgcolor="FF9F00"><img src="/images/spacer.gif" alt="" width="1" height="1"></td>
        </tr>
        <tr>
          <td bgcolor="003B6F"><img src="/images/spacer.gif" alt="" width="1" height="25"></td>
        </tr>
      </table><table width="100%" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td width="206" valign="top">

<table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td colspan="2" bgcolor="929497"><img src="/images/spacer.gif" alt="" width="1" height="14"></td>
              </tr>
              <tr>
                <td valign="top"><img src="/images/left_top.gif" alt="left top scan lines" width="178" height="27"></td>
                <td rowspan="2" valign="top"><img src="/images/left_top_curve.gif" alt="curve" width="28" height="28"></td>
              </tr>
              <tr>
                <td valign="top"><img src="/images/left_bottom.gif" alt="Left bottom scan lines" width="178" height="45"></td>
              </tr>
            </table> <br></td>
          <td width="574" height="550" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr bgcolor="929497">
                <td colspan="2" valign="top"><img src="/images/spacer.gif" alt="" width="1" height="14"></td>
              </tr>
              <tr>
                <td valign="top"> <table width="100%" border="0" cellspacing="0" cellpadding="0"><tr>
                      <td class="breadcrumbs"><a name="main"></a>
                        <table width="100%" border="0" cellpadding="0" cellspacing="0">
                          <tr>
                            <td height="28"> <table width="41%" border="0" cellpadding="0" cellspacing="0" class="breadcrumbs">
                                <tbody>
                                  <tr>
                                    <td><a class="breadcrumbs" href="/default.asp">Home</a>&nbsp;&nbsp;</td>
                                    <td>&gt;</td>
                                    <td><a class="breadcrumbs" href="/hr.asp">&nbsp;&nbsp;HR&nbsp;&nbsp;</a></td>
                                    <td>&gt;</td>
                                    <td></td>
                                  </tr>
                                </tbody>
                              </table></td>
                            <td height="28" align="right"> <noscript>Javascript "print this page" feature</noscript> </td>
                          </tr>
                        </table></td>
                    </tr>
                    <tr>
                      <td class="header">You Can Make a Difference.. .</td>
                    </tr>
                    <tr>
                      <td><img src="/images/spacer.gif" alt="" width="1" height="10"></td>
                    </tr>
                    <tr>
                      <td class="headerorange">Internship Interest Form </td>
                    </tr>
                    <tr>
                      <td bgcolor="#999999"><p>.</p>
                        </td>
                    </tr>
                    <tr>
                      <td><img src="/images/spacer.gif" width="1" height="10"></td>
                    </tr>
                    <tr>
                      <td><form action="sample_Search.asp" method="POST" name="mainform" id="mainform">
                      <input type=hidden name="FormAction" value="Step2">
                        <table width="100%" border="0">
                        <tr>
                          <td colspan="6" bgcolor="#CCCCCC">&nbsp;</td>
                          </tr>
                        <tr bgcolor="#CCCCCC">
                          <td colspan="6"><span class="style12">Field of Interest </span></td>
                          </tr>
                        <tr>
                          <td colspan="6" valign="middle"><span class="style21"><span class="style33">6</span> Select a maximum of up to 4 academic majors and/or fields of interest:</span> <br>
                            <table width="100%" border="1">
                              <tr>
                                <td><span class="style22">
                                  <input name="fields_of_interest[]" type="checkbox" value="Accounting">
                                  </span></td>
                                <td>Accounting</td>
                                <td><span class="style22">
                                  <input name="fields_of_interest[]" type="checkbox" value="Geography">
                                </span></td>
                                <td>Geography </td>
                                <td><span class="style22">
                                  <input name="fields_of_interest[]" type="checkbox" value="Political Science">
                                </span></td>
                                <td>Political Science </td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Biological Science "></td>
                                <td> Biological Science </td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Gerontology"></td>
                                <td> Gerontology</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Psychology"></td>
                                <td> Psychology</td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Business Administration"></td>
                                <td> Business Administration </td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Graphic Art/Design"></td>
                                <td> Graphic Art/Design </td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Public Administration"></td>
                                <td> Public Administration </td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Communications/Marketing"></td>
                                <td> Communications/Marketing</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Health Science/Health Education"></td>
                                <td> Health Science/Health Education</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Public Policy/Legislation"></td>
                                <td> Public Policy/Legislation</td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Computer/Information"></td>
                                <td> Computer/Information &amp; Technology</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Horticulture/Landscape Architecture"></td>
                                <td> Horticulture/Landscape Architecture</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Public Relations/Affairs"></td>
                                <td> Public Relations/Affairs</td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Earth Science"></td>
                                <td> Earth Science</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Human Resources "></td>
                                <td> Human Resources</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Research &amp; Analysis"></td>
                                <td> Research &amp; Analysis</td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Economics"></td>
                                <td> Economics</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Law/Criminal Justice"></td>
                                <td> Law/Criminal Justice</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Social Ecology"></td>
                                <td> Social Ecology</td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Education"></td>
                                <td> Education</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Library Science"></td>
                                <td> Library Science</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Social Work/Human Services"></td>
                                <td> Social Work/Human Services</td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Environmental Studies"></td>
                                <td> Environmental Studies</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Natural Science"></td>
                                <td> Natural Science</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Sociology/Social Science"></td>
                                <td> Sociology/Social Science</td>
                              </tr>
                              <tr>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Finance"></td>
                                <td> Finance</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Physical Science"></td>
                                <td> Physical Science</td>
                                <td><input name="fields_of_interest[]" type="checkbox" value="Other" onclick="togOpass();"></td>
                                <td><strong> Other</strong></td>
                              </tr>
                            </table></td>
                          </tr>
                        <tr>
                          <td colspan="6">&nbsp;</td>
                        </tr>
                        <tr>
                          <td colspan="3" align="right">&nbsp;</td>

                          <td colspan="3">&nbsp;</td>
                        </tr>
                        <tr>
                          <td colspan="3" align="right"><input name="SubmitForm" type="submit" id="SubmitForm" value="Submit"></td>
                          <td colspan="3"><input type="reset" name="Reset" value="Reset"></td>
                          </tr>
                        <tr>
                          <td width="10%">&nbsp;</td>
                          <td width="28%">&nbsp;</td>
                          <td width="11%">&nbsp;</td>
                          <td width="6%">&nbsp;</td>
                          <td width="9%">&nbsp;</td>
                          <td width="36%">&nbsp;</td>
                        </tr>
                      </table>
                          </form></td>
                    </tr>
                  </table></td>
                <td valign="top"><img src="/images/spacer.gif" alt="" width="6" height="1"></td>
              </tr>
            </table></td>
        </tr>
      </table></td>
  </tr>
  <tr>
    <td bgcolor="003B6F"> </td>
  </tr>
</table>
<%
ElseIf Request.form("FormAction") = "Step2" Then
Set conntemp = Server.CreateObject("ADODB.Connection")


conntemp.Open "Provider=SQLOLEDB.1; Data Source=127.127.127.10; Initial Catalog=internship_form; User Id=admin; Password=admin"

mySQL="SELECT ApplicantID, firstname, lastname, submitdate, email, fields_of_interest, bilingual_skills FROM internship_form where isArchive=0"

strList=Request.Form("fields_of_interest")
ArrChecked = split(strList)

For i=lbound(ArrChecked) to ubound(ArrChecked)
    mySQL = mySQL & "AND" & CHARINDEX(ArrChecked(i), fields_of_interest) > 0
    'If i < ubound(ArrChecked) then mySQL = mySQL & " AND "
Next
Response.write mySQL
Response.write strList
set rstemp=conntemp.execute(mySQL)
do while not rstemp.eof
%>


  <tr>
    <td><a href="<%=my_link%>"><%=rstemp("applicantID")%></a></td>
  </tr>


<%

rstemp.movenext
loop
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
End if
%>

</body>
</html>




happygv June 17th, 2005 06:46 PM

In all the <Input> tags that are used for CHECKBOXES, Remove the "[]"
Code:

<input name="fields_of_interest" type="checkbox"  value="Biological Science ">
And while requesting
Code:

strList = Request.Form("fields_of_interest")
Response.write strList 'should show is as - "CHECK1, CHECK4, CHECK8, CHECK9, CHECK20", that were checked

If you are through with this, then You shouldn't be facing any problems later.

Hope that helps.
Cheers!



_________________________
- Vijay G
Strive for Perfection

phungleon June 20th, 2005 06:31 PM

Hi Vijay,
I am so sorry to have to bother you again. I did everything according to your advice. I have removed all the []. But the problem is, we are now back to error one: "Type mismatch: 'CHARINDEX'" I don't understand this.




All times are GMT -4. The time now is 05:48 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.