Merging two searching pages together

mrong
mrong used Ask the Experts™
on
Greeting,
I have two searching pages in asp. They are searching by checkbox selections. Now I want to merge those two pages together. Below are the 2 sql I used. Questions is how to merge those 2 sql together. Please put in consideration as the user might not select checkbox on either pages.

strSQL1 ="SELECT DISTINCT table1.* FROM table1 LEFT JOIN Specialty ON table1.FirmID = Specialty.FirmID where Specialty_Master.Specialty_details in ('" & strResult & "') order by table1.FirmName;"

strSQL2 ="SELECT DISTINCT table1 FROM table1 LEFT JOIN Discipline_detail ON table1.FirmID = Discipline_detail.FirmID WHERE (((" & Architecture & "=False) OR Discipline_detail.[Architecture]=" & Architecture & ") AND ((Discipline_detail.[Civil/Site_Engineering]=" & Civil_Site_Engineer & ") or (" & Civil_Site_Engineer & "=False)) ) ORDER BY table1.FirmName;"

Thanks in advance.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
Are these 2 queries using the same checkboxes on your markup page?

Also, you said to put in consideration that the user might not select a checkbox.

If the user does not select a checkbox, what do you want to see happen?

Right now, your first query will return ALL records with the WHERE IN(...) if no checkbox is selected.

That's not going to be the case with the second query.

Author

Commented:
sammySeltzer:
2 queries are using different checkboxes since the checkbox values are from different tables.

Thank you for your note. I didn't realize the 1st query will return all the records if nothing was selected. That needs to be fixed.

what needs to be done is: if the user selects checkbox for query1 then run query 1, if the user selects checkbox for query2 then run query2. If selects both then run both. If nothing is selected, then should display a message say nothing has been seleted.

thanks.
Top Expert 2011

Commented:
Right now, your first query will return ALL records with the WHERE IN(...) if no checkbox is selected.

No, I take that back. That's not the case as it is not performing a keyword search.

Be back a little later.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Top Expert 2011

Commented:
Sorry, have been tied up all day.

Are the markups (for the checkboxes) one same page?

I would hope so.

If not, why bother with displaying data based on  selection?

Can you post them here, perhpas as an attachment so I can use them to test rather than creating one from scratch?

Author

Commented:
sammySeltzer,
I will post them this morning. also posted another question at the link below.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_27245835.html
thanks.

Author

Commented:
Attached is my checkbox.asp. I tried to merge two checkbox pages together but got error message below.

ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/firmsurvey/MasterDSReport.asp, line 218
 

checkbox.txt
Top Expert 2011

Commented:
So, are the 2 documents merged here?

If they are separate, and they each point to the page that contains this:
strSQL1 ="SELECT DISTINCT table1.* FROM table1 LEFT JOIN Specialty ON table1.FirmID = Specialty.FirmID where Specialty_Master.Specialty_details in ('" & strResult & "') order by table1.FirmName;"

strSQL2 ="SELECT DISTINCT table1 FROM table1 LEFT JOIN Discipline_detail ON table1.FirmID = Discipline_detail.FirmID WHERE (((" & Architecture & "=False) OR Discipline_detail.[Architecture]=" & Architecture & ") AND ((Discipline_detail.[Civil/Site_Engineering]=" & Civil_Site_Engineer & ") or (" & Civil_Site_Engineer & "=False)) ) ORDER BY table1.FirmName;"


Then we don't need to do anything with this query at all.

All you would need to do is at the top, you define the form fields and where they are coming from like:

Request(...)
request(...)


Then your query would pick them up based on what is passed to it in the where clause.

Another option, a better option, I think would be to enter this at the top, just below </head> on each of of the 2 markup pages since you said they are separate like this:

<% Session("THISPAGE") = "Page1" %> replace page1 with whatever your page name is
Then do it for the other page.

For instance, I would put it here on the page you attached and I am going to call that page MasterDSReport.

S, just a snapshot of it would be:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang="en">

<head>
<!-- THIS CODE PROVIDES THE FORMATTING FOR THE TEXT - PLEASE LEAVE INTACT -->
<link rel="stylesheet" href="css/nbmain.css" type="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
.style1 {
	font-size: medium;
}
</style>
</head>
<% Session("THISPAGE") = "MasterDSReport" %> 

<body bgcolor="#ffffff" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
then rest of your code follow

Open in new window


Now, on the page with the 2 queries, I would simply do this:

If Session("THISPAGE") = "MasterDSReport" and strResult <>Then
run the report for that page
else
response.write "Nothing was selected. Please try again"
response.redirect "MasterDSReport.asp"
end if

We can handle this more gracefully but this is just a start.

First, I need to know, are the forms on the page you attached?

Author

Commented:
sammySeltzer:
I might not made it clear before. I hav2 searching pages and let's call them search1.asp & search2.asp. Each will be submitted to result1.asp & result2.asp. Now I want to merge search1.asp & search2.asp to one single search.asp and it is submitted to one result.asp.
search.asp contains all the checkboxes in seach1.asp & search2.asp
result.asp contains the combined sql from result1.asp & result2.asp
The user might select the checkboxed(from search1.asp or search2.asp or both) in search.asp.
Top Expert 2011

Commented:
Ok, much clearer.

Sorry, it is taking longer. This happens to be crunch time for us with a seriies of meetings ( i hate meetings).

Now, have you already merged search1.asp and search2.asp into one search.asp file?

If yes, is it the file you attached to me?

If yes, have the bugs you reported earlier been fixed?

Merging result1.asp and result2.asp into one result.asp won't be difficult, I don't think.

Author

Commented:
sammySeltzer:
Thank you for your help and it means a lot.
Yes, the page I attached is the merged page. There is bugs in there and I didn't get a chance to fix it today.
Top Expert 2011

Commented:
Ok, I will work on that now first and proceed from there.

thanks

That error, by the way, means you are missing either recordset or connection string like set conn= server.createobject ...

Be back shortly...
Top Expert 2011

Commented:
Here is the fixed up version of the markup.

You can play with formatting.

As stated earlier, your issue was simply not setting your connection right.

You needed something like:

set objconn = server.createObject("adodb.connection")

Then somewhere down the line, you messed up your recordset.

Instead of rsItems2, you had rsItems1.

Anywhay, just change connection to your DB and this should be fine.

I am about to leave, sorry but I will work on the other bit later this evening.


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang="en">

<head>
<!-- THIS CODE PROVIDES THE FORMATTING FOR THE TEXT - PLEASE LEAVE INTACT -->
<link rel="stylesheet" href="css/nbmain.css" type="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
.style1 {
	font-size: medium;
}
</style>
<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
var checkflag = "false";
function check(field) {
if (checkflag == "false") {
for (i = 0; i < field.length; i++) {
field[i].checked = true;}
checkflag = "true";
return "Uncheck All"; }
else {
for (i = 0; i < field.length; i++) {
field[i].checked = false; }
checkflag = "false";
return "Check All"; }
}
//  End -->
</script>

</head>


<body bgcolor="#ffffff" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">

               <FORM action="FacilitiesDiscipline.asp"  NAME="frmUser" METHOD="POST" target="new" >
                     <table align="center">
                      <tr>
                       <td>
						<table width="65%">
							<tr>
								<td>&nbsp;</td>
								<td colspan="3"><span class="style1">Please select below to
						view the report</span></td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								 <td>
              &nbsp;</td>
								 <td style="width: 346px">
              <p><input type="checkbox" name="Architecture" value="1" onfocus="nextfield ='Laboratory_Planning'" >Architecture</td>
								<td style="width: 99px">&nbsp;</td>
								 <td style="width: 205px">
              <p><input type="checkbox" name="Laboratory_Planning" value="1" onfocus="nextfield ='Civil_Site_Engineer'" >Laboratory_Planning</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td style="height: 25px;">&nbsp;</td>
								<td style="width: 346px; height: 25px;">
								<input type="checkbox" name="Civil_Site_Engineer" value="1" onfocus="nextfield ='Landscape_Architecture'" >Civil/Site_Engineer</td>
								<td style="width: 99px; height: 25px;"></td>
								<td style="width: 205px">
								<input type="checkbox" name="Landscape_Architecture" value="1" onfocus="nextfield ='Design/Build'" >Landscape_Architecture</td>
								<td style="width: 128px; height: 25px;"></td>
								<td style="height: 25px"></td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Design_Build" value="1" onfocus="nextfield ='Marine_Engineering'" >Design/Build</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Marine_Engineering" value="1" onfocus="nextfield ='Electrical_Engineering'" >Marine_Engineering</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Electrical_Engineering" value="1" onfocus="nextfield ='Mechanical_Engineering'" >Electrical_Engineering</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Mechanical_Engineering" value="1" onfocus="nextfield ='Fire_Protection'" >Mechanical_Engineering</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Fire_Protection" value="1" onfocus="nextfield ='Planning'" >Fire_Protection</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Planning" value="1" onfocus="nextfield ='Forensic_Engineering'" >Planning</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Forensic_Engineering" value="1" onfocus="nextfield ='Plumbing_Engineering'" >Forensic_Engineering</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Plumbing_Engineering" value="1" onfocus="nextfield ='Geotechnical'" >Plumbing_Engineering</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Geotechnical" value="1" onfocus="nextfield ='Roofing_Consultant'" >Geotechnical</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Roofing_Consultant" value="1" onfocus="nextfield ='Interior_Design'" >Roofing_Consultant</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Interior_Design" value="1" onfocus="nextfield ='Structural_Engineering'" >Interior_Design</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Structural_Engineering" value="1" onfocus="nextfield ='Surveying'" >Structural_Engineering</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Surveying" value="1" onfocus="nextfield ='done'" >Surveying</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>

							</tr>
                  </table>
                  <hr width="70%" align="left" color="gainsboro">
	  <%

    Dim rsItems2
   Set rsItems2 = Server.CreateObject("ADODB.Recordset")
   set objConn2 = server.CreateObject("ADODB.Connection")
   set objRS =server.CreateObject("ADODB.Recordset")
   objConn2.ConnectionString= "Provider=microsoft.jet.oledb.4.0;Data source=" & server.mappath("YourDB.mdb")
   objRs.CursorLocation = 3
   objConn2.Open

  
	strSQL2 = "Select distinct Specialty_Details from Specialty_Detail where Specialty_Details is not null order by Specialty_Details;"
     rsItems2.Open strSQL2, objConn2

	'Display the FORM and the top of the TABLE
	'Response.Write "<FORM METHOD=POST ACTION=""MasterSpecialty.asp"" target=""_new"">"
	Response.Write "<input type='checkbox' name='checkall'  onClick='this.value=check(this.form.result)'>check all/uncheck all"



	Response.Write "<TABLE border='1' cellpadding='0' cellspacing='4' style='border-collapse: collapse' bordercolor='#111111' width='70%'>"
	Dim iLoop
	Response.Write "<TR>"
	Response.Write "<TH></TH>"
	Response.Write "<TH>Specialty</TH>"

	Response.Write "</TR>"

	'Display each element in the table..
	Do While Not rsItems2.EOF


		Response.Write "<TR>" & vbCrLf

		'Create a checkbox to check for searching records, setting the checkboxes
		'Value equal to the current items
		'Response.Write "<TD><INPUT TYPE=CHECKBOX NAME='Result' "
		'Response.Write "VALUE=" & rsItems2("Specialty_Details")& "></TD>"

          %>
            <TD><INPUT TYPE="CHECKBOX" NAME="result" VALUE="<%=rsItems2("Specialty_Details")%>"></TD>
            <%


		'Display the number and name of the record
		Response.Write vbCrLf & "<TD>" & rsItems2("Specialty_Details") & "</TD>"

		Response.Write "</TR>" & vbCrLf & vbCrLf

		'Move to the next record...
		rsItems2.MoveNext
	Loop



	'Clean up our ADO objects
	rsItems2.Close
	Set rsItems2 = Nothing

	objConn2.Close
	Set objConn2 = Nothing            %>

		<tr>
		<td style="width: 346px">
		<INPUT TYPE="Submit" VALUE="Submit ">
        <INPUT TYPE="RESET"></td>
         </tr>
	   </table>
      </td>
     </tr>
    </table>

    </form>

</BODY>

Open in new window

Top Expert 2011

Commented:
I just have one suggestion for the markup page.

I *think* that you need to break your checkboxes into 2.

Something like:

Step 1: Call it something Design Category.

Please select one or more. Leave blank if not applicable.

Then on the Check/Uncheck section, call it

Step: 2 Select One or more specialties. Leave blank if not applicable.

it is much clearer that way, I think.
Top Expert 2011

Commented:
@mrong, I have a slight issue with the way the checkboxes are designed.

It would have been simpler if you had given the checkboxes SAME name but with different values.

Something like:

<input type="checkbox" name="mycheckbox" value="Design_Build">
<input type="checkbox" name="mycheckbox" value="Marine_Engineering">
...
...

Author

Commented:
sammySeltzer:
Sorry,I am working on something else today and haven't get a chance to work on this. Most likely have to get back to you tomorrow.
Thanks

Author

Commented:
sammySeltzer:
Finally I came back to this question.
You help me solved "Specialty" checkboxes and also saw my other question for "Discipliine" checkbox. Do you think I should do the same for the "Discipline" checkboxes just like the "Specialty"?
That is, give all the "discipline" checkboxed a same name and pull them directly from the database?
Thanks.
Top Expert 2011

Commented:
You can pull them directly from the db but you don't have to.

What I think is important is that they have same name but with different values.

This way, on the processing page, we can say something like:

if request.checkbox(...) for the discipline is not blank, and specialty is blank then insert into discipline db only.

If displine checkbox is blank but specialty checkbox is NOT blank, then insert into specialty table.

If both are not blank, then insert into both.

Otherwise (else) response.write "you must check at least one box.

If you leave it with different names, then there is going to be a bunch of IF statements that will look messy

Make sense?

Author

Commented:
sammySeltzer:
There is no inserttion involve in my case. Only select and return the matching records.
Displines are checkbox field in my database. If I give them same name but different values, how do I run a search(select stmt) for it? For instance, I have  "Design_Build" & 'Marine_Engineering" checked,
what my select stmt will be?

Thanks.
Top Expert 2011

Commented:
If you change your markup like I suggested, to something like:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang="en">

<head>


<!-- THIS CODE PROVIDES THE FORMATTING FOR THE TEXT - PLEASE LEAVE INTACT -->
<link rel="stylesheet" href="css/nbmain.css" type="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
.style1 {
	font-size: medium;
}
</style>
<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
var checkflag = "false";
function check(field) {
if (checkflag == "false") {
for (i = 0; i < field.length; i++) {
field[i].checked = true;}
checkflag = "true";
return "Uncheck All"; }
else {
for (i = 0; i < field.length; i++) {
field[i].checked = false; }
checkflag = "false";
return "Check All"; }
}
    function alterAll(Discipline){
          var allCBox = document.getElemetnsByName(Discipline.name);
          var state = Discipline.checked;
                 for( var i = 0 ; allCBox.length; i++)
                         allCBox[i].checked = state;
    }

function selAll()
{
var chk = document.frmUser.Discipline;
for (i = 0; i < chk.length; i++)
{
	if(chk[i].checked == true) chk[i].checked = false ;
	else chk[i].checked = true ;
}
}

//  End -->
</script>

</head>


<body bgcolor="#ffffff" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">

               <FORM action="FacilitiesDiscipline.asp"  NAME="frmUser" METHOD="POST" target="new" >
                     <table align="center">
                      <tr>
                       <td>

						<table width="65%" bgcolor="gold">
							<tr>
								<td>&nbsp;</td>
								<td colspan="3"><span class="style1">Please select below to	view the report</span></td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
						</table>
						<table width="65%" bgcolor="gold">
							<tr>
								<td>
								<input type="checkbox" name="inverter" onClick="javascript:selAll();"></td>
								<td style="width: 346px">Check all / Uncheck all</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							</table>
							<table width="65%" bgcolor="gold">
							<tr>
								 <td>&nbsp;</td>
								 <td style="width: 346px">
              					 <p><input type="checkbox" name="Discipline" value="Architecture">Architecture</td>
								<td style="width: 99px">&nbsp;</td>
								 <td style="width: 205px">
              					 <p><input type="checkbox" name="Discipline" value="Laboratory_Planning">Laboratory_Planning</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td style="height: 25px;">&nbsp;</td>
								<td style="width: 346px; height: 25px;">
								<input type="checkbox" name="Discipline" value="Civil_Site_Engineer">Civil/Site_Engineer</td>
								<td style="width: 99px; height: 25px;"></td>
								<td style="width: 205px">
								<input type="checkbox" name="Discipline" value="Landscape_Architecture">Landscape_Architecture</td>
								<td style="width: 128px; height: 25px;"></td>
								<td style="height: 25px"></td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Discipline" value="Design_Build">Design/Build</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Discipline" value="Marine_Engineering">Marine_Engineering</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Discipline" value="Electrical_Engineering">Electrical_Engineering</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Discipline" value="Mechanical_Engineering">Mechanical_Engineering</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Discipline" value="Fire_Protection">Fire_Protection</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Discipline" value="Planning">Planning</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Discipline" value="Forensic_Engineering">Forensic_Engineering</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Discipline" value="Plumbing_Engineering">Plumbing_Engineering</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Discipline" value="Geotechnical">Geotechnical</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Discipline" value="Roofing_Consultant">Roofing_Consultant</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">
								<input type="checkbox" name="Discipline" value="Interior_Design">Interior_Design</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Discipline" value="Structural_Engineering">Structural_Engineering</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">&nbsp;</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>
							</tr>
							<tr>
								<td>&nbsp;</td>
								<td style="width: 346px">&nbsp;</td>
								<td style="width: 99px">&nbsp;</td>
								<td style="width: 205px">
								<input type="checkbox" name="Discipline" value="Surveying">Surveying</td>
								<td style="width: 128px">&nbsp;</td>
								<td>&nbsp;</td>

							</tr>
                  </table>
                  <hr width="70%" align="left" color="gainsboro">
	  <%

    Dim rsItems2
   Set rsItems2 = Server.CreateObject("ADODB.Recordset")
   set objConn2 = server.CreateObject("ADODB.Connection")
   set objRS =server.CreateObject("ADODB.Recordset")
   objConn2.ConnectionString= "Provider=microsoft.jet.oledb.4.0;Data source=" & server.mappath("Paging.mdb")
   objRs.CursorLocation = 3
   objConn2.Open


	strSQL2 = "Select distinct Specialty_Details from Specialty_Detail where Specialty_Details is not null order by Specialty_Details;"
     rsItems2.Open strSQL2, objConn2

	'Display the FORM and the top of the TABLE
	'Response.Write "<FORM METHOD=POST ACTION=""MasterSpecialty.asp"" target=""_new"">"
	Response.Write "<input type='checkbox' name='checkall'  onClick='this.value=check(this.form.result)'>check all/uncheck all"



	Response.Write "<TABLE border='1' cellpadding='0' cellspacing='4' style='border-collapse: collapse' bordercolor='#111111' width='70%' bgcolor='steelBLUE'>"
	Dim iLoop
	Response.Write "<TR>"
	Response.Write "<TH></TH>"
	Response.Write "<TH>Specialty</TH>"

	Response.Write "</TR>"

	'Display each element in the table..
	Do While Not rsItems2.EOF


		Response.Write "<TR>" & vbCrLf

		'Create a checkbox to check for searching records, setting the checkboxes
		'Value equal to the current items
		'Response.Write "<TD><INPUT TYPE=CHECKBOX NAME='Result' "
		'Response.Write "VALUE=" & rsItems2("Specialty_Details")& "></TD>"

          %>
            <TD><INPUT TYPE="CHECKBOX" NAME="result" VALUE="<%=rsItems2("Specialty_Details")%>"></TD>
            <%


		'Display the number and name of the record
		Response.Write vbCrLf & "<TD>" & rsItems2("Specialty_Details") & "</TD>"

		Response.Write "</TR>" & vbCrLf & vbCrLf

		'Move to the next record...
		rsItems2.MoveNext
	Loop



	'Clean up our ADO objects
	rsItems2.Close
	Set rsItems2 = Nothing

	objConn2.Close
	Set objConn2 = Nothing            %>

		<tr>
		<td style="width: 346px">
		<INPUT TYPE="Submit" VALUE="Submit ">
        <INPUT TYPE="RESET"></td>
         </tr>
	   </table>
      </td>
     </tr>
    </table>

    </form>

</BODY>

Open in new window


Then your processing code could be something like this:


<%@ Language=VBScript %>
<% Option Explicit %>
<%

	Dim objConn,projGroup
	Set rst = Server.CreateObject("ADODB.Recordset")
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
    "DATA SOURCE=" & server.mappath("paging.mdb")
	objConn.Open


	'We want to archive our projects.  The list of project Numbers that need
	'to be archived are in a comma-delimited list...
	Dim strSpecialtyList,strDisciplineList,rst,rst1,rst2,rst3,strSQL,strSQL1,strSQL2,strSQL3
	strSpecialtyList = Request("Result")
	strDisciplineList = Request("Discipline")

	if strSpecialtyList = "" and strDisciplineList = "" then
		'No items selected from either Specialty or Discipline checkox
		Response.Write "You must check at least one box from either Specialty group or Discipline group !"
		'Response.end
	ElseIf strSpecialtyList <> "" and strDisciplineList = "" then
	'Selected from the Specialty checkbox only

		'Now, use the SQL set notation to select all of the records
		'specified by strSpecialtyList and strDisciplineList
		strSpecialtyList = Replace( strSpecialtyList, ", ", "','" ) ' see below!
		strDisciplineList = Replace( strDisciplineList, ", ", "','" ) ' see below!


        strSQL ="SELECT DISTINCT Specialty_Details FROM Specialty_Detail where Specialty_details in ('" & strSpecialtyList & "') order by Specialty_Details;"
       Response.Write "DEBUG strSQL: " & strSQL & "<HR>"
       'response.end
       Set rst=objConn.Execute(strSQL)

       ElseIf strSpecialtyList = "" and strDisciplineList <> "" then
       'Selected from Discipline checkbox only

        strSQL1 ="SELECT DISTINCT Discipline_Details FROM Discipline_Detail WHERE Discipline_Details in ('" & strDisciplineList & "') order by Discipline_Details;"
        Response.Write "DEBUG strSQL1: " & strSQL1 & "<HR>"
        'Response.end
        Set rst1=objConn.Execute(strSQL1)

        ElseIf strSpecialtyList <> "" and strDisciplineList <> "" then
        'Selected from both Specialty and Discipline checkboxes

        strSQL2 ="SELECT DISTINCT Specialty_Details FROM Specialty_Detail where Specialty_details in ('" & strSpecialtyList & "') order by Specialty_Details;"
       Response.Write "DEBUG strSQL2: " & strSQL2 & "<HR>"
       'response.end
       Set rst2=objConn.Execute(strSQL2)


         strSQL3 ="SELECT DISTINCT Discipline_Details FROM Discipline_Detail WHERE Discipline_Details in ('" & strDisciplineList & "') order by Discipline_Details;"
         Response.Write "DEBUG strSQL3: " & strSQL3 & "<HR>"
        ' Response.end
         Set rst3=objConn.Execute(strSQL3)

      Else

      End If

		'Clean up
		objConn.Close
		Set objConn = Nothing
%>
<form>
<input type=button value="Close Window" onClick="javascript:window.close();">
</form>

Open in new window


Untested, sorry but looks fundamentally sound in context.

Let me know.

You will of course have to change your query and use yours.

Author

Commented:
sammySeltzer:
I went over your sample code and in the 2nd part you have the following
 ElseIf strSpecialtyList <> "" and strDisciplineList <> "" then
strSQL2 ...............
strSQL3...............
Are you thinking to union those 2 select stmt together later?
Thanks.
Top Expert 2011

Commented:
You can UNION them if you prefer or you just list them out as shown.

Use what works best for you but the concept is profound.
Top Expert 2011
Commented:
You can also try very simply:

		'Now, use the SQL set notation to select all of the records
		'specified by strSpecialtyList and strDisciplineList
		strSpecialtyList = Replace( strSpecialtyList, ", ", "','" ) ' see below!
		strDisciplineList = Replace( strDisciplineList, ", ", "','" ) ' see below!

	if strSpecialtyList = "" and strDisciplineList = "" then
		'No items selected from either Specialty or Discipline checkox
		Response.Write "You must check at least one box from either Specialty group or Discipline group !"
		'Response.end
	Else
	'Selected from either one or both groups

        strSQL ="SELECT DISTINCT Specialty_Details FROM Specialty_Detail where Specialty_details in ('" & strSpecialtyList & "') order by Specialty_Details;"

 UNION ALL

        strSQL ="SELECT DISTINCT Discipline_Details FROM Discipline_Detail WHERE Discipline_Details in ('" & strDisciplineList & "') order by Discipline_Details;"
        Response.Write "DEBUG strSQL: " & strSQL & "<HR>"
        'Response.end
        Set rst=objConn.Execute(strSQL)

 End If

Open in new window


This way, it display one specialty values if user selects from Specialty group only

OR displays Discipline values if user selects from Discipline group only

OR displays values from both groups

Author

Commented:
I tried your code posted at 08/16/11 11:48 AM

Got the following output:
DEBUG strSQL2: SELECT DISTINCT Specialty_Details FROM Specialty_Detail where Specialty_details in ('A/V, Elevator Specialist') order by Specialty_Details;
--------------------------------------------------------------------------------
DEBUG strSQL3: SELECT DISTINCT Discipline_Details FROM Discipline_Detail WHERE Discipline_Details in ('Architecture, Forensic_Engineering, Geotechnical') order by Discipline_Details;
--------------------------------------------------------------------------------
 
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/firmsurvey/DS.asp, line 72


strSQL3 ="SELECT DISTINCT Discipline_Details FROM Discipline_Detail WHERE Discipline_Details in ('" & strDisciplineList & "') order by Discipline_Details;"
         Response.Write "DEBUG strSQL3: " & strSQL3 & "<HR>"
        ' Response.end
Line 72->         Set rst3=objConn.Execute(strSQL3)
Top Expert 2011

Commented:
Well, a couple of things I noticed right away.

1, Move these:

            'Now, use the SQL set notation to select all of the records
            'specified by strSpecialtyList and strDisciplineList
            strSpecialtyList = Replace( strSpecialtyList, ", ", "','" ) ' see below!
            strDisciplineList = Replace( strDisciplineList, ", ", "','" ) ' see below!


to the top next to these:
      Dim strSpecialtyList,strDisciplineList,rst,rst1,rst2,rst3,strSQL,strSQL1,strSQL2,strSQL3
      strSpecialtyList = Request("Result")
      strDisciplineList = Request("Discipline")

This way, instead of these:

'A/V, Elevator Specialist'

you get these:

'A/V', 'Elevator Specialist'

Second, you should be using your own code, NOT the ones I used for testing on my PC.

Author

Commented:
I saw the problem caused the error.
For each record In table Discipline_detail, I have FirmID with checkbox fields(Architecture,Civil_Site_Engineer and etc...
how to change the select stmt ?
Thanks.
Top Expert 2011

Commented:
Maybe I didn't understand you but is this what you meant?


SELECT DISTINCT Discipline_Details FROM Discipline_Detail WHERE FormId in ('"& strDisciplineList &"') order by Discipline_Details;

Open in new window

Author

Commented:
My bad, I don't have Discipline_Details field in table Discipline_detail

Author

Commented:
I tried to union 2 sql together below but got error -> Type mismatch: 'UNION'

strSQL ="SELECT DISTINCT Specialty_Details FROM Specialty_Detail where Specialty_details in ('" & strSpecialtyList & "') order by Specialty_Details;"
 UNION ALL
        strSQL ="SELECT DISTINCT Discipline_Details FROM Discipline_Detail WHERE Discipline_Details in ('" & strDisciplineList & "') order by Discipline_Details;"
       
Top Expert 2011

Commented:
Sorry; that was klunky!!


    strSQL ="SELECT DISTINCT Specialty_Details FROM Specialty_Detail where Specialty_details in ('" & strSpecialtyList & "') order by Specialty_Details " & _
         " UNION ALL " & _
        "SELECT DISTINCT Discipline_Details FROM Discipline_Detail WHERE Discipline_Details in ('" & strDisciplineList & "')"
Set rst=objConn.Execute(strSQL)

Open in new window

Author

Commented:
Thank you ! I will post another question for formatting the ASP layout soon.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial