Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 15th, 2005, 03:34 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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



Reply With Quote
  #2 (permalink)  
Old June 15th, 2005, 06:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old June 16th, 2005, 04:40 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old June 16th, 2005, 04:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old June 16th, 2005, 07:10 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #6 (permalink)  
Old June 17th, 2005, 01:42 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #7 (permalink)  
Old June 17th, 2005, 02:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #8 (permalink)  
Old June 17th, 2005, 03:40 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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>



Reply With Quote
  #9 (permalink)  
Old June 17th, 2005, 06:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #10 (permalink)  
Old June 20th, 2005, 06:31 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Best Searching Criteria Muhammad Zeeshan SQL Server 2000 1 September 30th, 2007 07:14 AM
2 LINK CRITERIA Vision G Access 1 July 14th, 2006 10:33 AM
selection criteria... dbrook007 Other Programming Languages 0 February 21st, 2006 04:41 PM
Criteria in Query lryckman Access 1 June 23rd, 2004 11:11 AM
what is the criteria to get one record yylee Access 1 April 29th, 2004 04:19 PM



All times are GMT -4. The time now is 04:57 AM.


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