How to Subtract Credit aliases from Debit aliases for all rows?

Question

I am developing a Ledger Management System, I have two type of reports, one is Ledger Report and other is Account Report. In Account Report i am fetching data from debit and credit table and displaying in dataGridView.

In dataGridView i have Remaining aliases that contains result value of Credit - Debit, i am also assign this value for the second row Debit column because i want next Credit amount should subtract with Remaining Amount.

The reason of dong this is query display same debit amount in every row that results next credit amount subtract again with actual debit amount and Remaining balance is incorrect.

So i want for second row Credit amount should subtract from Debit amount of populated value in second row and so on.

private void PopulateAccountReport()
        {
            string selectedName = comboBox2.SelectedItem.ToString();
            OleDbConnection connection = new OleDbConnection(connectionString);

            string query = "SELECT c.r_name AS 'Name', c.r_date AS 'Credit Date', c.r_amount AS 'Credit', d.d_date AS 'Debit Date', d.d_amount AS 'Debit', (d.d_amount - c.r_amount) AS 'Remaining', c.r_detail AS 'Credit Detail', d.d_detail AS 'Debit Detail' FROM credit c INNER JOIN debit d ON c.r_name = d.d_name WHERE c.r_name = @SelectedName ORDER BY c.r_sr ASC";

            using (OleDbCommand cmd = new OleDbCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("@SelectedName", selectedName);
                connection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                connection.Close();
                dataGridView1.DataSource = dt;
                decimal totalDebit = 0;
                decimal totalCredit = 0;

                foreach (DataRow row in dt.Rows)
                {
                    totalDebit += Convert.ToDecimal(row["'Debit'"]);
                    totalCredit += Convert.ToDecimal(row["'Credit'"]);

                    
                    int rowIndex = dt.Rows.IndexOf(row);
                    if (rowIndex < dt.Rows.Count - 1)
                    {
                        dt.Rows[rowIndex + 1]["'Debit'"] = row["'Remaining'"];
                    }
                }
                txt_tdebit.Text = totalDebit.ToString();
                txt_tcredit.Text = totalCredit.ToString();
            }
        }

`

See the Image for Expected Result

Answer 1

// After loading the DataTable, add a new column for 'Remaining'
dt.Columns.Add("'Remaining'", typeof(decimal));

// Initialize variables to calculate totals and running balance
var runningBalance = 0m;
var totalDebit = 0m;
var totalCredit = 0m;

for (int i = 0; i < dt.Rows.Count; i++)
{
    decimal credit = Convert.ToDecimal(dt.Rows[i]["'Credit'"]);
    decimal debit = Convert.ToDecimal(dt.Rows[i]["'Debit'"]);
    totalDebit += debit;
    totalCredit += credit;

    // Calculate remaining balance
    runningBalance += credit - debit;

    // Update the 'Remaining' value in the current row
    dt.Rows[i]["'Remaining'"] = runningBalance;

    // If not the last row, update the 'Debit' value in the next row
    if (i < dt.Rows.Count - 1)
    {
        dt.Rows[i + 1]["'Debit'"] = runningBalance;
    }
}

// Update total debit and credit text boxes
txt_tdebit.Text = totalDebit.ToString();
txt_tcredit.Text = totalCredit.ToString();

// Finally, set the data source for the DataGridView
dataGridView1.DataSource = dt;

your code does not maintain a running balance when calculating the remaining amounts It needs to accumulate the credits and debits for each row to correctly compute the remaining balance for each row