?
Solved

Routine to total-up user-changed datagrid column in asp.net

Posted on 2004-11-17
11
Medium Priority
?
265 Views
Last Modified: 2008-03-17
I have an asp.net datagrid with a variable/unknown number of rows. All the columns except one are read-only. The exception is a columnn where users can amend the figures. I want to have a client-side routine which re-calculates the totals but I don't have a clue about javascript! Can anyone help ?

0
Comment
Question by:Mamine
  • 7
  • 3
11 Comments
 
LVL 19

Expert Comment

by:dakyd
ID: 12607897
Will something like this help?  I don't know what your data grid spits out as a table, but the script below should be a start.  It assumes that there's a text input in the column that can be modified, and you can change the column that needs to be totalled by chaning the "column" variable near the top.  It also assumes that the first row is a header and the last row is for totals, so it doesn't try to include those rows in the sum.    I used an id of "theDataGrid", but odds are you're using something else, so that'll have to change as well.  Hope that helps, but if you're still a bit lost, post the html that your data grid produces, and we'll go from there.

<html>
<head>
<script type="text/javascript">
// stores which column has the values to be totalled
// note, first colum = 0, second = 1, third = 2, ...
column = 1;

window.onload = init;

function init()
{
  tbl = document.getElementById("theDataGrid");
  for (var i = 1, n = tbl.rows.length - 1; i < n; i ++)
  {
    var currCell = tbl.rows[i].cells[column];
    currCell.getElementsByTagName("input")[0].onblur = updateTotal;
  }
}

function updateTotal()
{
  var totalAmt = 0;
  for (var i = 1, n = tbl.rows.length - 1; i < n; i ++)
  {
    var currCell = tbl.rows[i].cells[column];
    var currInp = currCell.getElementsByTagName("input")[0];
    if (currInp.value != "")
      totalAmt += parseFloat(currInp.value);
  }
  document.getElementById("total").innerHTML = totalAmt;
}
</script>
</head>

<body>
<table id="theDataGrid">
  <tr>
    <th>Product</th>
    <th>Cost</th>
  </tr>
  <tr>
    <td>Prod 1</td>
    <td><input type="text" /></td>
  </tr>
  <tr>
    <td>Prod 2</td>
    <td><input type="text" /></td>
  </tr>
  <tr>
    <td>Prod 3</td>
    <td><input type="text" /></td>
  </tr>
  <tr>
    <td>Prod 4</td>
    <td><input type="text" /></td>
  </tr>
  <tr>
    <td>Total</td>
    <td id="total"></td>
  </tr>
</table>
</body>
</html>
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 12611929
If u could post the datagrid it would be easier for us.

Thanks
0
 

Author Comment

by:Mamine
ID: 12612575
Many thanks dakyd this looks really great! A couple of questions to help my understanding ...

1. What does the [0] refer to in here: getElementsByTagName("input")[0]

2. If I want to read a value in another column that is readonly (eg to calculate horizontal subtotals as well, multiplying the above column by a quantity column), how do I identify the read-only/quantity col ? asp.net throws out these other columns without any tag name except the <td></td> its in so I can't use getElementsByTagName. Is there another way of identifying cells ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Mamine
ID: 12612740
Here is a sample datagrid:

<table class="Grid" cellspacing="0" align="Left" rules="rows" border="1" id="ucContent_DbGrid_Ingredients" style="width:100%;border-collapse:collapse;">
<caption align="Top">Items</caption>
<tr class="GridHdr">
<td align="Left">Item</td>
<td align="Center">Quantity</td>
<td align="Right" style="width:6em;">Cost1</td>
<td align="Right" style="width:6em;">Cost2</td>
<td align="Right">Your cost</td>
<td align="Right" style="width:6em;">CalcCost</td>
</tr>
<tr class="GridItem">
<td align="Left"><a href="ItemDetails.aspx?id=1392">Item1</a></td>
<td nowrap="nowrap" align="Center">1</td>
<td nowrap="nowrap" align="Right">10.000</td>
<td nowrap="nowrap" align="Right">0.052</td>
<td nowrap="nowrap" align="Right"><input type="text" maxlength="7" size="7" tabindex="2" style="TEXT-ALIGN: right" onblur="recalc"></td>
<td nowrap="nowrap" align="Right">0.052</td>
</tr>
<tr class="GridItem">
<td align="Left"><a href="ItemDetails.aspx?id=2918">Item2</a></td>
<td nowrap="nowrap" align="Center">1</td>
<td nowrap="nowrap" align="Right">900.000</td><td nowrap="nowrap" align="Right">&nbsp;</td>
<td nowrap="nowrap" align="Right"><input type="text" maxlength="7" size="7" tabindex="2" style="TEXT-ALIGN: right" onblur="recalc"></td>
<td nowrap="nowrap" align="Right">900.000</td>
</tr>
<tr class="GridItem">
<td align="Left"><a href="ItemDetails.aspx?id=3074">Item3</a></td>
<td nowrap="nowrap" align="Center">3</td>
<td nowrap="nowrap" align="Right">100.000</td>
<td nowrap="nowrap" align="Right"><input type="text" maxlength="7" size="7" tabindex="2" style="TEXT-ALIGN: right" onblur="recalc"></td>
<td nowrap="nowrap" align="Right">300.000</td>
</tr>
<tr class="GridFtr" align="Left" valign="Top">
<td align="Left" valign="Top">&nbsp;</td>
<td nowrap="nowrap" valign="Top">&nbsp;</td>
<td>&nbsp;</td>
<td align="Right" valign="Top">&nbsp;</td>
<td align="Right" valign="Top">&nbsp;</td>
<td align="Right" valign="Top">Totals</td>
<td nowrap="nowrap" align="Right" valign="Top">1,200.052</td>
</tr>
</table>

This should come out as:

Item     Quantity    Cost1    Cost2  Your Cost    CalcCost
Item1    1             10         0.052                       0.052
Item2    1             900                                      900            
Item3    3             100                                      300            
                                                    Totals        1200.052

Your Cost is the column that the user can change. CalcCost needs to be a column of subtotals with a calculated grand total
0
 

Author Comment

by:Mamine
ID: 12612839
The asp.net datagrid looks like this before it is rendered:

<asp:datagrid id="DbGrid_Ingredients" runat="server" width="100%" gridlines="Horizontal" selectedindex="0" autogeneratecolumns="False" horizontalalign="Left" caption="Ingredients" captionalign="Top" pagesize="5" cssclass="Grid" showfooter="True" itemstyle-cssclass="GridItem" headerstyle-cssclass="GridHdr" pagerstyle-cssclass="GridFtr">
      <footerstyle horizontalalign="Left" cssclass="GridHdr" verticalalign="Top"></footerstyle>
      <itemstyle cssclass="GridItem"></itemstyle>
      <headerstyle cssclass="GridFtr"></headerstyle>
      <columns>
            <asp:hyperlinkcolumn datanavigateurlfield="ItemID" datanavigateurlformatstring="ItemDetails.aspx?id={0}"
                  datatextfield="Item" headertext="Item">
                  <headerstyle horizontalalign="Left"></headerstyle>
                  <itemstyle horizontalalign="Left"></itemstyle>
                  <footerstyle horizontalalign="Left" verticalalign="Top"></footerstyle>
            </asp:hyperlinkcolumn>
            <asp:boundcolumn datafield="Quantity" readonly="True" headertext="Quantity&lt;BR&gt;needed">
                  <headerstyle horizontalalign="Center"></headerstyle>
                  <itemstyle wrap="False" horizontalalign="Center"></itemstyle>
                  <footerstyle wrap="False" verticalalign="Top"></footerstyle>
            </asp:boundcolumn>
            <asp:boundcolumn datafield="Cost1" readonly="True" headertext="Bazaar&lt;BR&gt;Cost" dataformatstring="{0:N3}">
                  <headerstyle horizontalalign="Right" width="6em"></headerstyle>
                  <itemstyle wrap="False" horizontalalign="Right"></itemstyle>
                  <footerstyle horizontalalign="Right" verticalalign="Top"></footerstyle>
            </asp:boundcolumn>
            <asp:boundcolumn datafield="Cost2" readonly="True" headertext="NPC&lt;BR&gt;Cost" dataformatstring="{0:N3}">
                  <headerstyle horizontalalign="Right" width="6em"></headerstyle>
                  <itemstyle wrap="False" horizontalalign="Right"></itemstyle>
                  <footerstyle horizontalalign="Right" verticalalign="Top"></footerstyle>
            </asp:boundcolumn>
            <asp:templatecolumn headertext="Your cost" footertext="Totals">
                  <headerstyle horizontalalign="Right"></headerstyle>
                  <itemstyle wrap="False" horizontalalign="Right"></itemstyle>
                  <itemtemplate>
                        <input type="text" maxlength="7" size="7" tabindex="2" style="TEXT-ALIGN: right" onblur="recalc">
                  </itemtemplate>
                  <footerstyle horizontalalign="Right" verticalalign="Top"></footerstyle>
            </asp:templatecolumn>
            <asp:boundcolumn readonly="True" headertext="CalcCost" dataformatstring="{0:N3}">
                  <headerstyle horizontalalign="Right" width="6em"></headerstyle>
                  <itemstyle wrap="False" horizontalalign="Right"></itemstyle>
                  <footerstyle wrap="False" horizontalalign="Right" verticalalign="Top"></footerstyle>
            </asp:boundcolumn>
      </columns>
      <pagerstyle cssclass="GridFtr"></pagerstyle>
