• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3120
  • Last Modified:

Total Sum

I have a question on a Datagrid, I have a bunch of Numbers on a Column named as "Balance", These numbers are retreived from the Database and I need to add these numbers and put the total Sum at the bottom of the Grids Column. Let me know if there is a way

By the way I am using VB.NET and a webapp

Moiz  
0
Moizsaif123
Asked:
Moizsaif123
  • 10
  • 6
  • 2
  • +1
1 Solution
 
D_M_DCommented:
You can use the SUM() function.  for example...

"Select Sum(Balances) From sometable"


This will total all the 'Balance' values in this table.
--------
D_M_D
0
 
Moizsaif123Author Commented:
yea but how will I place this sum at the bottom of the DataGrids Column 'Balances'.


Moiz
0
 
CluskittCommented:
Can't be sure if there are any changes on Datagrid from VB6 to .NET, and I don't have Visual Studio here to give you exact code, and also, it has been a while since I dabbled with VB datagrid, so please forgive me if it turns out inaccurate, but the principle should stay the same.
All you have to do is check the last line of the Datagrid (are you saving a last line for the sum? if not, add one more), and then it's something like (I think, as I said, I'm a bit rusty on this one):
Datagrid.line=5 'or whatever number is appropriate
Datagrid.column=3 'or whatever column balance is on
Datagrid.text=TotalBalance 'TotalBalance would be a variable where you load the result of the Sum(Balances) query.

Anyway, that is basically the idea.
Hope that helps.
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!

 
D_M_DCommented:
Check out this link...

http://www.dotnetjohn.com/articles/articleid22.aspx

Hope this helps

--------
D_M_D
0
 
Moizsaif123Author Commented:
D_M_D, Sorry I couldnt make out anything in the site,
basically I need to know how to input the Sum value on the footer of the Datagrid or to create an empty row and input the Sum into that row...


Moiz
0
 
CluskittCommented:
I think the option in D_M_D's link was more elegant, but you can do a count(Balance), load it to a variable(varBalance, for ex.), and set:
Datagrid.lines=varBalance+1
Datagrid.line=varBalance+1
Datagrid.column=3 'or whatever column you use for balance)
Datagrid.text=TotalBalance
0
 
khubpkCommented:
HI
In you event
{
dim total as integer
dim check as integer
dim num1 as integer
check =Me.bindingcontext(Datasetname which hold datagrid).count
for i= 0 to check-1
num1= datagrid1.item(column,i)
total = total+ num1
next i
datagird1.item(column,check)=total

}
0
 
Moizsaif123Author Commented:
khubpk,

what does this statement do?

datagird1.item(column,check)=total


0
 
CluskittCommented:
puts the data in the last row (rows going from 0 to the number of records in the database are accounted for in count, and check will return the last one).
For example, if the count will return 3 records, check will equal 3. So rows 0, 1 and 2 will have the record values, and row 3 will have the total, which was being incremented in the for statement.
And it's datagrid, not datagird, that was just a typo ;)
0
 
Moizsaif123Author Commented:
Check it Out,

Dim nCtr As Integer
Dim Var As Double
Dim Total As Double
     For nCtr = 0 To DataGrid1.Items.Count - 1
     Var = DataGrid1.Items.Item(nCtr).Cells(6).Text
     Total = Total + Var
Next

From Cluskitt Comment, what if I want to display all the three records and display the Total down below the 3rd Record....how can this be done?




0
 
Moizsaif123Author Commented:
If I put this statement ,

 DataGrid1.Items.Item(nCtr).Cells(6).Text = Total

I get this error message,
                       Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

If I use this statement, it puts my Total into the last row of the Grid

DataGrid1.Items.Item(nCtr - 1).Cells(6).Text = Total
0
 
CluskittCommented:
How many rows and columns does your datagrid have?
You need one row for each record you want to show, plus one for the total.
And try  khubpk's approach:

{
dim total as integer
dim check as integer
dim num1 as integer
check =Me.bindingcontext(Datasetname which hold datagrid).count
for i= 0 to check-1
  num1= datagrid1.item(column,i)
  total = total+ num1
next i
datagrid1.item(column,check)=total
}
 
datagrid1.item(column,check) will give you what you want. column will be a fixed number (2 for example, if you want the 2nd column), and check will just go through all the lines incrementing the total. If your rows don't go from 0 to 2 (on a 3 item datagrid) but from 1 to 3, you will change for i= 0 to check-1, into for i= 0 to check, and datagrid1.item(column,check)=total, into datagrid1.item(column,check+1)=total.
Either way, you need an extra row for the total.
0
 
Moizsaif123Author Commented:
what I'm doing is khubpk's approach, if you read my code carefully with all the syntax clearance, and

datagrid1.item(column,check+1)=total, the check is the count for the number of items ie. the number of rows in the grid which is named as nCtr in my code. So when I put this......

DataGrid1.Items.Item(nCtr).Cells(6).Text = Total or
DataGrid1.Items.Item(nCtr+1).Cells(6).Text = Total //cells(6) gives the column number

I get this error message....

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
 
0
 
CluskittCommented:
Ok, if you want to put the sum, put it in a separate object. Since the DataGrid object is "bound" to the database, it isn't a good idea to go writing stuff in it that doesn't belong in the database.
Simply put a textbox, label, or whichever you prefer, and insert the sum there. that is the best way to do it. Messing about with databound grids isn't a good idea. Still, that's only my opinion. Either way, good luck. ;-)
0
 
Moizsaif123Author Commented:
I am using the DataGrid Footer to display the Total, on

Datagrid1.Column(6).FooterText.Text=(Field Name from DataBase)

but also I agree with Cluskitt on displaying the Sum on a Label or TB, the only hitch is I dont know how to position the Label each time the size of the Datagrid changes, I guess javascript comes into play here. If anyone knows...I'd luv to learn.....




0
 
CluskittCommented:
Label1.top=Datagrid1.top+Datagrid1.Height+10
Put this on the events where the datagrid may change. You can also adjust the space in between them (I put 10 as an example. Try out which one would be best.). You can also make much the same with the left position to center it. Assuming the label has a width of 50:
Label1.left=Datagrid1.left+(Datagrid1.width/2)-25
This will seek the center of the Datagrid, and subtract half the length of the label, so it will be 25 to the left of the center and 25 to the right.
0
 
Moizsaif123Author Commented:
This is my Script, but  it doesnt position my Label

<script language="javascript">
function Allignment(){
var d1=document.getElementById("DataGrid1").offsetTop+document.getElementById("DataGrid1").offsetHeight+10

var d2=document.getElementById("Label1").offsetTop
d2=d1
//alert(d2)  d2 refers to the DataGrid which equals 329
//alert(d1)  d1 refers to the Label equals 329
}
</script>
0
 
Moizsaif123Author Commented:
Now it Does, Check it Out,I could also try out the positioning of the width of the label ...

<script language="javascript">
function Allignment(){
var d1=document.getElementById("DataGrid1").offsetTop+document.getElementById("DataGrid1").offsetHeight+10

document.getElementById("Label1").style.top=d1
}
</script>
0
 
Moizsaif123Author Commented:
But still the Sum Value on the Footer looks good on the Grid....
0

Featured Post

Independent Software Vendors: 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!

  • 10
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now