Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > asp classic > answers

Advanced Search

Newbie
  
Join Date: Jul 2008
Posts: 30
#1: Oct 14 '09
I have these big long list of contacts.
I want to make an advanced search. How shall I start?

There is a basic search with Company, branch and contact name.

CroCrew's Avatar
Expert
  
Join Date: Jan 2008
Location: Michigan
Posts: 338
#2: Oct 15 '09

re: Advanced Search

Hello yimma216,

I would start by putting all the contacts in a database (I prefer MS-SQL) then I would mock up how I would like the screens to look; then start coding. After you have started coding and if you run into a problem, please post any question that you might have and we will be happy to help you out.

All the best,
CroCrew~
Frinavale's Avatar
Site Moderator
  
Join Date: Oct 2006
Location: The Great White North
Posts: 5,137
#3: Oct 15 '09

re: Advanced Search

I think what CroCrew is getting at is....think about what your Advanced Search is supposed to do before you try to implement it ;)

-Frinny
Newbie
  
Join Date: Jul 2008
Posts: 30
#4: Oct 15 '09

re: Advanced Search

Thanks guys. I have set up a webpage to how it looks. The database is MSSQL.

I am working on the MSSQL and hopefully can run a query search to display all of the details.

We have this check boxes, about regions Northern, Southern and Central.

When User select a check box, it will display the companies by regions.

I am thinking may be an if-statement with the vbscript can help me display northern, southern and central regions.
Newbie
  
Join Date: Jul 2008
Posts: 30
#5: Oct 16 '09

re: Advanced Search

I tried to get the values from the form, request("check_list").
Then do different things according to the value from the request("check_list").

However, how do u get the individual value out of the request("check_list")??
i.e. request("check_list")=1, 2, 3,
CroCrew's Avatar
Expert
  
Join Date: Jan 2008
Location: Michigan
Posts: 338
#6: Oct 16 '09

re: Advanced Search

Hello yimma216,

So, I/we understand the situation better could you please post the code for the page(s)?

Please before pasting your code for each page make sure you wrap the code of each page inside [code] tags.

Thanks,
CroCrew~
Newbie
  
Join Date: Jul 2008
Posts: 30
#7: Oct 20 '09

re: Advanced Search

Thanks CoCrew. I figured that I could use a spit function to get individual value.

But I have a new question to fix. I created a check box control which did not disable the list items that already exist in the database. Some of them worked as I want, some of them do not.

May be you can help me to figure that out?

