Auto Sorting of Newly added DataRow

[dharanko]

Dear All,

I have problem with how to auto sort newly added DataRow on DataSet.Tables and the my problem is like this ..........................

DataSet.Tables(0) is from "select date, amount, balance from mytalbe order by date"

Now after new row (DataRow) added I want to go through row by row to update my balance fill with the current amount + previous balance

balance = amount + previous amount

Till the new row is always more than existing date on table then it is okey but when you add new row with previous date than existing dates on table. My row by row goin to make goes wrong coz the newly added row index is +1 more than exsting rows index.

Any Help Will Be Greatly Appreciated

Dharanko
Nepal

Opps Sorry For My Poor English Language

 

[pachjo]

I have a function I created to calculate a rolling balance in this manner, if I understand you correct.

Here is how I do it:

Private Function CalculateRollingBalance(ByVal intTabpage As Integer) As Boolean

        ' facilitates calculating the rolling balance 
        ' the transaction table would already have been populated with the required data to include or
        ' exclude voided records

        Dim intRowPointer As Integer = 0

        ' very first entry will be added to initial value of zero

        Dim intSeed As Decimal = 0

        Try

            ' only do this if there is data to work on 

            If Me.MyFinanceDataSet1.Transactions.Rows.Count > 0 Then

                ' cycle through the rows 

                Do While intRowPointer <= Me.MyFinanceDataSet1.Transactions.Rows.Count - 1

                    ' calcualte the rolling balance

                    intSeed += Convert.ToDecimal(Me.MyFinanceDataSet1.Transactions.Rows(intRowPointer).Item("TransactionAmount"))

                    ' add value to the table
                    Me.MyFinanceDataSet1.Transactions.Rows(intRowPointer).Item("RollingBalance") = intSeed

                    ' move to next row

                    intRowPointer += 1

                Loop

                ' write to database
                Me.TransactionsTableAdapter.Update(Me.MyFinanceDataSet1.Transactions)

            End If

            CalculateRollingBalance = True

        Catch objError As Exception

            MessageBox.Show("Location: CalculateRollingBalance" & System.Environment.NewLine & System.Environment.NewLine & "The following error occurred whilst calculating the rolling balance" & _
                           System.Environment.NewLine & System.Environment.NewLine & objError.Message & System.Environment.NewLine & System.Environment.NewLine & "If the problem persists contact support", _
                           "Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            CalculateRollingBalance = False

        End Try

    End Function

May not be a perfect solution, but for my little home application I am writing for the educational purpose of learning DB programming with VB.NET it works a treat

 

[dharanko]

My Problem

Hi, Thank you for the reply.

Actually I have dataset table and everytime its datarow updated or add new i have to perfrom the row by row calculation and update the clumns value. Therefore every time new row added or modified I want to do the calucation but according to the order of the 2nd and 3rd columns.

And only want to update that on the database when Save Button clicked. Actually I want to use the power of ADO.NET disconnected features but since I am new I think it will take some time to figure out here.

I have come to the DataView object and I am working on it and I hope it will solve my problem.

Dharanko
Nepal

 

[cjard]

Dear All,

I have problem with how to auto sort newly added DataRow on DataSet.Tables and the my problem is like this ..........................

DataSet.Tables(0) is from "select date, amount, balance from mytalbe order by date"

Now after new row (DataRow) added I want to go through row by row to update my balance fill with the current amount + previous balance

balance = amount + previous amount

Till the new row is always more than existing date on table then it is okey but when you add new row with previous date than existing dates on table. My row by row goin to make goes wrong coz the newly added row index is +1 more than exsting rows index.

Any Help Will Be Greatly Appreciated

Dharanko
Nepal

Opps Sorry For My Poor English Language

Just add a column with the .Expression property set to "SUM([amount])"
Do this in design time. If you dont think you can, then you might be doing your data access the wrong way. Read the DW2 link in my signature, section on creating a simple application
Change the bold bit to the name of your column holding the amount

Now all rows will have the sum:

name, amount, total
apple, 1, 10
pear, 2, 10
orange, 3, 10
grape, 4, 10

1+2+3+4 = 10

 

[cjard]

I have a function I created to calculate a rolling balance in this manner, if I understand you correct.

Heh, youre going to love my 1 line solution then

 

[pachjo]

Well what can I say? I will check it out to see if it work in my setup

And if it does .........

DOH!!!!!!! why do I keep using 50 lines of code when 1 will do?

I guess there is just so many bits available I just have not learned how to find them

 

[pachjo]

I'm not too sure this will work with my situation?

1. I am looking into locating the .Expression property, looking at DW2 link

2. My transaction table has a void column to allow the filtering of void and active transactions.

3. Based on 2 above I need to be able to display either all transactions or only active transactions with a rolling balance depending on user selection.

4. Also the date range is flexible so the rolling balance needs to be calculated on the fly as and when the user selects a new data range and whether to display all or only active transactions.

What I am doing is writing my own home finance application that mimics the online bank statements.

It is also acting as an educational exercise in database programming in VB.NET 2005.

I had my application all but complete to the point of internal backup and import/export of data but chose to start over to do things the 'right' way.

I don't regret doing so as the whole idea of this project is a learning exercise thus i kinda want to do it right

 

[cjard]

having read your other post, this thread makes more sense. Expression cannot be used to provide a rolling balance