Solved

Expand/Contract dynamic table to display detail

Posted on 2006-10-19
15
381 Views
Last Modified: 2008-02-26
Hello - here's what I'm trying to accomplish...I have a table in an asp page that displays the results of a dynamic recordset. The data is grouped in an sql statement by a destination like so...

"select acctnum, destination, count(widgets), sum(dohickeys) from tableXYZ group by accntum, destination"

I would like to have another recordset the same as above but that includes an origin to the detail, like so...

"select acctnum, origin, destination, count(widgets), sum(dohickeys) from tableXYZ group by accntum, destination"

I would then like the ability to expand/contract the results of the first recordset in the table and display the detail from the second recordset, like so...

 acctnum destination widgets  dohickeys
 + 1234         ATL            2             5
 - 2347         BOS           3              7
               origin     destination  widgets  dohickeys
                 CLE          BOS           2             2
                 PHL          BOS           1             5

and so on...

I assume this would have to be done with javascript (hence why I posted it here). I have done a little in js, so details would be appreciated. Thanks!
0
Comment
Question by:MilburnDrysdale
  • 8
  • 6
15 Comments
 
LVL 23

Expert Comment

by:rama_krishna580
ID: 17768565
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17768675
Hi MilburnDrysdale,

I'm assuming you know how to get the data to show up in the table formatted the way you want with your server side code and only need help on the Exapand/Contracting of the data.  If that isn't true, just let me know.

The first step is that we will add ID's to your rows.  The ID for each row from the first Recordset will be something like "ACCT"+Acct Number.  The ID for each row from the second recordset will be something like "Detail"+Acct+"_" + Counter - where counter is just that a counter that counts the number of rows.  Note that Acct Number and counter will have to be done with your server-side code.

Now we just add an onclick function to the rows from the first recordset.  <tr id="Acct100" onclick="showHide(this);"

And for the detail rows we set them to have a class of "detail"  the detail class has looks like this:
    .detail: {display:none;}

Obviously we now have to have a js function called showHide() for this to work.  I'll post that in the example.

Here's code for an example:
---------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
    <title>Untitled Page</title>
    <script type="text/javascript">
    function showHide(obj) {
      id = obj.id.substring(4);
      oTable = document.getElementById('table1');
      oRows = oTable.rows;
      for (i=0;i<oRows.length;i++) {
        if (oRows[i].id.split("_")[1] == id) {
          if (oRows[i].className == "detail") {
            oRows[i].className = "";
            obj.cells[0].innerHTML = "-";
          }
          else {
            oRows[i].className = "detail";
            obj.cells[0].innerHTML = "+";
          }
        }
      }
    }
    </script>
    <style type="text/css">
     .detail { display:none; }
    </style>
</head>
<body>
<table id="table1">
  <tr id="Acct123" onclick="showHide(this);">
    <td width="5">+</td><td>123</td><td>Test</td>
  </tr>
  <tr id="detail_123_1" class="detail">
    <td colspan="2">&nbsp;</td><td>detail</td><td>detail</td>
  </tr>
  <tr id="detail_123_2" class="detail">
    <td colspan="2">&nbsp;</td><td>detail</td><td>detail</td>
  </tr>
  <tr id="Acct200" onclick="showHide(this);">
    <td width="5">+</td><td>200</td><td>Test</td>
  </tr>
  <tr id="detail_200_1" class="detail">
    <td colspan="2">&nbsp;</td><td>detail</td><td>detail</td>
  </tr>
  <tr id="detail_200_2" class="detail">
    <td colspan="2">&nbsp;</td><td>detail</td><td>detail</td>
  </tr>

</table>

</body>
</html>
----------------------------------------------------------







Hope this helps,
Neal.
0
 

Author Comment

by:MilburnDrysdale
ID: 17769611
Neal - here's what I have so far (tortured attempt as js!)...

