2-part question about checkbox repeated regions 2

[FollowThePeacemakers]

A TWO-PART QUESTION...

PART 1: I have a page with repeat region checkboxes on it (using Ultradev) and these checkboxes are populated by a database field like so...

http://216.122.245.51/friends/RAB/select.asp

Here's the problem: Notice how there are excess identical "industries" listed? The region is repeating according to how many like records there are in each industry. I don't want this. How do I solve this problem? If there is an industry, say "Roofing Contractor," how do I get the page to only list ONE checkbox for ONE industry instead of THREE checkboxes just because there are three "like-industried" contractors in the database? I hope this question makes sense. In a nutshell: I want one checkbox per industry even though there are multiple records within that industry.

PART 2: Try out the form by selecting only one checkbox and click on "Submit." It works, right? OK go back and try again, but this time select multiple industries and click "Submit." You'll see that the ?industry= values get passed but my verify.asp page won't display multiple industries in the same table. How do I remedy this?

Anyone's quick reply is very much appreciated! Deadline looms.

 

[gny]

PART 1:
The fault probably lies in your SQL query.
My guess is it looks something like this:

select industry from contractor;

This code doesn´t choose only unique industries.

Try this instead:

select distinct industry from contractor;

Hope i´ve been some help

 

[gny]

About Part 2
Please post the code where you build the SQL for the query on verify.asp as that´s probably where it goes wrong

 

[NiteCrawlr]

Part 1:

I was having the same problem, I solved it by doing something like this (It is not a good way to do it but it works... ):

Set rs_opt = Server.CreateObject("ADODB.RecordSet&quot
connopt = "Select industry from contractor"
Set rs_opt = objconexao.execute(connopt)

control_a = ""
Do while not rs_opt.EOF
control_b = trim(rs_opt("industry&quot)
If control_a <> control_b then

<input type=&quot;checkbox&quot; value=&quot;<%=trim(rs_opt(&quot;industry&quot)%>&quot; name=&quot;industry&quot;>
<font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;>trim(rs_opt(&quot;industry&quot)<br>
</font>

end if
control_a = control_b
rs_opt.MoveNext
loop

I first create a variable (control_a) which is blank then in the loop I create another variable (control_b) which in your case it will get industry value, then before it does anything, I compare the two variables, if they have a diferent value then it will create the checkbox otherwise it won't.

 

[FollowThePeacemakers]

The &quot;select distinct&quot; worked like a charm! Thank you! Can you tell I'm new to SQL/VBScript?

As for Part 2, here's the SQL statement on verify.asp...

<%
Dim rsSelectMain__MMColParam
rsSelectMain__MMColParam = &quot;1&quot;
if (Request.QueryString(&quot;id_industry&quot <> &quot;&quot then rsSelectMain__MMColParam = Request.QueryString(&quot;id_industry&quot
%>
<%
set rsSelectMain = Server.CreateObject(&quot;ADODB.Recordset&quot
rsSelectMain.ActiveConnection = MM_XxXxXxXxconnection_STRING
rsSelectMain.Source = &quot;SELECT id_industry, Industry, Company, City, State, Contact, Phone, Fax, Email, Website FROM FOI WHERE id_industry = '&quot; + Replace(rsSelectMain__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;' ORDER BY Industry ASC&quot;
rsSelectMain.CursorType = 0
rsSelectMain.CursorLocation = 2
rsSelectMain.LockType = 3
rsSelectMain.Open()
rsSelectMain_numRows = 0
%>

Thanks so much for your help! I'm learning as we go.

 

[gny]

I´m glad to be of some help!

What you should do since you don´t know how many industries will be sought is to build the WHERE-clause dynamically.
Declare a variable which will contain the clause and use that instead of the WHERE-clause part of the query.
Fill it by iterating through all sought industries.
Something like this:

dim sWhere
dim sItem

set rsSelectMain = Server.CreateObject("ADODB.Recordset")
rsSelectMain.ActiveConnection = MM_XxXxXxXxconnection_STRING

sWhere=""
for each item in request("id_industry")
   sItem =Replace(item, "'", "''") 
   '*** check if this is the first item
   if sWhere = "" then
      sWhere = " WHERE id_industry ='" & sItem & "'"
   else
      sWhere = sWhere & " OR id_industry ='" & sItem & "'"
   end if
next
rsSelectMain.Source = "SELECT id_industry, Industry, Company, City, State, Contact, Phone, Fax, Email, Website FROM FOI" & sWhere & " ORDER BY Industry ASC" 

'*** And so on....

 

[FollowThePeacemakers]

gny: Your solution to Part 2 works in theory. Here is what I got from your solution...

http://216.122.245.51/friends/RAB/select_gny.asp

If you multiple-select a few industries, the results are there on verify.asp (because they are showing on the recordset navigation status) but the records themselves just aren't showing in the table. Hmmm.

 

[gny]

In theory...

Please post the code where you print out the table.

 

[FollowThePeacemakers]

Cheers!...


<table align=&quot;center&quot; border=&quot;1&quot; cellpadding=&quot;4&quot; bordercolor=&quot;#999999&quot; cellspacing=&quot;0&quot;>
<tr bgcolor=&quot;#333333&quot;>
<td align=&quot;default&quot; width=&quot;12%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>Industry
</b></font></td>
<td align=&quot;default&quot; width=&quot;12%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>Company
</b></font></td>
<td align=&quot;default&quot; width=&quot;12%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>Contact
</b></font></td>
<td align=&quot;default&quot; width=&quot;11%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>Phone
</b></font></td>
<td align=&quot;default&quot; width=&quot;10%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>Fax
</b></font></td>
<td align=&quot;default&quot; width=&quot;10%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>City
</b></font></td>
<td align=&quot;default&quot; width=&quot;4%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>State</b></font></td>
<td align=&quot;default&quot; width=&quot;17%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>Email
</b></font></td>
<td align=&quot;default&quot; width=&quot;12%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b>Website
</b></font></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsSelectMain.EOF))
%>
<tr>
<td align=&quot;default&quot; width=&quot;12%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(rsSelectMain.Fields.Item(&quot;Industry&quot.Value)%></font> </td>
<td align=&quot;default&quot; width=&quot;12%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(rsSelectMain.Fields.Item(&quot;Company&quot.Value)%></font> </td>
<td align=&quot;default&quot; width=&quot;12%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(rsSelectMain.Fields.Item(&quot;Contact&quot.Value)%></font> </td>
<td align=&quot;default&quot; width=&quot;11%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(rsSelectMain.Fields.Item(&quot;Phone&quot.Value)%></font> </td>
<td align=&quot;default&quot; width=&quot;10%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(rsSelectMain.Fields.Item(&quot;Fax&quot.Value)%></font></td>
<td align=&quot;default&quot; width=&quot;10%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(rsSelectMain.Fields.Item(&quot;City&quot.Value)%></font></td>
<td align=&quot;default&quot; width=&quot;4%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(rsSelectMain.Fields.Item(&quot;State&quot.Value)%> </font></td>
<td align=&quot;default&quot; width=&quot;17%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(rsSelectMain.Fields.Item(&quot;Email&quot.Value)%></font></td>
<td align=&quot;default&quot; width=&quot;12%&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><a href=&quot;

http://www.<%=(rsSelectMain.Fields.Item(&quot;Website&quot;).Value)%>&quot;

target=&quot;_blank&quot;><%=(rsSelectMain.Fields.Item(&quot;Website&quot.Value)%></a></font></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsSelectMain.MoveNext()
Wend
%>
</table>

 

[gny]

You only enter the while-loop if

Repeat1__numRows <> 0

.
As far as I can see you set Repeat1__numRows to 0 (zero) right after you open the recordset. Could this be the problem/solution?

 

[FollowThePeacemakers]

gny: That was the problem. Thank you! I voted for you again!

I took a shot and set it to 1 and now it works just how I need it to! Check it out...

http://216.122.245.51/friends/RAB/select_gny.asp

I still the SQL/VBS/ASP rookie and thank you for the guidance.

 

[gny]

You´re welcome, i´m really glad to help....and votes are always nice...
It seems to work really nicely now!
Except if you select no industry, then you get all companies. I guess maybe you should give that some thought as well (if you want to). It´s pretty easy, if you don´t want the user to get all companies when they select no industry, just show a message &quot;no industries found&quot; or redirect/transfer the browser back to the select page if the

Request.QueryString("id_industry") = ""

.
But of course, that´s just a minor detail i guess.
Good luck with your future work! Maybe you´ll help me someday?
Take care,
/gny

 

[FollowThePeacemakers]

That's a good idea! I didn't think of that. Finally, something at my lower level of VB coding skill. Here's what I did after your suggestion...

If Request.QueryString(&quot;id_industry&quot = &quot;&quot; Then
Response.redirect(&quot;select_gny.asp&quot
End If

Short and sweet!

By the way, (you've probably noticed) your nickname is in my test filenames so I can keep track of whose assistance is whose. I'm going to change all that for live use (in the code as well).

I'm sure I could ask you a million questions! :-V This project is above my experience level but tragically I need to do it and there's still plenty more to be done. |-I

Wow, dunno what I'd do without Tek-tips!!!