Advanced Search

[yimma216]

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]

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]

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

[yimma216]

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.

[yimma216]

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]

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~

[yimma216]

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?

<h1>Infosphere</h1>
<h3>Subsciption Options</h3>
<%
Set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
set rs2 = Server.CreateObject("ADODB.Recordset")
conn.Open  "Driver={SQL Native Client};Server=cslproxy;database=customer_management;Trusted_Connection=yes;"


if request("Submit")<>"" then
	 'response.write request("contact") & " <br/>"
	 store = request("check_list")	 
	 selection = split(store, ",")
	 
	 For i = 0 to Ubound(selection)
	 		 response.write("selection " & i & ": " & selection(i) & "<br />")
			 sql="Insert into contact_options (contact_uid,option_uid,value) values (" & request("contact") & "," & selection(i) & ",1)"  			 
	 		 conn.execute sql
	 Next

	 response.write "Insert Successful."	
	 response.redirect "showcompany.asp?contact=" &  request("contact")
else
'select all the data
sql = "select * from contact_options c inner join options o on c.option_uid=o.uid where contact_uid=" & request("contact") 
rs2.open sql, conn 

dim a(6)
i=0
Do while not rs2.eof
	 a(i)= rs2("option_uid")
	 'response.write rs2("option_uid") & "<br />"
 	 rs2.movenext
	 i= i +1
loop
rs2.close

'select options
sql = "select * from options"
rs.open sql, conn
%>
<form name="myform" action='subscription.asp' method='post'>
<input type='hidden' name='contact' value='<%=request("contact")%>' />
<table>
<%
 b = 0
 Do while not rs.eof 
		 if rs("uid")<>a(b) then	 		 		
		 		 response.write "<tr><td><input type='checkbox' name='check_list' value='" & rs("uid") & "' ></td>"
		 		 response.write "<td><b>" & rs("option_name") & "</b></td></tr>"
		 		 b = b + 1
		 else 
		 		 response.write "<tr><td><input type='checkbox' name='check_list' value='" & rs("uid") & "' disabled></td>"
		 		 response.write "<td><b>" & rs("option_name") & "</b></td></tr>"
		 		 b = b + 1				 		 
		 End if		 		 
		 rs.movenext
	loop
%>
<!--	<tr><td>
			<input type="checkbox" name="Check_ctr" value="yes" onClick="Check(document.myform.check_list)">
	</td><td><b><i>Check All</i></b></td></tr>-->
	<tr><td>&nbsp;</td><td>	
			<input type="Submit" name="Submit" value="Submit">
	</td></tr>	
</table>
</form>
<%
rs.close

End if 

conn.close
%>

<hr />
<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>

</td></tr></table>

[CroCrew]

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.a sp” then can you post the code for that page too? Because that is where it looks like the update is done.

CroCrew~

[gcube]

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:

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:

If Request.Form("Area") <> "" Then

'   * Set and open your database connection (conn and rs) *

    vtemp = split(Request.Form("Area"),",")
    For i = 0 to UBound(vtemp)
        sql = "SELECT * FROM tblContact WHERE Area = '"& vtemp(i) &"' ;"
        rs.Open sql, conn, 1, 3
'           * print html code to format vtemp(i) as your heading for the list

            If rs.RecordCount = 0 Then
'               * print something indicating that there are no contacts in this area

            Else
                Do While Not rs.EOF
        
 '                   * print rs.Fields("fields you want to display for the contact")

                    rs.MoveNext
                Loop
            End If
        rs.Close

'        * print whatever separation you want between the lists

    Next

'    * close your database connection

End If

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.

[yimma216]

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.

 set rs3=Server.CreateObject("ADODB.Recordset")
sql = "select * from contact_options c inner join options o on c.option_uid=o.uid where contact_uid=" & request("contact") 
rs3.open sql, conn 
i=1
response.write "<table>"
response.write "<tr><th align='left'>Subscription List</th><td><a href='subscription.asp?contact=" & request("contact" )& "'>Edit</a></td></tr>"
do while not rs3.eof 
   response.write vbcrlf & "<tr><td>" & i & ". " & rs3("option_name") & "</td>"
   response.write vbcrlf & "<td></td></tr>"			 	 
   rs3.movenext
   i = i + 1
   loop
   response.write "</table>"
rs3.close
end if

Then the edit page will pass through the contact id to the subscription.as p.
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_option s 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.

'select all the data
sql = "select * from contact_options c inner join options o on c.option_uid=o.uid where contact_uid=" & request("contact") 
rs2.open sql, conn 

dim a(6)
i=0
Do while not rs2.eof
	 a(i)= rs2("option_uid")
	 'response.write rs2("option_uid") & "<br />"
 	 rs2.movenext
	 i= i +1
loop
rs2.close

'select options
sql = "select * from options"
rs.open sql, conn
%>
<form name="myform" action='subscription.asp' method='post'>
<input type='hidden' name='contact' value='<%=request("contact")%>' />
<table>
<%
 b = 0
 Do while not rs.eof 
		 if rs("uid")<>a(b) then	 		 		
		 		 response.write "<tr><td><input type='checkbox' name='check_list' value='" & rs("uid") & "' ></td>"
		 		 response.write "<td><b>" & rs("option_name") & "</b></td></tr>"
		 		 b = b + 1
		 else 
		 		 response.write "<tr><td><input type='checkbox' name='check_list' value='" & rs("uid") & "' disabled></td>"
		 		 response.write "<td><b>" & rs("option_name") & "</b></td></tr>"
		 		 b = b + 1				 		 
		 End if		 		 
		 rs.movenext
	loop
%>
<!--	<tr><td>
			<input type="checkbox" name="Check_ctr" value="yes" onClick="Check(document.myform.check_list)">
	</td><td><b><i>Check All</i></b></td></tr>-->
	<tr><td>&nbsp;</td><td>	
			<input type="Submit" name="Submit" value="Submit">
	</td></tr>	
</table>
</form>
<%
rs.close

Will be very appreciated if I could spare some smartness here to solve the problem. Thanks :)