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

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 ?

MamineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dakydCommented:
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
surajgupthaCommented:
If u could post the datagrid it would be easier for us.

Thanks
0
MamineAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MamineAuthor Commented:
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
MamineAuthor Commented:
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
dakydCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MamineAuthor Commented:
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
MamineAuthor Commented:
.... or is it cos its single quotes rather than double quotes ? (the values read from the non-input table cells come out fine)
0
MamineAuthor Commented:
... scrap that ... my boo-boo ... dropped the .value off the end - duh!
0
MamineAuthor Commented:
Works like a dream now!!!
0
dakydCommented:
Great, glad to hear you got what you wanted.  Thanks for the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.