Expand|Select|Wrap|Line Numbers
  1. <h1>Infosphere</h1>
  2. <h3>Subsciption Options</h3>
  3. <%
  4. Set conn = Server.CreateObject("ADODB.Connection")
  5. set rs = Server.CreateObject("ADODB.Recordset")
  6. set rs2 = Server.CreateObject("ADODB.Recordset")
  7. conn.Open  "Driver={SQL Native Client};Server=cslproxy;database=customer_management;Trusted_Connection=yes;"
  8.  
  9.  
  10. if request("Submit")<>"" then
  11.      'response.write request("contact") & " <br/>"
  12.      store = request("check_list")     
  13.      selection = split(store, ",")
  14.  
  15.      For i = 0 to Ubound(selection)
  16.               response.write("selection " & i & ": " & selection(i) & "<br />")
  17.              sql="Insert into contact_options (contact_uid,option_uid,value) values (" & request("contact") & "," & selection(i) & ",1)"               
  18.               conn.execute sql
  19.      Next
  20.  
  21.      response.write "Insert Successful."    
  22.      response.redirect "showcompany.asp?contact=" &  request("contact")
  23. else
  24. 'select all the data
  25. sql = "select * from contact_options c inner join options o on c.option_uid=o.uid where contact_uid=" & request("contact") 
  26. rs2.open sql, conn 
  27.  
  28. dim a(6)
  29. i=0
  30. Do while not rs2.eof
  31.      a(i)= rs2("option_uid")
  32.      'response.write rs2("option_uid") & "<br />"
  33.       rs2.movenext
  34.      i= i +1
  35. loop
  36. rs2.close
  37.  
  38. 'select options
  39. sql = "select * from options"
  40. rs.open sql, conn
  41. %>
  42. <form name="myform" action='subscription.asp' method='post'>
  43. <input type='hidden' name='contact' value='<%=request("contact")%>' />
  44. <table>
  45. <%
  46.  b = 0
  47.  Do while not rs.eof 
  48.          if rs("uid")<>a(b) then                      
  49.                   response.write "<tr><td><input type='checkbox' name='check_list' value='" & rs("uid") & "' ></td>"
  50.                   response.write "<td><b>" & rs("option_name") & "</b></td></tr>"
  51.                   b = b + 1
  52.          else 
  53.                   response.write "<tr><td><input type='checkbox' name='check_list' value='" & rs("uid") & "' disabled></td>"
  54.                   response.write "<td><b>" & rs("option_name") & "</b></td></tr>"
  55.                   b = b + 1                          
  56.          End if                  
  57.          rs.movenext
  58.     loop
  59. %>
  60. <!--    <tr><td>
  61.             <input type="checkbox" name="Check_ctr" value="yes" onClick="Check(document.myform.check_list)">
  62.     </td><td><b><i>Check All</i></b></td></tr>-->
  63.     <tr><td>&nbsp;</td><td>    
  64.             <input type="Submit" name="Submit" value="Submit">
  65.     </td></tr>    
  66. </table>
  67. </form>
  68. <%
  69. rs.close
  70.  
  71. End if 
  72.  
  73. conn.close
  74. %>
  75.  
  76. <hr />
  77. <p><a href='default.asp'>CRM Home Page</a>&nbsp;<a href='../'>Infosphere Home Page</a>&nbsp;<a href="newRecord.asp">New Record</a></p>
  78.  
  79. </td></tr></table>
  80.  
CroCrew's Avatar
Expert
  
Join Date: Jan 2008
Location: Michigan
Posts: 338
#8: Oct 20 '09

re: Advanced Search

Hello yimma216,

I/we would be glad to help. But, could you provide us with more information in what you are trying to do.

What is the name of the page that you posted above? If it is not “subscription.asp” then can you post the code for that page too? Because that is where it looks like the update is done.

CroCrew~
Newbie
  
Join Date: Oct 2009
Location: Grant, AL
Posts: 17
#9: Oct 20 '09

re: Advanced Search

Unless I'm missing something, the question sounds like the solution should be much simpler. I am assuming that the form portion of your code is to display the form and that you are using the same file to process the form submittals. This leads to a display question:

