Calculate Running Total in listview group extract from database
I'm developing Asset Management System, it will compute for straight line depreciation, my problem is how to calculate running total on each group that I've extracted from my database.Can somebody help me, I can't figure out what I'm doing wrong.
Here's a snippet for my extraction of data
Code:
Private Sub cboAType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboAType.SelectedIndexChanged
ListView1.Items.Clear()
If Not cboAType.Text = "-" Then
ListView1.Columns.Clear()
ListView1.Columns.Add("", 150, HorizontalAlignment.Left) 'description + model - 0
ListView1.Columns.Add("Asset ID", 70, HorizontalAlignment.Left) '1
ListView1.Columns.Add("Acquisition Date", 115, HorizontalAlignment.Left) '2
ListView1.Columns.Add("Method", 140, HorizontalAlignment.Left) '3
ListView1.Columns.Add("Cost", 65, HorizontalAlignment.Left) '4
ListView1.Columns.Add("Salvage Value", 90, HorizontalAlignment.Left) '5
ListView1.Columns.Add("", 50, HorizontalAlignment.Left) '6
ListView1.Columns.Add("Life", 30, HorizontalAlignment.Left) '7
ListView1.Columns.Add("Monthly Depn", 60, HorizontalAlignment.Left) '8
ListView1.Columns.Add("Depn Exp", 60, HorizontalAlignment.Left) '9
ListView1.Columns.Add("Accu Depn", 60, HorizontalAlignment.Left) '10
ListView1.Columns.Add("Book Value", 80, HorizontalAlignment.Left) '11
oledbcon.Open()
str = "SELECT branch FROM tblAsset WHERE assetType LIKE '" & cboAType.Text & "' GROUP BY branch"
cmd = New OleDbCommand(str, oledbcon)
objRead = cmd.ExecuteReader
While objRead.Read
lv = Me.ListView1.Items.Add(objRead("branch").ToString)
str1 = "SELECT * FROM tblAsset WHERE branch LIKE '" & lv.SubItems.Item(0).Text & "'"
cmd1 = New OleDbCommand(str1, oledbcon)
objRead1 = cmd1.ExecuteReader
While objRead1.Read
lv1 = Me.ListView1.Items.Add(objRead1("description").ToString)
lv1.SubItems.Add(objRead1("serial").ToString)
lv1.SubItems.Add(objRead1("dop").ToString) '2
lv1.SubItems.Add(objRead1("method").ToString)
lv1.SubItems.Add(objRead1("price").ToString) '4
lv1.SubItems.Add(objRead1("salvage").ToString) '5
lv1.SubItems.Add(objRead1("AmtPer").ToString) '6
lv1.SubItems.Add(objRead1("life").ToString) '7
tempPS = Convert.ToDouble(IIf(lv1.SubItems.Item(4).Text = "", "0.00", lv1.SubItems.Item(4).Text)) _
- Convert.ToDouble(IIf(lv1.SubItems.Item(5).Text = "", "0.00", lv1.SubItems.Item(5).Text))
tempSLN = tempPS _
/ Convert.ToDouble(IIf(lv1.SubItems.Item(7).Text = "", "0.00", lv1.SubItems.Item(7).Text)) 'depreciation
tempMonthlyExp = Format(tempSLN _
/ 12, "0.00")
lv1.SubItems.Add(tempMonthlyExp) '8
Dim d11 As DateTime = lv1.SubItems.Item(2).Text
Dim d22 As DateTime = dPeriod.Value
If d11.Year = d22.Year Then
Dim months As Integer = (Math.Abs((d11.Month - d22.Month)))
deprexp = Format(months _
* Convert.ToDouble(IIf(lv1.SubItems.Item(8).Text = "", "0.00", lv1.SubItems.Item(8).Text)), "0.00")
lv1.SubItems.Add(deprexp) '9
ElseIf Not d11.Year = d22.Year Then
Dim tempyear As Integer = 0
Do Until tempyear = d22.Year
tempyear = tempyear + 1
Loop
Dim yrstring As String = tempyear
Dim dtp1 As DateTime = "January " & yrstring
Dim months11 As Integer = (Math.Abs((dtp1.Month - d22.Month)))
deprexp = Format((months11 + 1) _
* Convert.ToDouble(IIf(lv1.SubItems.Item(8).Text = "", "0.00", lv1.SubItems.Item(8).Text)), "0.00")
lv1.SubItems.Add(deprexp) '9
End If
Dim d1 As DateTime = lv1.SubItems.Item(2).Text
Dim d2 As DateTime = dPeriod.Value
Dim M As Integer = Math.Abs((d1.Year - d2.Year))
Dim months1 As Integer = ((M * 12) + Math.Abs((d1.Month - d2.Month)))
accudepr = Format(months1 _
* Convert.ToDouble(IIf(lv1.SubItems.Item(8).Text = "", "0.00", lv1.SubItems.Item(8).Text)), "0.00")
lv1.SubItems.Add(accudepr) '10
netbook = Format(Convert.ToDouble(IIf(lv1.SubItems.Item(4).Text = "", "0.00", lv1.SubItems.Item(4).Text)) _
- Convert.ToDouble(IIf(lv1.SubItems.Item(10).Text = "", "0.00", lv1.SubItems.Item(10).Text)), "0.00")
lv1.SubItems.Add(netbook) '11
End While
lv1 = Me.ListView1.Items.Add("")
lv1.SubItems.Add("")
lv1.SubItems.Add("")
lv1.SubItems.Add("")
Dim totalP As New Double
totalP = Format(FIND_COST, "0.00")
lv1.SubItems.Add(totalP)
lv1 = Me.ListView1.Items.Add("")
objRead1.Close()
End While
objRead.Close()
oledbcon.Close()
ElseIf cboAType.Text = "-" Then
ListView1.Columns.Clear()
ListView1.Items.Clear()
End If
'and here's my code for calculating running total. I already used this code on my payroll summary and it works perfect, I just can't figure it out why it don't work here
Public Function FIND_COST() As Double
Dim cost1 As Double = 0
Try
For cost2 As Integer = 0 To ListView1.Items.Count - 1
cost1 += CType(ListView1.Items(cost2).SubItems(5).Text, Double)
Next
Catch ex As Exception
End Try
Return cost1
End Function
Here's my screenshot output
http://imageshack.us/photo/my-images/7/39727638.jpg/
Thank You
Re: Calculate Running Total in listview group extract from database
if you know you want he running totals why are you not calculating them as you put them into listview1
and storing them after you complete the inputs
here to talk
Re: Calculate Running Total in listview group extract from database
You could create a view that you pull the data from that calculates your running totals:
Example of SQL code to this would be:
Code:
SELECT a.TransID, a.PaidIn, SUM(b.PaidIn) AS RunningTotal
FROM Transactions AS a INNER JOIN
Transactions AS b ON a.TransID >= b.TransID
GROUP BY a.TransID, a.PaidIn
ORDER BY a.TransID, a.PaidIn
Hope this helps or puts you in the right direction.
Re: Calculate Running Total in listview group extract from database
@ incidentals,
I've already declared the calculation for running totals, it's on the bottom of my codes, I declared as a Function. It calculates a listview column and BTW I'd already use that function on my other projects and it works fine. My problem is that it don't calculate the column or let's say the column for the group that I've extracted.
@ConfusedAgain,
Thanks for the help :thumb:, I'll try this one. How about the other entry like on the lv1.SubItems.Item(8).Text, this data is not on my database. It's a floating calculation.
Re: Calculate Running Total in listview group extract from database
You could either pass it as a parameter or update a temporary table in your database.