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.



happygv June 21st, 2005 01:18 AM

No problem, you please post me the output of "strList" then lets decide how it goes from there on. Since I have not seen the data that comes along with it in the first place, I am not quite sure to reply on what goes wrong with it. Keep using Response.write in every step to see how it progresses and that is how we can trace where we missing things. Let me see what comes in strList first and will take from there.

_________________________
- Vijay G
Strive for Perfection

phungleon June 21st, 2005 10:36 AM

hi Vijay,
This is one of the problem. After I removed the [], i get the error "Type mismatch: 'CHARINDEX'" message BEFORE the page get a chance to run the 'Response.write strList' statement. Therefore, I can't tell you what the output is. However, in the error message, I was able to see the POST Data: # FormAction=Step2&fields_of_interest=Accounting&fie lds_of_interest=Biological+Science+&SubmitForm=Sub mit This is the result from me checking "Accounting" and "Biological Science."

Now if I kept the [], it seems like the codes ran find, but there is nothing coming out from the 'Response.write strList.' In other words, the output of strList is blank. I mentioned about this on my second post from above.

Does this help?

Thanks again.
Leon



happygv June 22nd, 2005 08:21 AM

Hi Leon,

The typemismatch happens, because the "fields_of_interest" used in CHARINDEX is an array, where the second parameter of CHARINDEX should be a string type. I somehow missed that above, as I thought you would have been referring to your tables's columnname AMajor, as it is said in your first post. This FOR ...NEXT part is to construct the SQL statement for you to find the relevant result that would be executed in the pater part of the code. So I don't think it should be "fields_of_interest" there instead should be "AMajor". Hope a relook on my first reply would also help on this.

Sorry about losing my focus.
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

phungleon June 22nd, 2005 10:51 AM

Hi Vijay,

Thank you! It is not your fault at all. The column's name is 'Fields_of_Interest." I used AMajor at the beginning for example purpose only. I understand what your codes do, but I just can't figure out why I get the error message. I meant, I know my inputs are string type, and I have double and triple checked to make sure I got column name and table name correctly. Anyway, I will go back to check again and test out with some stuff. Thank you VERY much for all your help. I'll let you know if I find anything.

Leon


happygv June 23rd, 2005 05:07 AM

Hi Leon,

It was my bad, for having not checked the code at my end and posted here. It should have saved a lot of your precious time, had I tested it first at my end and have posted here. The "Type Mismatch" error with CharIndex is due to "not enclosing that part in Quotes". Here is the corrected code of that line
Code:

strsql = strsql & "CHARINDEX(" & ArrChecked(i) & ", AMajor) > 0 "
I am terribly sorry about that. Hope this doesn't trouble anymore.

Cheers!

_________________________
- Vijay G
Strive for Perfection

phungleon June 23rd, 2005 10:23 AM

Hi Vijay,
Like always, I appreciate very much for all your helps and taking your time to answer my questions. I don't think i am made for programming. I feel like there are so much that I don't know about programming.

Anyway, I added in the quotes, ran the script, but got the "Execution of a full-text operation failed. A clause of the query contained only ignored words" error message. I have no idea what this mean. The good news is, I was able to find an alternative to accomplish the task. If you don't mind, i would like to share it with you. Here are the codes:


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
strList=trim(Request.Form("fields_of_interest"))
strList = Replace(strList, ",", "' Or '")
strList = "'" & strList & "'"
strList = Replace(strList, "'", Chr(34))
mySQL = mySQL & " And Contains(fields_of_interest, '" & strList & "')"


I replaced the For.....Next loop statement with it. Once again, thank you VERY much for your help.
Until next time....take care! :-)
Leon


happygv June 24th, 2005 04:29 AM

Hi Leon,

Is that column a TEXT datatype? CONTAINS() can be used for search operations on a TEXT datatype column and not on VARCHAR()/CHAR() datatypes, thats why I did suggest CHARINDEX(). I wasn't aware of that being a TEXT datatype, until I saw the error message that you posted, and info on usage of CONTAINS().

Also I am not sure if OR between the items that are checked would yield the correct results for you. Is that not an AND kind of operation in your case? If that worked, thats really great.

Cheers!

_________________________
- Vijay G
Strive for Perfection

phungleon June 27th, 2005 04:04 PM

Hi Vijay,
Thanks for the folllow-up. Good point on the CONTAIN() function issue...my column is a VARCHAR() datatype, but for some reason it works. However, I had to initiate a "Full-text indexing" in SQL 2000, on that column in order for the CONTAIN() function to work. Also, I understand there are some issue when using full-text indexing; for example, it affects performance b/c indexes are stored and managed external to SQL Server. Second, which I am very concern of is that the indexing doesn't get updated when I delete or add records to the database?

If the above issues are true, than I really rather go with your method of the FOR.....NEXT loop, b/c there are new records constantly being added to the table. Howver, when I ran your script, I got the "Execution of a full-text operation failed. A clause of the query contained only ignored words" error message. Any idea of what this mean? I searched the web and MS site for answers, but they did not mention a solution for it, except something about MS Hotfix? What can I do to make your solution work?

The "OR" works better, b/c I am looking for anyone who has selected anything that I have "checked" to search for. Thanks for noticing that!

Leon



phungleon June 29th, 2005 02:29 PM

Problem solved!!! :-) I figured out what I needed to do for the auto index update to occure. It has to do with setting the indexing to "Change Tracking" with the "Update index in Background" option in SQL 2000. Thank VERY much Vijay for your help! And those who took your time to read my topic.

Leon



All times are GMT -4. The time now is 02:18 PM.

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