Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating data in a columnar fashion inside a scollable table

Status
Not open for further replies.

tmcneil

Technical User
Nov 17, 2000
294
US
I have some code within a DIV that I would like to try and populate a table with columns and rows. The first column will have check boxes with the 2nd column containing all Red Line subway stations. The next two rows will have check boxes and the list of orange line stations. So, that will leave the green, blue and silver line stations to be shown.
Well, the code that I have is great for populating data in a row-like fashion, but I need to change it for the purposes of what I am trying to do. Any ideas or more information needed to explain my problem.

Todd

Code:
<DIV id="Destination" STYLE="display: <%if tab <> "Destination" then%> none <% end if%>;  width: 100%;">
<table border="0" cellspacing="0" cellpadding="0" class="box" height="297">
<tr><td height="4"></td></tr>
<tr>
<td width="2" rowspan="4"><img src="images/space.gif" width="2" height="1"></td>
<td></td>
<td width="2" rowspan="4"><img src="images/space.gif" width="2" height="1"></td>
</tr>
<tr><td height="8"><img src="images/space.gif" width="1" height="8"></td></tr>
<tr>
<td width="2" rowspan="4"><img src="images/space.gif" width="2" height="1"></td>
<td></td>
<td width="2" rowspan="4"><img src="images/space.gif" width="2" height="1"></td>
<tr>
<td colspan="1" class="canvas" align="top">
<div class="scrollTable" id="testScrollTable" style="overflow: auto; width: 822; height: 326;  border-right: 0px;">
<span class="scrollTableHead">
<table width="100%" cellspacing="0" frame="border">
<tr>
<td width="18"><nobr>&nbsp;</nobr></td>
<td width="141"><nobr>&nbsp;<b>Red Line</b></nobr></td>
<td width="18"><nobr>&nbsp;</nobr></td>
<!--<td width="56"><nobr>&nbsp;</nobr></td>-->
<td width="141"><nobr>&nbsp;<b>Orange Line</b></nobr></td>
<td width="19"><nobr>&nbsp;</nobr></td>
<!--<td width="56"><nobr>&nbsp;</nobr></td>-->
<td width="141"><nobr>&nbsp;<b>Blue Line</b></nobr></td>
<td width="19"><nobr>&nbsp;</nobr></td>
<!--<td width="56"><nobr>&nbsp;</nobr></td>-->
<td width="141"><nobr>&nbsp;<b>Green Line</b></nobr></td>
<td width="19"><nobr>&nbsp;</nobr></td>
<!--<td width="56"><nobr>&nbsp;</nobr></td>-->
<td width="141"><nobr>&nbsp;<b>Silver Line</b></nobr></td>
<td width="19"><nobr>&nbsp;</nobr></td>
<!--<td width="56"><nobr>&nbsp;</nobr></td>-->
<td width="141"><nobr>&nbsp;<b>Domain/Station Group</b></nobr></td>
<td></td>
</tr>
</table>
</span>
<span id="sTable" class="scrollTableBody" style="width: 820; height: 300px; overflow-x: hidden;">
<table width="100%" cellspacing="0" cellpadding="0" style="border-collapse: collapse;">
<%
	Set cn = GetDBConnection()
	Set SQLStmt = Server.CreateObject("ADODB.Command")
	Set RS = Server.CreateObject("ADODB.Recordset")
	SQLStmt.CommandText = "SELECT a.location_id, a.name, a.station_id, " & _
	                       " b.domain_id, c.domain_name "                 & _ 
	                       "FROM LOCATION_STATION_NODE_V a, "             & _
	                       " DOMAIN_STATION_XREF b, "                     & _
	                       " DOMAIN c "                                   & _
	                       "WHERE is_station='Y' AND "                    & _
	                       " a.STATION_ID = b.STATION_ID AND "            & _
	                       " b.DOMAIN_ID  = c.DOMAIN_ID "                 & _
	                       "ORDER BY domain_id, station_id"

	SQLStmt.CommandType = 1
	Set SQLStmt.ActiveConnection = cn
	RS.Open SQLStmt

	count = 0
	Do While Not RS.EOF
	    count = count + 1