<title>West Bound Planning - Shipper History</title>
<script type="text/javascript">
    function showHide(obj) {
      id = obj.id.substring(4);
      oTable = document.getElementById('table1');
      oRows = oTable.rows;
      for (i=0;i<oRows.length;i++) {
        if (oRows[i].id.split("_")[1] == id) {
          if (oRows[i].className == "detail") {
            oRows[i].className = "";
            obj.cells[0].innerHTML = "-";
          }
          else {
            oRows[i].className = "detail";
            obj.cells[0].innerHTML = "+";
          }
        }
      }
    }
    </script>
      <style type="text/css">
      .detail { display:none; }
      </style>
<style type="text/css">
<!--
body,td,th {
      font-family: Arial, Helvetica, sans-serif;
      font-size: 12px;
}
body {
      margin-left: 0px;
      margin-top: 0px;
}
#Layer1 {
      position:absolute;
      left:283px;
      top:99px;
      width:75px;
      height:32px;
      z-index:1;
}
#Layer2 {
      position:absolute;
      left:121px;
      top:168px;
      width:35px;
      height:14px;
      z-index:2;
}
#Layer3 {
      position:absolute;
      left:5px;
      top:143px;
      width:463px;
      height:21px;
      z-index:2;
}
.style1 {
      font-size: 14px;
      font-weight: bold;
}
#Layer4 {
      position:absolute;
      left:5px;
      top:167px;
      width:469px;
      height:70px;
      z-index:3;
}
#Layer5 {
      position:absolute;
      left:4px;
      top:264px;
      width:1005px;
      height:52px;
      z-index:4;
}
-->
</style>
<script type="text/JavaScript">
<!--
function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
}

function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}

function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_swapImage() { //v3.0
  var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
   if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}

function MM_callJS(jsStr) { //v2.0
  return eval(jsStr)
}
//-->
</script>
</head>

<body onload="MM_preloadImages('images/home2.gif')">
<div id="Layer1"><a href="home_new.asp" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('home','','images/home2.gif',1)"><img src="images/home1.gif" alt="Home" name="home" width="70" height="30" border="0" id="home" /></a></div>
<div class="style1" id="Layer3">West Coast Inbound Planning - Shipper Activity (past 180 days)</div>
<div id="Layer4">
  <form id="form1" name="form1" method="post" action="">
    <label>
      <select name="TERMINAL" size="5" multiple="multiple" id="TERMINAL">
        <option>Select Destination Terminal(s) from List</option>
        <option value="GEG, LAS, LAX, PDX, PHX, SEA, SFO, YVR">All Terminals</option>
        <option value="GEG">GEG</option>
        <option value="LAS">LAS</option>
        <option value="LAX">LAX</option>
        <option value="PDX">PDX</option>
        <option value="PHX">PHX</option>
        <option value="SEA">SEA</option>
        <option value="SFO">SFO</option>
        <option value="YVR">YVR</option>
            </select>
    </label>
    <label>
    <input type="submit" name="Submit" value="Submit" />
    </label>
  </form>
</div>
<div id="Layer5">
  <table id="table1" border="1">
    <tr bgcolor="#CCCCCC">
      <td>&nbsp;</td>
      <td><div align="center"><strong>Bill To Name </strong></div></td>
      <td><div align="center"><strong>Destination</strong></div></td>
      <td><div align="center"><strong>Shipments</strong></div></td>
      <td><div align="center"><strong>Weight</strong></div></td>
      <td><div align="center"><strong>Date of Last Shipment </strong></div></td>
      <td><div align="center"><strong>Phone # </strong></div></td>
      <td><div align="center"><strong>Contact</strong></div></td>
    </tr>
    <% While ((Repeat1__numRows <> 0) AND (NOT rs1.EOF)) %>
      <tr>
        <td><a href="#"><img src="images/plus.gif" width="13" height="13" border="0" onclick="MM_callJS('showHide(this);')" /></a></td>
        <td><div align="left" id="BNM+(rs1.Fields.Item("BILLTONAME").Value)"><%=(rs1.Fields.Item("BILLTONAME").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("DEST").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("SHIPMENTS").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("WEIGHT").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("LASTSHIP").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("PHONE").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("CONT").Value)%></div></td>
      </tr>
      <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rs1.MoveNext()
