Solved

Total Sum

Posted on 2003-11-19
19
3,064 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
Comment Utility
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
Comment Utility
yea but how will I place this sum at the bottom of the DataGrids Column 'Balances'.


Moiz
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
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
Comment Utility
Check out this link...

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

Hope this helps

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

Author Comment

by:Moizsaif123
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
khubpk,

what does this statement do?

datagird1.item(column,check)=total


0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 4

Author Comment

by:Moizsaif123
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
But still the Sum Value on the Footer looks good on the Grid....
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

11 Experts available now in Live!

Get 1:1 Help Now