Quote:
The first time the page is called, it will display only the form. When the form is submitted, the processing will be performed and the list will be displayed, followed by the form so that it can be re-submitted. Is this your intent?
I am going to only address the question of processing the form submittal and leave the page display to you. I will assume that you have a table with the data for the contacts (I'm going to call it "tblContact").

tblContact has only 1 field of interest to me for processing - I'll call it "Area". I expect tblContact to also have fields for information about the contact (i.e., name) that you'll use for display.

Your form has a field named "Area", which returns a comma delimited list. Here's your basic processing:

Expand|Select|Wrap|Line Numbers
  1. If Request.Form("Area") <> "" Then
  2.  
  3. '   * Set and open your database connection (conn and rs) *
  4.  
  5.     vtemp = split(Request.Form("Area"),",")
  6.     For i = 0 to UBound(vtemp)
  7.         sql = "SELECT * FROM tblContact WHERE Area = '"& vtemp(i) &"' ;"
  8.         rs.Open sql, conn, 1, 3
  9. '           * print html code to format vtemp(i) as your heading for the list
  10.  
  11.             If rs.RecordCount = 0 Then
  12. '               * print something indicating that there are no contacts in this area
  13.  
  14.             Else
  15.                 Do While Not rs.EOF
  16.  
  17.  '                   * print rs.Fields("fields you want to display for the contact")
  18.  
  19.                     rs.MoveNext
  20.                 Loop
  21.             End If
  22.         rs.Close
  23.  
  24. '        * print whatever separation you want between the lists
  25.  
  26.     Next
  27.  
  28. '    * close your database connection
  29.  
  30. End If
  31.  
I expect that your database has other tables - including a list of possible Area values which you'll use to produce your form option selection list, but you don't even need that - you could do it by selecting distinct Area from tblContact - that way, you won't ever have an area that is without any contact records.
Newbie
  
Join Date: Jul 2008
Posts: 30
#10: 3 Weeks Ago

re: Advanced Search

Hi cube, thanks for the advice. I just have time to try out the code today.

But I think I am more clear what the problem I have now.

Let me explain, I do have two pages as croCrew said.

The first page has a display of a subscription list.
Expand|Select|Wrap|Line Numbers
  1.  set rs3=Server.CreateObject("ADODB.Recordset")
  2. sql = "select * from contact_options c inner join options o on c.option_uid=o.uid where contact_uid=" & request("contact") 
  3. rs3.open sql, conn 
  4. i=1
  5. response.write "<table>"
  6. response.write "<tr><th align='left'>Subscription List</th><td><a href='subscription.asp?contact=" & request("contact" )& "'>Edit</a></td></tr>"
  7. do while not rs3.eof 
  8.    response.write vbcrlf & "<tr><td>" & i & ". " & rs3("option_name") & "</td>"
  9.    response.write vbcrlf & "<td></td></tr>"                  
  10.    rs3.movenext
  11.    i = i + 1
  12.    loop
  13.    response.write "</table>"
  14. rs3.close
  15. end if
Then the edit page will pass through the contact id to the subscription.asp.
Contact id then will look up the contact_options to find which subscription this contact has.

The subscription page then shall display all the subscription options (from the options table) except for those are in the database (contact_options table) already. This is my target.

On this page, I tried to store the existing option uid in an array and compare to the rest of the available subscription options according to their uid.

But if the array number stores only 1 value which is 3, the html will display all the options again start from 1 to 6. It does not make the option 3 disappeared.

So, I suspect some where in my loop condition is not quite rite in the second part of the if statement; which is the following.
Expand|Select|Wrap|Line Numbers
  1. 'select all the data
  2. sql = "select * from contact_options c inner join options o on c.option_uid=o.uid where contact_uid=" & request("contact") 
  3. rs2.open sql, conn 
  4.  
  5. dim a(6)
  6. i=0
  7. Do while not rs2.eof
  8.      a(i)= rs2("option_uid")
  9.      'response.write rs2("option_uid") & "<br />"
  10.       rs2.movenext
  11.      i= i +1
  12. loop
  13. rs2.close
  14.  
  15. 'select options
  16. sql = "select * from options"
  17. rs.open sql, conn
  18. %>
  19. <form name="myform" action='subscription.asp' method='post'>
  20. <input type='hidden' name='contact' value='<%=request("contact")%>' />
  21. <table>
  22. <%
  23.  b = 0
  24.  Do while not rs.eof 
  25.          if rs("uid")<>a(b) then                      
  26.                   response.write "<tr><td><input type='checkbox' name='check_list' value='" & rs("uid") & "' ></td>"
  27.                   response.write "<td><b>" & rs("option_name") & "</b></td></tr>"
  28.                   b = b + 1
  29.          else 
  30.                   response.write "<tr><td><input type='checkbox' name='check_list' value='" & rs("uid") & "' disabled></td>"
  31.                   response.write "<td><b>" & rs("option_name") & "</b></td></tr>"
  32.                   b = b + 1                          
  33.          End if                  
  34.          rs.movenext
  35.     loop
  36. %>
  37. <!--    <tr><td>
  38.             <input type="checkbox" name="Check_ctr" value="yes" onClick="Check(document.myform.check_list)">
  39.     </td><td><b><i>Check All</i></b></td></tr>-->
  40.     <tr><td>&nbsp;</td><td>    
  41.             <input type="Submit" name="Submit" value="Submit">
  42.     </td></tr>    
  43. </table>
  44. </form>
  45. <%
  46. rs.close
  47.  
  48.  
Will be very appreciated if I could spare some smartness here to solve the problem. Thanks :)
Reply

« previous question | next question »

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,510 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved