mrong
asked on
Merging two searching pages together
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
strSQL2 ="SELECT DISTINCT table1 FROM table1 LEFT JOIN Discipline_detail ON table1.FirmID = Discipline_detail.FirmID WHERE (((" & Architecture & "=False) OR Discipline_detail.[Archite
Thanks in advance.
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
strSQL2 ="SELECT DISTINCT table1 FROM table1 LEFT JOIN Discipline_detail ON table1.FirmID = Discipline_detail.FirmID WHERE (((" & Architecture & "=False) OR Discipline_detail.[Archite
Thanks in advance.
ASKER
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.
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.
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.
No, I take that back. That's not the case as it is not performing a keyword search.
Be back a little later.
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?
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?
ASKER
sammySeltzer,
I will post them this morning. also posted another question at the link below.
https://www.experts-exchange.com/questions/27245835/How-to-create-a-PDF-in-asp.html
thanks.
I will post them this morning. also posted another question at the link below.
https://www.experts-exchange.com/questions/27245835/How-to-create-a-PDF-in-asp.html
thanks.
ASKER
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
checkbox.txt
ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/firmsurvey/MasterDSReport
checkbox.txt
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
strSQL2 ="SELECT DISTINCT table1 FROM table1 LEFT JOIN Discipline_detail ON table1.FirmID = Discipline_detail.FirmID WHERE (((" & Architecture & "=False) OR Discipline_detail.[Archite
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:
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?
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
strSQL2 ="SELECT DISTINCT table1 FROM table1 LEFT JOIN Discipline_detail ON table1.FirmID = Discipline_detail.FirmID WHERE (((" & Architecture & "=False) OR Discipline_detail.[Archite
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 followNow, 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?
ASKER
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.
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.
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.
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.
ASKER
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.
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.
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...
thanks
That error, by the way, means you are missing either recordset or connection string like set conn= server.createobject ...
Be back shortly...
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
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.
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
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> </td>
<td colspan="3"><span class="style1">Please select below to
view the report</span></td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td>
</td>
<td style="width: 346px">
<p><input type="checkbox" name="Architecture" value="1" onfocus="nextfield ='Laboratory_Planning'" >Architecture</td>
<td style="width: 99px"> </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"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td style="height: 25px;"> </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> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Design_Build" value="1" onfocus="nextfield ='Marine_Engineering'" >Design/Build</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Marine_Engineering" value="1" onfocus="nextfield ='Electrical_Engineering'" >Marine_Engineering</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Electrical_Engineering" value="1" onfocus="nextfield ='Mechanical_Engineering'" >Electrical_Engineering</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Mechanical_Engineering" value="1" onfocus="nextfield ='Fire_Protection'" >Mechanical_Engineering</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Fire_Protection" value="1" onfocus="nextfield ='Planning'" >Fire_Protection</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Planning" value="1" onfocus="nextfield ='Forensic_Engineering'" >Planning</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Forensic_Engineering" value="1" onfocus="nextfield ='Plumbing_Engineering'" >Forensic_Engineering</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Plumbing_Engineering" value="1" onfocus="nextfield ='Geotechnical'" >Plumbing_Engineering</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Geotechnical" value="1" onfocus="nextfield ='Roofing_Consultant'" >Geotechnical</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Roofing_Consultant" value="1" onfocus="nextfield ='Interior_Design'" >Roofing_Consultant</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Interior_Design" value="1" onfocus="nextfield ='Structural_Engineering'" >Interior_Design</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Structural_Engineering" value="1" onfocus="nextfield ='Surveying'" >Structural_Engineering</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Surveying" value="1" onfocus="nextfield ='done'" >Surveying</td>
<td style="width: 128px"> </td>
<td> </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>
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.
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.
@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"
...
...
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"
...
...
ASKER
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
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
ASKER
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.
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.
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?
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?
ASKER
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.
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.
If you change your markup like I suggested, to something like:
Then your processing code could be something like this:
Untested, sorry but looks fundamentally sound in context.
Let me know.
You will of course have to change your query and use yours.
<!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> </td>
<td colspan="3"><span class="style1">Please select below to view the report</span></td>
<td style="width: 128px"> </td>
<td> </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"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
</table>
<table width="65%" bgcolor="gold">
<tr>
<td> </td>
<td style="width: 346px">
<p><input type="checkbox" name="Discipline" value="Architecture">Architecture</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<p><input type="checkbox" name="Discipline" value="Laboratory_Planning">Laboratory_Planning</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td style="height: 25px;"> </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> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Discipline" value="Design_Build">Design/Build</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Discipline" value="Marine_Engineering">Marine_Engineering</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Discipline" value="Electrical_Engineering">Electrical_Engineering</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Discipline" value="Mechanical_Engineering">Mechanical_Engineering</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Discipline" value="Fire_Protection">Fire_Protection</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Discipline" value="Planning">Planning</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Discipline" value="Forensic_Engineering">Forensic_Engineering</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Discipline" value="Plumbing_Engineering">Plumbing_Engineering</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Discipline" value="Geotechnical">Geotechnical</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Discipline" value="Roofing_Consultant">Roofing_Consultant</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px">
<input type="checkbox" name="Discipline" value="Interior_Design">Interior_Design</td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Discipline" value="Structural_Engineering">Structural_Engineering</td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px"> </td>
<td style="width: 128px"> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td style="width: 346px"> </td>
<td style="width: 99px"> </td>
<td style="width: 205px">
<input type="checkbox" name="Discipline" value="Surveying">Surveying</td>
<td style="width: 128px"> </td>
<td> </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>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>Untested, sorry but looks fundamentally sound in context.
Let me know.
You will of course have to change your query and use yours.
ASKER
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.
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.
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.
Use what works best for you but the concept is profound.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(strSQ
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(strSQ
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,strDiscip
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.
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,strDiscip
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.
ASKER
I saw the problem caused the error.
For each record In table Discipline_detail, I have FirmID with checkbox fields(Architecture,Civil_
how to change the select stmt ?
Thanks.
For each record In table Discipline_detail, I have FirmID with checkbox fields(Architecture,Civil_
how to change the select stmt ?
Thanks.
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;ASKER
My bad, I don't have Discipline_Details field in table Discipline_detail
ASKER
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;"
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;"
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)ASKER
Thank you ! I will post another question for formatting the ASP layout soon.
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.