%>
<tr class="item" height="19">
<td class="frowdata" width="20" nowrap>
<%
	chkd = ""
	IF Not IsEmpty(stations) THEN
	  IF IsArray(stations) THEN
  	    FOR EACH sid in stations
	      IF CInt(sid) = CInt(RS("STATION_ID")) THEN
		    chkd = " checked"
		  END IF
	    NEXT 
	  ELSEIF CInt(stations) = CInt(RS("STATION_ID")) THEN
	    chkd = " checked"
	  END IF
	END IF
%>
<input type="checkbox" name="station" id="station<%=count%>" value="<%=RS("STATION_ID")%>" <%=chkd%> onClick="checkItems(this);">
</td>
<!--<td class="rowdata" align="center" width="60" nowrap>&nbsp;<nobr><%=RS("LOCATION_ID")%></nobr></td>-->
<td class="rowdata" width="145" nowrap>&nbsp;<nobr><%=RS("NAME")%></nobr></td>
<td class="rowdata" width="20" nowrap>
<%
	chkd = ""
	IF Not IsEmpty(stations) THEN
	  IF IsArray(stations) THEN
  	    FOR EACH sid in stations
	      IF CInt(sid) = CInt(RS("STATION_ID")) THEN
		    chkd = " checked"
		  END IF
	    NEXT 
	  ELSEIF CInt(stations) = CInt(RS("STATION_ID")) THEN
	    chkd = " checked"
	  END IF
	END IF
%>
<input type="checkbox" name="station" id="station<%=count%>" value="<%=RS("STATION_ID")%>" <%=chkd%> onClick="checkItems(this);">
</td>
<!--<td class="rowdata" align="center" width="60" nowrap>&nbsp;<nobr><%=RS("LOCATION_ID")%></nobr></td>-->
<td class="rowdata" width="145" nowrap>&nbsp;<nobr><%=RS("NAME")%></nobr></td>
<td class="rowdata" width="20" nowrap>
<%
	chkd = ""
	IF Not IsEmpty(stations) THEN
	  IF IsArray(stations) THEN
  	    FOR EACH sid in stations
	      IF CInt(sid) = CInt(RS("STATION_ID")) THEN
		    chkd = " checked"
		  END IF
	    NEXT 
	  ELSEIF CInt(stations) = CInt(RS("STATION_ID")) THEN
	    chkd = " checked"
	  END IF
	END IF
%>
<input type="checkbox" name="station" id="station<%=count%>" value="<%=RS("STATION_ID")%>" <%=chkd%> onClick="checkItems(this);">
</td>
<!--<td class="rowdata" align="center" width="60" nowrap>&nbsp;<nobr><%=RS("LOCATION_ID")%></nobr></td>-->
<td class="rowdata" width="145" nowrap>&nbsp;<nobr><%=RS("NAME")%></nobr></td>
<td class="rowdata" width="20" nowrap>
<%
	chkd = ""
	IF Not IsEmpty(stations) THEN
	  IF IsArray(stations) THEN
  	    FOR EACH sid in stations
	      IF CInt(sid) = CInt(RS("STATION_ID")) THEN
		    chkd = " checked"
		  END IF
	    NEXT 
	  ELSEIF CInt(stations) = CInt(RS("STATION_ID")) THEN
	    chkd = " checked"
	  END IF
	END IF
%>
<input type="checkbox" name="station" id="station<%=count%>" value="<%=RS("STATION_ID")%>" <%=chkd%> onClick="checkItems(this);">
</td>
<!--<td class="rowdata" align="center" width="60" nowrap>&nbsp;<nobr><%=RS("LOCATION_ID")%></nobr></td>-->
<td class="rowdata" width="145" nowrap>&nbsp;<nobr><%=RS("NAME")%></nobr></td>
<td class="rowdata" width="20" nowrap>
<%
	chkd = ""
	IF Not IsEmpty(stations) THEN
	  IF IsArray(stations) THEN
  	    FOR EACH sid in stations
	      IF CInt(sid) = CInt(RS("STATION_ID")) THEN
		    chkd = " checked"
		  END IF
	    NEXT 
	  ELSEIF CInt(stations) = CInt(RS("STATION_ID")) THEN
	    chkd = " checked"
	  END IF
	END IF
%>
<input type="checkbox" name="station" id="station<%=count%>" value="<%=RS("STATION_ID")%>" <%=chkd%> onClick="checkItems(this);">
</td>
<!--<td class="rowdata" align="center" width="60" nowrap>&nbsp;<nobr><%=RS("LOCATION_ID")%></nobr></td>-->
<td class="rowdata" width="145" nowrap>&nbsp;<nobr><%=RS("NAME")%></nobr></td>
<td class="rowdata" width="20" nowrap>
<%
	chkd = ""
	IF Not IsEmpty(domains) THEN
	  IF IsArray(domains) THEN
  	    FOR EACH did in domains
	      IF CInt(did) = CInt(RS("DOMAIN_ID")) THEN
		    chkd = " checked"
		  END IF
	    NEXT 
	  ELSEIF CInt(domains) = CInt(RS("DOMAIN_ID")) THEN
	    chkd = " checked"
	  END IF
	END IF
%>
<input type="checkbox" name="domain" id="domain<%=count%>" value="<%=RS("DOMAIN_ID")%>" <%=chkd%> onClick="checkItems(this);">
</td>
<td class="lrowdata" width="140" nowrap>&nbsp;<nobr><%=RS("DOMAIN_NAME")%></nobr></td>
<td></td>
</tr>
<% 
    RS.MoveNext
	Loop
	RS.Close
%>
<% 
    FOR row = count to 12 %>
<tr class="item" height="19">
<td class="frowdata" width="20" nowrap>&nbsp;</td>
<td class="rowdata" width="145" nowrap>&nbsp;</td>
<td class="rowdata" width="20" nowrap>&nbsp;</td>
<td class="rowdata" width="145" nowrap>&nbsp;</td>
<td class="rowdata" width="20" nowrap>&nbsp;</td>
<td class="rowdata" width="145" nowrap>&nbsp;</td>
<td class="rowdata" width="20" nowrap>&nbsp;</td>
<td class="rowdata" width="145" nowrap>&nbsp;</td>
<td class="rowdata" width="20" nowrap>&nbsp;</td>
<td class="rowdata" width="145" nowrap>&nbsp;</td>
<td class="rowdata" width="20" nowrap>&nbsp;</td>
<td class="lrowdata" width="140" nowrap>&nbsp;</td>
<!--<td></td>-->
</tr>
<% NEXT %>
</table>
</div>
</td>
<td><img src="images/space.gif" width="32" height="1"></td>
<input type="hidden" id="numstations" name="numstations" value="<%=count%>">
<td align="left" valign="top" width="120">&nbsp;&nbsp;
<font size="1" style="font-size: 1pt;">
<img src="images/space.gif" height="2" width="84">
<input class="dlgbutton" type="button" name="SELECTALL" value="Select All" title="Select All Station Zones" onClick="selectAll();" <% if count = selcnt then %> disabled<% end if %>>
<img src="images/space.gif" height="1" width="84">
<input class="dlgbutton" type="button" name="UNSELECTALL" value="Unselect All" title="Unselect All Stations Zones" onClick="unselectAll();" <% if selcnt = 0 then %> disabled<% end if %>>
<img src="images/space.gif" height="1" width="84">
<input class="dlgbutton" type="reset" name="reset" value="Reset" title="Reset">
<img src="images/space.gif" height="1" width="84">
</font>
</td>
<td><img src="images/space.gif" width="6" height="1"></td>
</tr>
<tr><td height="4"></td></tr>
</table>
</div>
 
Well, I have more information. I created a view of how I would like the ordering of the records to look from left to right. Like this with the 1st row containing the column headers:

Red Line Orange Line Blue Line Green Line Silver Line
Alewife Oak Grove Wonderland Lechmere Dudley Square
Davis Malden Revere Science Pk Courthouse

and so on and so forth.

So, with that recordset, how am i going to determine how to populate my scrollable table from left to right, row by row>

Any ideas?

Todd
 
WiccaChic,

My thoughts exactly. I always hope that someone else has gone through some of the same kind of problems that I have so that's why I always post my questions here. Someone always has a good solution. Well, I did have a co-worker help me since she's more versed and programming than I am and we solved the problem by using an array and then using two For loops to pull out the station values and build the table. It's some cool stuff, but very hard to follow the first couple of times through. So, I put some notes into the code to make it easier to understand. This is the code that's on my Destination tab:

Code:
<DIV id="Destination" STYLE="display: <%if tab <> "Destination" then%> none <% end if%>; width: 100%;">
<table border="0" cellspacing="0" cellpadding="0" class="box" height="305">
    <tr><td height="1"></td></tr>
    <tr>
        <td width="2" rowspan="4"><img src="images/space.gif" width="2" height="1"></td>
        <td></td>
        <td width="2" rowspan="4"><img src="images/space.gif" width="2" height="1"></td>
    </tr>
    <tr><td height="2"><img src="images/space.gif" width="1" height="10"></td></tr>
    <tr>
        <td width="2" rowspan="4"><img src="images/space.gif" width="2" height="1"></td>
        <td></td>
        <td width="2" rowspan="4"><img src="images/space.gif" width="2" height="1"></td>
            <tr>
        <td colspan="1" class="canvas" align="top">

<div class="scrollTable" id="scrollDestTable" style=" overflow: auto; width: 866px; height: 306px; border-right: 0px none;">
<span class="scrollTableHead">
<table width="100%" cellspacing="0" frame="border"><!--width="100%"-->
    <tr>
        <td width="19"><nobr>&nbsp;</nobr></td>
        <td width="121"><nobr>&nbsp;<b>Red Line</b></nobr></td>
        <td width="19"><nobr>&nbsp;</nobr></td>
        <!--<td width="56"><nobr>&nbsp;</nobr></td>-->
        <td width="121"><nobr>&nbsp;<b>Orange Line</b></nobr></td>
        <td width="19"><nobr>&nbsp;</nobr></td>
        <!--<td width="56"><nobr>&nbsp;</nobr></td>-->
        <td width="121"><nobr>&nbsp;<b>Blue Line</b></nobr></td>
        <td width="19"><nobr>&nbsp;</nobr></td>
        <!--<td width="56"><nobr>&nbsp;</nobr></td>-->
        <td width="121"><nobr>&nbsp;<b>Green Line</b></nobr></td>
        <td width="19"><nobr>&nbsp;</nobr></td>
        <!--<td width="56"><nobr>&nbsp;</nobr></td>-->
        <td width="121"><nobr>&nbsp;<b>Silver Line</b></nobr></td>
        <td width="19"><nobr>&nbsp;</nobr></td>
        <!--<td width="56"><nobr>&nbsp;</nobr></td>-->
        <td width="80"><nobr>&nbsp;<b>Station Group</b></nobr></td>
        <td></td>
    </tr>
</table>
</span>
<span id="sDestinationTable" class="scrollTableBody" style="width: 860; height: 298px;">
    <table width="100%" id="dataDestination" cellspacing="0" cellpadding="0" style="border-collapse: collapse;"> <!-- width="100%"-->
<%	
    Dim curr_domain_id
    Dim num_groups
    Dim num_domain_ids
    Dim max_items
    Dim i, j
    Dim num_properties
    Dim DestinationArray()  'see ReDim DestinationArray(...) below
                            'DestinationArray(DOMAIN, DESTINATION, DESTINATION_PROPERTY)
                            'DESTINAION_PROPERTIES: 0 - ID
                            '                       1 - NAME
    num_properties = 2

    ' Get number of domain ids and the greates number of stations of all the domain_ids
    Set cn = GetDBConnection()
    Set SQLStmt = Server.CreateObject("ADODB.Command")
    Set RS = Server.CreateObject("ADODB.Recordset")
    SQLStmt.CommandText = "SELECT COUNT(b.domain_id) "                   & _ 
                           "   as num_domain_ids, "                      & _
                           " MAX( COUNT(b.domain_id) ) "                 & _ 
                           "   as max_domain_id "                        & _
                           "FROM LOCATION_STATION_NODE_V a, "            & _
                           " DOMAIN_STATION_XREF b, "                    & _
                           " DOMAIN c "                                  & _
                           "WHERE is_station='Y' AND "                   & _
                           " a.STATION_ID = b.STATION_ID AND "           & _
                           " b.DOMAIN_ID  = c.DOMAIN_ID AND "            & _
                           " b.DOMAIN_ID <= 5 "                          & _
                           "GROUP BY b.domain_id"
    SQLStmt.CommandType = 1
    Set SQLStmt.ActiveConnection = cn
    RS.Open SQLStmt
    num_domain_ids = RS("NUM_DOMAIN_IDS")
    num_groups = num_domain_ids + 1         'add 1 column for Station Groups
    max_items = RS("MAX_DOMAIN_ID")
    ReDim DestinationArray(num_groups-1, max_items-1, num_properties-1)    'See Dim DestinationArray() above

    ' Query all stations in all domains; to be used for Querying specific lines
    Set cn = GetDBConnection()
    Set SQLStmt = Server.CreateObject("ADODB.Command")
    Set RS = Server.CreateObject("ADODB.Recordset")
    SQLStmt.CommandText = "SELECT a.location_id, a.name, a.station_id, " & _
                           " b.domain_id, c.domain_name "                & _ 
                           "FROM LOCATION_STATION_NODE_V a, "            & _
                           " DOMAIN_STATION_XREF b, "                    & _
                           " DOMAIN c "                                  & _
                           "WHERE is_station='Y' AND "                   & _
                           " a.STATION_ID = b.STATION_ID AND "           & _
                           " b.DOMAIN_ID  = c.DOMAIN_ID "                & _
                           "ORDER BY domain_id, station_id"
    SQLStmt.CommandType = 1
    Set SQLStmt.ActiveConnection = cn
    RS.Open SQLStmt
    
    ' Iterate through each domain_id (exa; Red, Orange, Blue, Green Silver)
    For curr_domain_id=1 to num_domain_ids
        ' Query each line individually
        Set cn = GetDBConnection()
        Set SQLStmt = Server.CreateObject("ADODB.Command")
        Set RS2 = Server.CreateObject("ADODB.Recordset")
        SQLStmt.CommandText = "SELECT location_id, name, station_id, "       & _
                            " domain_id, domain_name "                    & _ 
                            "FROM ("                                      & _
                            RS.Source                                     & _
                            ") "                                          & _
                            "WHERE "                                      & _
                            " DOMAIN_ID = '" & curr_domain_id & "'"       & _
                            "ORDER BY station_id"
        SQLStmt.CommandType = 1
        Set SQLStmt.ActiveConnection = cn
        RS2.Open SQLStmt

        'DOMAIN_NAME  DOMAIN_ID     # OF STATIONS
        '-----------  ---------     -------------
        'RED            1               22
        'ORANGE         2               22
        'BLUE           3               12
        'GREEN          4               29
        'SILVER         5                3

        ' Populate Station Array with each line's LOCATION_IDs
        i = curr_domain_id - 1          'array starts at 0
        For j=0 to (RS2.RecordCount-1)
            DestinationArray(i, j, 0) = RS2("STATION_ID")
            'DestinationArray(i, j, 1) = RS2("LOCATION_ID")
            DestinationArray(i, j, 1) = RS2("NAME")
            RS2.MoveNext
        Next
    Next 'curr_domain_id

    ' Populate Station Array with StationGroups info
    Set cn = GetDBConnection()
    Set SQLStmt = Server.CreateObject("ADODB.Command")
    Set RS2 = Server.CreateObject("ADODB.Recordset")
    SQLStmt.CommandText = "SELECT domain_id, domain_name "     & _ 
                          "FROM DOMAIN "                       & _
                          "ORDER BY domain_id"
    SQLStmt.CommandType = 1
    Set SQLStmt.ActiveConnection = cn
    RS2.Open SQLStmt

    i = num_groups-1
    For j=0 to (RS2.RecordCount-1)
        DestinationArray(i, j, 0) = RS2("DOMAIN_ID")
        DestinationArray(i, j, 1) = RS2("DOMAIN_NAME")
        RS2.MoveNext
    Next
    
    ' Close Recordsets (in reverse order of dependence)
    RS2.Close
    RS.Close
    
    ' Evaluate Station Groups check boxes
    ' (pull recordsets from STATION_GROUPS table)
'    chkd = ""
'    If Not IsEmpty(groups) Then
'        If IsArray(groups) Then
'            For Each id in object
'                If CInt(id) = CInt(DestinationArray(i,j,0)) Then
'                    chkd = "checked"
'                End If
'            Next 
'        ElseIf CInt(groups) = CInt(DestinationArray(i,j,0)) Then
'            chkd = "checked"
'        End If
'    End If
'    
    ' Populate table with check boxes
    count = 0
    For j=0 to (max_items-1)
%>
        <tr class="item<%=j+1%>" height="19">
<%
        For i=0 to (num_groups-1)
%>
            <td class="rowdata" width="20" nowrap>
<%
            'Determine whether to check box ON or OFF ("checked" or "")
            dim object  'temp object just for the next 18 lines
            If (i < num_domain_ids) Then    'stations
                object = stations
            Else                            'last column is station groups
                object = groups
            End If

            chkd = ""
            If Not IsEmpty(object) Then
                If IsArray(object) Then
                    For Each id in object
                        If CInt(id) = CInt(DestinationArray(i,j,0)) Then
                            chkd = "checked"
                        End If
                    Next 
                ElseIf CInt(object) = CInt(DestinationArray(i,j,0)) Then
                    chkd = "checked"
                End If
            End If
%>
<%If (DestinationArray(i,j,0) <> "") Then   'item exists%>
<%  If (i < num_domain_ids) Then    'stations %>
<%      count = count + 1   'only count actual stations, not station groups %>
                <input type="checkbox" name="station" id="station<%=DestinationArray(i,j,0)%>" value="<%=DestinationArray(i,j,0)%>" <%=chkd%> onClick="checkItems(this);">
            </td>
            <td class="rowdata" width="125" nowrap>&nbsp;<nobr><%=DestinationArray(i,j,1)%></nobr></td>
<%  Else                            'last column is station group%>
                <input type="checkbox" name="group" id="group<%=DestinationArray(i,j,0)%>" value="<%=DestinationArray(i,j,0)%>" <%=chkd%> onClick="checkItems(this);">
            </td>
            <td class="lrowdata" width="75" nowrap>&nbsp;<nobr><%=DestinationArray(i,j,1)%></nobr></td>
            <td class="ritem" width="100%" nowrap>&nbsp;</td>
            <td></td>
        </tr>
<%  End If%>
<%Else                                      'empty item%>
<%  If (i < num_domain_ids) Then    'stations %>
            </td>
            <td class="rowdata" width="125" nowrap></td>
<%  Else                            'last column is station group%>
            </td>
            <td class="lrowdata" width="75" nowrap></td>
            <td class="ritem" width="100%" nowrap>&nbsp;</td>
            <td></td>
        </tr>
<%  End If%>
<%End If%>

<%
        Next 'i
    Next 'j
%>
    </table>
</span>
</div>
</td>
<td><img src="images/space.gif" width="5" height="1"></td>
<input type="hidden" id="numstations" name="numstations" value="<%=count%>">
<td align="middle" valign="top" width="120">&nbsp;&nbsp;
<font size="1" style="font-size: 1pt;">
<img src="images/space.gif" height="2" width="84">
<input class="dlgbutton" type="button" name="SELECTALL" value="Select All" title="Select All Stations" onClick="selectAll();" <% if count = selcnt then %> disabled<% end if %>>
<img src="images/space.gif" height="1" width="84">
<input class="dlgbutton" type="button" name="UNSELECTALL" value="Unselect All" title="Unselect All Stations" onClick="unselectAll();" <% if selcnt = 0 then %> disabled<% end if %>>
<img src="images/space.gif" height="1" width="84">
<input class="dlgbutton" type="reset" name="reset" value="Reset" title="Reset">
<img src="images/space.gif" height="1" width="84">
</font>
</td>
<td><img src="images/space.gif" width="6" height="1"></td>
</tr>
<tr><td height="7"></td></tr>
</table>
</div>

Now, it's out here and hope this will help someone else down the road.

Todd
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top