Wend
%>
  </table>
</div>
<img src="images/TRHOME_NEW.gif" width="1015" height="140" />
</body>
</html>


Guess I'm not understanding how to get the detail id dynamically. The detail could sometimes be one row and another time 10 rows.
0
 

Author Comment

by:MilburnDrysdale
ID: 17769627
Apologies for all the extra DMX code...thought I hid that before copying...

Tim
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17769690
Hi Tim,

  I've got to run, but I'll check in on this a bit later.  What I need to see is your ASP code where you are looping through the two recordsets.  Then I'll be able to point out what code needs to go in to create the dynamic ID's

Neal.
0
 

Author Comment

by:MilburnDrysdale
ID: 17770987
Here is the pertinent code...I have the second recordset set up but do not have it in a table yet...

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/dbconnect.asp" -->
<%
Dim rs1__MMColParam
rs1__MMColParam = "1"
If (Request.Form("TERMINAL") <> "") Then
  rs1__MMColParam = Request.Form("TERMINAL")
End If
%>
<%
Dim rs1
Dim rs2
Dim rs1_numRows
Dim rs2_numRows

Set rs1 = Server.CreateObject("ADODB.Recordset")
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs1.ActiveConnection = MM_dbconnect_STRING
rs2.ActiveConnection = MM_dbconnect_STRING
rs1.Source = "SELECT BILLTONAME, DEST, SUM(SHIPMENTS) AS SHIPMENTS, SUM(WEIGHT) AS WEIGHT, MAX(LASTSHIP) AS LASTSHIP, SUBSTR(CMPHON, 1,3)||' - '||SUBSTR(CMPHON,4,3)||' - '||SUBSTR(CMPHON, 7,4) AS PHONE, CMCONT AS CONT  FROM TAFWORK.FBLANES4  WHERE SHIPMENTS>5 AND DEST IN ('" + Replace(rs1__MMColParam, ", ", "','") + "') GROUP BY BILLTONAME, DEST, SUBSTR(CMPHON, 1,3)||' - '||SUBSTR(CMPHON,4,3)||' - '||SUBSTR(CMPHON, 7,4), CMCONT ORDER BY SUM(SHIPMENTS) DESC"
rs2.Source = "SELECT BILLTONAME, ORIG, DEST, SHIPMENTS, WEIGHT, LASTSHIP, SUBSTR(CMPHON, 1,3)||' - '||SUBSTR(CMPHON,4,3)||' - '||SUBSTR(CMPHON, 7,4) AS PHONE, CMCONT AS CONT  FROM TAFWORK.FBLANES4  WHERE SHIPMENTS>5 AND DEST IN ('" + Replace(rs1__MMColParam, ", ", "','") + "') ORDER BY SHIPMENTS DESC"
rs1.CursorType = 0
rs2.CursorType = 0
rs1.CursorLocation = 2
rs2.CursorLocation = 2
rs1.LockType = 1
rs2.LockType = 1
rs1.Open()
rs2.Open()

rs1_numRows = 0
rs2_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rs1_numRows = rs1_numRows + Repeat1__numRows

Dim Repeat2__numRows
Dim Repeat2__index

Repeat2__numRows = -1
Repeat2__index = 0
rs2_numRows = rs2_numRows + Repeat2__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>West Bound Planning - Shipper History</title>
<script type="text/javascript">
    function showHide(obj) {
      id = obj.id.substring(4);
      oTable = document.getElementById('table1');
      oRows = oTable.rows;
      for (i=0;i<oRows.length;i++) {
        if (oRows[i].id.split("_")[1] == id) {
          if (oRows[i].className == "detail") {
            oRows[i].className = "";
            obj.cells[0].innerHTML = "-";
          }
          else {
            oRows[i].className = "detail";
            obj.cells[0].innerHTML = "+";
          }
        }
      }
    }
    </script>
      <style type="text/css">
      .detail { display:none; }
      </style>