</asp:datagrid>
0
 
LVL 19

Accepted Solution

by:
dakyd earned 2000 total points
ID: 12617887
First, to answer your questions:
1. What does the [0] refer to in here: getElementsByTagName("input")[0]
  getElementsByTagName returns an array of all the elements that have the tag name that you specify.  In this case, it'll return an array of all the <input> tags within the cell that we specified.  There should only be one, so the array's only entry should be the first one, which has an index of 0.  Hence, getElementsByTagName("input")[0].

2. If I want to read a value in another column that is readonly (eg to calculate horizontal subtotals as well, multiplying the above column by a quantity column), how do I identify the read-only/quantity col ? asp.net throws out these other columns without any tag name except the <td></td> its in so I can't use getElementsByTagName. Is there another way of identifying cells ?
  Every row contains an array called cells, which has all of the cells within that row.  So for example, someRow.cells[1] will have the second cell in someRow.  That's why we specify the column which has the input that the user can change.  If we know this in advance, we can use it to our advantage to use the value of someRow.cells[yourCostCol] to do our calculations.  We can also do something similar to identify the quantity column, or any other column, for that matter.


Now, I think what you're looking for is something like this.  I had to clean up the sample table you posted, there were a few extra cells here and there, so it wasn't displaying properly.  In order to do the horizontal sums, you have tell the script which columns contain the quantity, cost1, cost2, and yourCost.  Those variables are right near the top, so you can easily change the script if you decide to change the layout of your datagrid.  One other thing I did was to remove the onblur handlers you put on each input.  You weren't actually calling functions, so they just threw javascript errors.  The init() function, however, will ensure that the inputs all recalculate the subtotals and totals whenever you move the focus away from the input.  Hope that helps.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<html>
<head>
<script type="text/javascript">
// stores which columns have the values to be totalled
// note, first colum = 0, second = 1, third = 2, ...
qtyCol = 1;
cost1Col = 2;
cost2Col = 3;
yourCostCol = 4;

window.onload = init;

function init()
{
  tbl = document.getElementById("ucContent_DbGrid_Ingredients");
  for (var i = 1, n = tbl.rows.length - 1; i < n; i ++)
  {
    var currCell = tbl.rows[i].cells[yourCostCol];
    currCell.getElementsByTagName("input")[0].onblur = updateTotal;
  }
}

function updateTotal()
{
  var totalAmt = 0;
  for (var i = 1, n = tbl.rows.length - 1; i < n; i ++)
  {
    var currRow = tbl.rows[i];
    var currCell = currRow.cells[yourCostCol];
    var currInp = currCell.getElementsByTagName("input")[0];
    var rowCost = 0;
    if (!isNaN(parseFloat(currRow.cells[cost1Col].innerHTML)))
      rowCost += parseFloat(currRow.cells[cost1Col].innerHTML);
    if (!isNaN(parseFloat(currRow.cells[cost2Col].innerHTML)))
      rowCost += parseFloat(currRow.cells[cost2Col].innerHTML)
    if (currInp.value != "")
    {
      rowCost += parseFloat(currInp.value);
      rowCost = parseFloat(currRow.cells[qtyCol].innerHTML) * rowCost.toFixed(3);
      currRow.cells[currRow.cells.length - 1].innerHTML = rowCost.toFixed(3);
    }
    totalAmt += parseFloat(currRow.cells[currRow.cells.length - 1].innerHTML);
  }
  document.getElementById("total").innerHTML = totalAmt.toFixed(3);
}
</script>
</head>

