Solved

Total Sum

Posted on 2003-11-19
19
3,072 Views
Last Modified: 2009-12-16
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
Comment
Question by:Moizsaif123
  • 10
  • 6
  • 2
  • +1
19 Comments
 
LVL 10

Expert Comment

by:D_M_D
ID: 9781801
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
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9781830
yea but how will I place this sum at the bottom of the DataGrids Column 'Balances'.


Moiz
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 9782084
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
 
LVL 10

Expert Comment

by:D_M_D
ID: 9782201
Check out this link...

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

Hope this helps

--------
D_M_D
0
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9782434
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 9782596
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
 

Expert Comment

by:khubpk
ID: 9783772
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
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9787863
khubpk,

what does this statement do?

datagird1.item(column,check)=total


0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 9787937
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Author Comment

by:Moizsaif123
ID: 9788924
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
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9789138
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 9789242
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
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9789751
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 9790350
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
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9792917
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
 
LVL 18

Accepted Solution

by:
Cluskitt earned 130 total points
ID: 9795890
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
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9797575
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
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9797660
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
 
LVL 4

Author Comment

by:Moizsaif123
ID: 9797707
But still the Sum Value on the Footer looks good on the Grid....
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

867 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

15 Experts available now in Live!

Get 1:1 Help Now