</head>

<body onload="MM_preloadImages('images/home2.gif')">
<div id="Layer1"><a href="home_new.asp" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('home','','images/home2.gif',1)"><img src="images/home1.gif" alt="Home" name="home" width="70" height="30" border="0" id="home" /></a></div>
<div class="style1" id="Layer3">West Coast Planning </div>
<div id="Layer4">
  <form id="form1" name="form1" method="post" action="">
    <label>
      <select name="TERMINAL" size="5" multiple="multiple" id="TERMINAL">
        <option>Select Destination Terminal(s) from List</option>
        <option value="GEG, LAS, LAX, PDX, PHX, SEA, SFO, YVR">All Terminals</option>
        <option value="GEG">GEG</option>
        <option value="LAS">LAS</option>
        <option value="LAX">LAX</option>
        <option value="PDX">PDX</option>
        <option value="PHX">PHX</option>
        <option value="SEA">SEA</option>
        <option value="SFO">SFO</option>
        <option value="YVR">YVR</option>
            </select>
    </label>
    <label>
    <input type="submit" name="Submit" value="Submit" />
    </label>
  </form>
</div>
<div id="Layer5">
  <table id="table1" border="1">
    <tr bgcolor="#CCCCCC">
      <td>&nbsp;</td>
      <td><div align="center"><strong>Bill To Name </strong></div></td>
      <td><div align="center"><strong>Destination</strong></div></td>
      <td><div align="center"><strong>Shipments</strong></div></td>
      <td><div align="center"><strong>Weight</strong></div></td>
      <td><div align="center"><strong>Date of Last Shipment </strong></div></td>
      <td><div align="center"><strong>Phone # </strong></div></td>
      <td><div align="center"><strong>Contact</strong></div></td>
    </tr>
    <% While ((Repeat1__numRows <> 0) AND (NOT rs1.EOF)) %>
      <tr>
        <td><a href="#"><img src="images/plus.gif" width="13" height="13" border="0" onclick="MM_callJS('showHide(this);')" /></a></td>
        <td><div align="left" id="BNM+(rs1.Fields.Item("BILLTONAME").Value)"><%=(rs1.Fields.Item("BILLTONAME").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("DEST").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("SHIPMENTS").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("WEIGHT").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("LASTSHIP").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("PHONE").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("CONT").Value)%></div></td>
      </tr>
      <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rs1.MoveNext()
Wend
%>
  </table>