<body>
<table class="Grid" cellspacing="0" align="Left" rules="rows" border="1" id="ucContent_DbGrid_Ingredients" style="width:100%;border-collapse:collapse;">
<caption align="Top">Items</caption>
<tr class="GridHdr">
  <td align="Left">Item</td>
  <td align="Center">Quantity</td>
  <td align="Right" style="width:6em;">Cost1</td>
  <td align="Right" style="width:6em;">Cost2</td>
  <td align="Right">Your cost</td>
  <td align="Right" style="width:6em;">CalcCost</td>
</tr>
<tr class="GridItem">
  <td align="Left"><a href="ItemDetails.aspx?id=1392">Item1</a></td>
  <td nowrap="nowrap" align="Center">1</td>
  <td nowrap="nowrap" align="Right">10.000</td>
  <td nowrap="nowrap" align="Right">0.052</td>
  <td nowrap="nowrap" align="Right"><input type="text" maxlength="7" size="7" tabindex="2" style="TEXT-ALIGN: right"></td>
  <td nowrap="nowrap" align="Right">10.052</td>
</tr>
<tr class="GridItem">
  <td align="Left"><a href="ItemDetails.aspx?id=2918">Item2</a></td>
  <td nowrap="nowrap" align="Center">1</td>
  <td nowrap="nowrap" align="Right">900.000</td>
  <td nowrap="nowrap" align="Right">&nbsp;</td>
  <td nowrap="nowrap" align="Right"><input type="text" maxlength="7" size="7" tabindex="2" style="TEXT-ALIGN: right"></td>
  <td nowrap="nowrap" align="Right">900.000</td>
</tr>
<tr class="GridItem">
  <td align="Left"><a href="ItemDetails.aspx?id=3074">Item3</a></td>
  <td nowrap="nowrap" align="Center">3</td>
  <td nowrap="nowrap" align="Right">100.000</td>
  <td>&nbsp;</td>
  <td nowrap="nowrap" align="Right"><input type="text" maxlength="7" size="7" tabindex="2" style="TEXT-ALIGN: right"></td>
  <td nowrap="nowrap" align="Right">300.000</td>
</tr>
<tr class="GridFtr" align="Left" valign="Top">
  <td align="Left" valign="Top">&nbsp;</td>
  <td nowrap="nowrap" valign="Top">&nbsp;</td>
  <td align="Right" valign="Top">&nbsp;</td>
  <td align="Right" valign="Top">&nbsp;</td>
  <td align="Right" valign="Top">Totals</td>
  <td nowrap="nowrap" align="Right" valign="Top" id="total">1,210.052</td>
</tr>
</table>
</body>
</html>
0
 

Author Comment

by:Mamine
ID: 12623280
This is absolutely wonderful! ... except I still have a small problem ... the parseFloat(currInp.value); ALWAYS comes out as NaN ... if I look at the html source, the input value='1234.567' (or whatever) rather than value=1234.567, so I'm guessing it can't convert from a string to a float automatically ?
0
 

Author Comment

by:Mamine
ID: 12623292
.... or is it cos its single quotes rather than double quotes ? (the values read from the non-input table cells come out fine)
0
 

Author Comment

by:Mamine
ID: 12623310
... scrap that ... my boo-boo ... dropped the .value off the end - duh!
0
 

Author Comment

by:Mamine
ID: 12623322
Works like a dream now!!!
0
 
LVL 19

Expert Comment

by:dakyd
ID: 12627376
Great, glad to hear you got what you wanted.  Thanks for the points.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: DanRollins
This article describes a JavaScript program that creates a maze made of hexagonal cells.  In Part 2 (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/A_7850-Hex-Maze-Part-2.html), we'll extend the program by adding a depth-…
Having worked on larger scale sites, we found out that you are bound to look at more scalable solutions to integrating widgets, code snippets or complete applications and mesh them into functional sites, in any given composition. To share some of…
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…
Suggested Courses

862 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