</div>
<img src="images/TRHOME_NEW.gif" width="1015" height="140" />
</body>
</html>
<%
rs1.Close()
Set rs1 = Nothing
%>
<%
rs2.Close()
Set rs2 = Nothing
%>
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17771361
I had a sample ready for you, but then realized we were going to have a problem.  That problem is that the order of the two recordsets is not exactly the same, and in any case is not the linking field between the recordsets.  This means that we can't guarantee the recordsets will line up properly to put the detail records under the main record in the table.  I was going to create a single recordset with all of the information there, but you are using grouping in your first recordset, so this won't work (or at least I can't think of how I would do it).  So we need to change the order of your recordsets so that things will line up properly.  The first field in the order clause will need to be the BILLTONAME field since that is the field that will link the two recordsets together.  We could do it without doing this but it would mean having to go through the entire rs2 recordset for every record in the rs1 recordset.  If we change the order we will be much more efficient.

By the way, I can't test this without setting up a database and some data.  I believe this will work as is, but it is untested, there may be a bug or two to be worked out.

Here are the relevant parts of the code.  I've commented within the code.

Neal.

This is where you setup your SQL statements
---------------------------------------------------------------------------
' Here I've just changed to order cluase of both recordsets to ensure they will line up properly.
rs1.Source = "SELECT BILLTONAME, DEST, SUM(SHIPMENTS) AS SHIPMENTS, SUM(WEIGHT) AS WEIGHT, MAX(LASTSHIP) AS LASTSHIP, SUBSTR(CMPHON, 1,3)||' - '||SUBSTR(CMPHON,4,3)||' - '||SUBSTR(CMPHON, 7,4) AS PHONE, CMCONT AS CONT  FROM TAFWORK.FBLANES4  WHERE SHIPMENTS>5 AND DEST IN ('" + Replace(rs1__MMColParam, ", ", "','") + "') GROUP BY BILLTONAME, DEST, SUBSTR(CMPHON, 1,3)||' - '||SUBSTR(CMPHON,4,3)||' - '||SUBSTR(CMPHON, 7,4), CMCONT ORDER BY BILLTONAME SUM(SHIPMENTS) DESC"

rs2.Source = "SELECT BILLTONAME, ORIG, DEST, SHIPMENTS, WEIGHT, LASTSHIP, SUBSTR(CMPHON, 1,3)||' - '||SUBSTR(CMPHON,4,3)||' - '||SUBSTR(CMPHON, 7,4) AS PHONE, CMCONT AS CONT  FROM TAFWORK.FBLANES4  WHERE SHIPMENTS>5 AND DEST IN ('" + Replace(rs1__MMColParam, ", ", "','") + "') ORDER BY BILLTONAME SHIPMENTS DESC"

-------------------------------------
I modified the showHide() function to accept the id of the row rathe than the row object itself.  This makes the code cleaner in the actual loop.
-------------------------------------
<script type="text/javascript">
    function showHide(id) {
      obj = document.getElementById(id");  
      id = id.substring(4);  // We don't care about the first 4 letters in the ID string
      oTable = document.getElementById('table1');  
      oRows = oTable.rows;
      for (i=0;i<oRows.length;i++) {
        if (oRows[i].id.split("_")[1] == id) {
          if (oRows[i].className == "detail") {
            oRows[i].className = "";
            obj.cells[0].innerHTML = "-";
          }
          else {
            oRows[i].className = "detail";
            obj.cells[0].innerHTML = "+";
          }
        }
      }
    }
</script>

---------------------------------------------------------------
Here's the actual Loop through the recordsets to display the data.
---------------------------------------------------------------
    <%
      ' Here I've added a new variable to store the BILLTONAME field because we will
      ' be using it frequently in the next area.
      dim BillToName
      While ((Repeat1__numRows <> 0) AND (NOT rs1.EOF))
      BillToName = rs1.Fields.item("BILLTONAME").Value
      ' Notice the ID of the tr below.  The ID will be "Acct" + the BILLTONAME field
      ' we will use this when calling the showHide function.
      %>
      <tr id="Acct<%=BillToName%>">
        <%
        ' Here we are checking to see if rs2 has at least one matching record with rs1.
        ' If so then it should be the current record.  This is why the order of the
        ' recordsets is so important.  If there is a matching record we show a cell with
        ' a plus sign in it indicating that there are child records.  If not we show
        ' a cell with just a blank space in it.  Notice the onclick event of the <td> that
        ' holds the plus sign.  This makes it so that the showHide function will be called
        ' only if there are detail records to be displayed.
        if not rs2.EOF then
          if rs2.Fields.item("BILLTONAME").Value = rs1.Fields.item("BILLTONAME").Value
            response.Write("<td onclick=""showHide('Acct" + BillToName + "';""><div align=""center"">+</div></td>"
          else
            response.Write("<td>&nbsp;</td>")
          end if
        else
          response.Write("<td>&nbsp;</td>")
        end if
        %>
        <td><a href="#"><img src="images/plus.gif" width="13" height="13" border="0" onclick="MM_callJS('showHide(this);')" /></a></td>
        <td><div align="left" id="BNM<%=BillToName%>"><%=BillToName%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("DEST").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("SHIPMENTS").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("WEIGHT").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("LASTSHIP").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("PHONE").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("CONT").Value)%></div></td>
      </tr>
      <%
      'Here sw set the Repeat2__index to 0.  We will reset this for each loop through rs1.
      'We then start a loop through rs2 so long as rs2's BILLTONAME matches the current rs1.BILLTONAME
      'We increment the Repeat2__index by 1 and create a new row.  This row is for the detail item
      'Notice how the id is generated it is Detail_the billtoname_Repeat2__index.  This gives each
      'row a uniqueID, but the id can be broken apart by looking for the _ character, which is
      'exactly what the showHide function does.
      Repeat2__index = 0
      While rs2.Fields.item("BILLTONAME").value = BillToName and not rs2.EOF
        Repeat2__index = Repeat2__index + 1
        %>
        <tr id="Detail_<%=BillToName%>_<%=Repeat2__index%>" class="detail">
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td><div align="center"><%=(rs2.Fields.Item("ORIG").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("DEST").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("SHIPMENTS").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("WEIGHT").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("LASTSHIP").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("PHONT").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("CONT").Value)%></div></td>
        </tr>
        <%
        rs2.MoveNext()
     Wend
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rs1.MoveNext()
Wend
%>



0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:MilburnDrysdale
ID: 17773097
Neal - I think I have everything set up properly, but I am getting the following error;

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

and it is pointing to this line;

 While rs2.Fields.item("BILLTONAME").value = BillToName and not rs2.EOF

Here is all the recordset code, just in case;

<table id="table1" border="1">
    <tr bgcolor="#CCCCCC">
      <td>&nbsp;</td>
      <td><div align="center"><strong>Bill To Name </strong></div></td>
      <td><div align="center"><strong>Destination</strong></div></td>
      <td><div align="center"><strong>Shipments</strong></div></td>
      <td><div align="center"><strong>Weight</strong></div></td>
      <td><div align="center"><strong>Date of Last Shipment </strong></div></td>
      <td><div align="center"><strong>Phone # </strong></div></td>
      <td><div align="center"><strong>Contact</strong></div></td>
    </tr>
    <% dim BillToName
      While ((Repeat1__numRows <> 0) AND (NOT rs1.EOF))
      BillToName = rs1.Fields.item("BILLTONAME").Value
        %>
      <tr id="Acct<%=BillToName%>">
     <%
       if not rs2.EOF then
          if rs2.Fields.item("BILLTONAME").Value = rs1.Fields.item("BILLTONAME").Value then
            response.Write("<td onclick=""showHide('Acct" + BillToName + "';"")><div align=""center"">+</div></td>")
          else
            response.Write("<td>&nbsp;</td>")
          end if
        else
          response.Write("<td>&nbsp;</td>")
        end if
        %>
        <td><div align="left" id="BNM<%=BillToName%>"><%=BillToName%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("DEST").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("SHIPMENTS").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("WEIGHT").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("LASTSHIP").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("PHONE").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("CONT").Value)%></div></td>
      </tr>
      <% Repeat2__index = 0
      While rs2.Fields.item("BILLTONAME").value = BillToName and not rs2.EOF
        Repeat2__index = Repeat2__index + 1
        %>
        <tr id="Detail_<%=BillToName%>_<%=Repeat2__index%>" class="detail">
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td><div align="center"><%=(rs2.Fields.Item("ORIG").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("DEST").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("SHIPMENTS").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("WEIGHT").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("LASTSHIP").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("PHONE").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("CONT").Value)%></div></td>
        </tr>
        <%
        rs2.MoveNext()
     Wend
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rs1.MoveNext()
Wend
%>
  </table>

Tim
0
 
LVL 19

Accepted Solution

by:
nschafer earned 500 total points
ID: 17773452
OK, I have two thoughts on this.  One I know will fix it, but if you'd be willing to test the other I'd find it interesting to know.

Here's the one I'm not sure about.
Reverse the order of the while statement:
-------------------------------------------------------
     While not rs2.eof and rs2.Fields.item("BILLTONAME").value = BillToName
-------------------------------------------------------
Some languages dont check the second part of an and statement if the first part is false.  I'm not sure if ASP works this way or not, that's why I'd be interested to see if this one works.  It's also cleaner then the method I'm sure will work.


Heres the one that I'm sure of:
-------------------------------------------------------
if not rs2.eof then
     While rs2.Fields.item("BILLTONAME").value = BillToName and not rs2.EOF
        Repeat2__index = Repeat2__index + 1
        %>
        <tr id="Detail_<%=BillToName%>_<%=Repeat2__index%>" class="detail">
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td><div align="center"><%=(rs2.Fields.Item("ORIG").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("DEST").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("SHIPMENTS").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("WEIGHT").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("LASTSHIP").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("PHONE").Value)%></div></td>
          <td><div align="center"><%=(rs2.Fields.Item("CONT").Value)%></div></td>
        </tr>
        <%
        rs2.MoveNext()
     Wend
end if
--------------------------------------------
Here we just wrap the whole section in an if statement to ensure we don't try if we are at the eof marker.

Neal.
0
 

Author Comment

by:MilburnDrysdale
ID: 17773711
Neal - getting the same error with either statement...

"ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17773842
Hmm......

At the same point in the scipt?

I'll go ahead and put some data together so I can test this.

Neal.
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17774563
I've put together some data and came up with this to fix the error:
---------------
      if not rs2.eof then
        dim BillToName2
        BillToName2 = rs2.Fields.item("BILLTONAME").value
        While BillToName2  = BillToName
          Repeat2__index = Repeat2__index + 1
          %>
          <tr id="Detail_<%=BillToName%>_<%=Repeat2__index%>" class="detail">
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td><div align="center"><%=(rs2.Fields.Item("DEST").Value)%></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("SHIPMENTS").Value)%></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("WEIGHT").Value)%></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("LASTSHIP").Value)%></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("PHONE").Value)%></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("CONT").Value)%></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("ORIG").Value)%></div></td>
          </tr>
          <%
          rs2.MoveNext()
          if rs2.EOF then
            BillToName2 = ""
          else
            BillToName2 = rs2.Fields.item("BILLTONAME").value
          end if
       Wend
----------------------------------------------

However, I'm now seeing an issue with the grouping problem, and a problem I had with my logic.  I originally thought the data was coming from seperate tables.  Looking at it more closely I see that it is coming from one table.  So there is no chance of having a record in rs1 without at least one matching record in rs2.  This means that I don't need to check for that as I was doing.  So I'll pull out that part of the script.  

The grouping problem is a bit more serious, it means that we need to check not just the BILLTONAME, but also the DEST to make sure the detail record shows up in the right place.  We probably want to check all of the grouping fields.

This also means that the BILLTONAME will not be a unique identifier for the rows in the rs1.  This means that the way the selection is being made won't work.  I'll modify the script to handle this, it will work very differently, perhaps in an easier manner.

I'll get back to you with this shortly.
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17775184
OK, I think I now have more along the lines of what you are after.  After looking at your code some more I determined that the way I was grouping data wasn't going to work.  It appears you are trying to sort rs1 based on the sum of the shipments as the primary sort, since there is no way to match this to a value in rs2, I can't assume the records will be sorted properly so that as you move through rs1, the records in rs2 will be in the same order which is what the original script I did assumed. I went back to your original SQL queries.

Instead of making that assumption, I now loop through rs2 for each record in rs1 and add the matching records to the table.  This is not as efficient as we have to loop through rs2 once for each record in rs1, but it allows us to get you the data sorted and grouped properly.  On long recordsets this could potentially be an issue though.  I am comparing all of the fields used in the grouping of rs1 to make sure that the correct detail record goes with the correct summary record.

I completely rewrote the function for showHide.  It does not use ID's anymore.  Instead it is useing the DOM nextSibling object along with a className that must be defined for each detail row.  Since we had a classname on the row to hide it in any case this makes life very easy.  This is actually more efficient than the original version as it only looks at the rows that are after the row you clicked on and stops looking when it finds one without the proper className.  The old version looked through every row in the table.  The function now depends on two css classes being present.  detailHide to hide the row and detailShow to show the row.

Here's the new function and css
-------------------------------------------------------------------
<script type="text/javascript">
    function showHide(obj) {
      var brk = 0;
      var obj1 = obj;
      while (brk == 0) {
        if (obj1 != null) obj1 = obj1.nextSibling;
        else brk = 1;
        if (obj1 != null) {
          if (obj1.nodeName == "TR") {
            if (obj1.className == "detailHide") {
              obj1.className = "detailShow";  // row hidden so show it
              obj.cells[0].innerHTML = "-";
            }
            else {
              if(obj1.className == "detailShow") {
                obj1.className = "detailHide"; // row shown so hide it
                obj.cells[0].innerHTML = "+";
              }
              else brk = 1; // not a detail record
            }
          }
        }
      }
    }
</script>
<style type="text/css">
  .detailHide { display:none; }
  .detailShow {  }
</style>
-------------------------------------------------------------------

and Here is the new loops for the table.
-------------------------------------------------------------------
   <%
    dim BillToName, Dest, Phone, Contact
    dim BillToName2, Dest2, Phone2, Contact2
    While ((Repeat1__numRows <> 0) AND (NOT rs1.EOF))
      BillToName = rs1.Fields.item("BILLTONAME").Value
      Dest = rs1.Fields.item("DEST").value
      Phone = rs1.Fields.item("PHONE").value
      Contact = rs1.Fields.item("CONT").value
      %>
      <tr onclick="showHide(this);">
        <td><div align="center">+</div></td>
        <td><div align="left" id="BNM<%=BillToName%>"><%=BillToName%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("DEST").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("SHIPMENTS").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("WEIGHT").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("LASTSHIP").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("PHONE").Value)%></div></td>
        <td><div align="center"><%=(rs1.Fields.Item("CONT").Value)%></div></td>
        <td>&nbsp;</td>
      </tr>
      <%
      rs2.moveFirst
      do until rs2.eof
        BillToName2 = rs2.Fields.item("BILLTONAME").value
        Dest2 = rs2.Fields.item("DEST").value
        Phone2 = rs2.Fields.item("PHONE").value
        Contact2 = rs2.Fields.item("CONT").value
        if BillToName2 = BillToName and Dest2 = Dest and Phone2 = Phone and Contact2 = Contact then
          %>
          <tr class="detailHide">
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td><div align="center"><%=Dest2 %></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("SHIPMENTS").Value)%></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("WEIGHT").Value)%></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("LASTSHIP").Value)%></div></td>
            <td><div align="center"><%=Phone2 %></div></td>
            <td><div align="center"><%=Contact2 %></div></td>
            <td><div align="center"><%=(rs2.Fields.Item("ORIG").Value)%></div></td>
          </tr>
          <%
        end if
        rs2.MoveNext()
      loop
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      rs1.MoveNext()
   Wend
   %>
-------------------------------------------------------------------


Let me know if this causes you any problems.

Neal.
0
 

Author Comment

by:MilburnDrysdale
ID: 17786288
Neal - suhhh weeet!! This is EXACTLY what I was looking for and it works great! Looping through the recordsets is not a problem for this app since the underlying tables are not huge. It loads pretty quickly. Thanks a ton for the help...wish I could give you more than 500 points!
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17786490
No problem,
  Glad I could help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Auto Submit on dropdown box 3 35
Detecting robots? 5 34
JavaScript error 1 33
Remove Protocol if at start of string? 3 11
Today I would like to talk about localizing (Internationalization) JavaScript applications. Introduction When creating an application that is going to be used by many people around the globe, it is important to remember that not everyone speak…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now