Answered Extract Cash and Credit Total Separately from datagridview

[rajdh75]

Hello,
I have a unbound datagridview which depend on Query from Access database like following -

Column1 Column2 Column3 Column4 Column5
Date InvoiceID CustomerName Cash / Credit Amount
03/05/2020 19 ABC Cash 60.00
14/05/2020 28 ABC Credit 30.00
16/05/2020 29 ABC Credit 100.01
18/05/2020 34 ABC Cash 1250.00
20/05/2020 41 ABC Cash 30.00

I have made 3 Textboxes as 1. Cash (TxtCash.Text) 2. Credit (TxtCredit.Text) 3. Total (TxtTotal.Text)
Now I want to show total Cash amount in Cash text box, total Credit amount in Credit text box and Total of both in Total text box.
In this case - Cash Total - 1340.00, Credit Total - 130.01 and Total amount - 1470.01.
I am attaching image of form for reference.
How could I do this automatically when datagridview is load or if not possible by a Show Total button ?
Any code for this ?

Thanks in advance.

 

[jmcilhinney]

If the data is coming from a database, why is the grid unbound in the first place? You're just making things hard for yourself. You should populate a DataTable and bind that to the grid via a BindingSource. You could then use the Compute method of the DataTable to get those values, e.g.

Dim cashSubtotal = CDec(table.Compute("SUM(Amount)", "PaymentType = 'Cash'"))
Dim creditSubtotal = CDec(table.Compute("SUM(Amount)", "PaymentType = 'Credit'"))
Dim total = CDec(table.Compute("SUM(Amount)", String.Empty))

If you're going to insist on doing it the hard way then how about you put some thought into it and give it a go? Programming doesn't exist in a vacuum. Code is just an implementation of an algorithm. Not surprisingly, that algorithm can be created independently of the code and is the same algorithm you'd use to do the task manually. If you were doing this manually, how would you do it? If it was me, I'd start with three running totals set to zero and then look at each row in turn. I'd add the amount to one running total and then also one of the other two, depending on the value of the payment type. That's just common sense, right? You don't need any programming experience to get to that point, so you should have got to that point already. You should then formalise those steps into a proper algorithm and then try to implement the steps you come up with in code. If you encounter an actual issue when you're doing that, then would be the time to post a question here, explaining what you're trying to achieve, how you're trying to achieve it and what happened when you tried.

 

[rajdh75]

Thanks for your advice [U]jmcilhinney[/U],

I have made following code from your solution.

Dim cmd As New OleDbCommand("SELECT * from TblInvoiceQuery WHERE DateOfSale BETWEEN @DateOfSaleFrom AND @DateOfSaleTo AND CustomerName = @CustomerName", con)

        With cmd.Parameters
            .Add("@DateOfSaleFrom", OleDbType.Date).Value = DateFromPicker.Value.Date + TimeFromPicker.Value.TimeOfDay
            .Add("@DateOfSaleTo", OleDbType.Date).Value = DateToPicker.Value.Date + TimeToPicker.Value.TimeOfDay
            .Add("@CustomerName", OleDbType.VarChar, 50).Value = CmbCustomerName.Text


            Dim da As New OleDbDataAdapter
            Dim dt As New DataTable
            da.SelectCommand = cmd
            dt.Clear()
            da.Fill(dt)
            DataGridView1.AutoGenerateColumns = False
            DataGridView1.Columns(0).DataPropertyName = "DateofSale"
            DataGridView1.Columns(1).DataPropertyName = "InvoiceID"
            DataGridView1.Columns(2).DataPropertyName = "CustomerName"
            DataGridView1.Columns(3).DataPropertyName = "TypeofSale"
            DataGridView1.Columns(4).DataPropertyName = "Amount"

            DataGridView1.DataSource = dt

            Dim cashSubtotal = CDec(dt.Compute("SUM(Amount)", "TypeofSale = 'Cash'"))
            Dim creditSubtotal = CDec(dt.Compute("SUM(Amount)", "TypeofSale = 'Credit'"))
            Dim total = CDec(dt.Compute("SUM(Amount)", String.Empty))


            TxtCashAmt.Text = cashSubtotal
            TxtCashAmt.Text = Format(Val(cashSubtotal), "#,##0.00")
           
            TxtCreditAmt.Text = creditSubtotal
            TxtCreditAmt.Text = Format(Val(creditSubtotal), "#,##0.00")

            TxtTotal.Text = cashSubtotal + creditSubtotal
            TxtTotal.Text = Format(Val(total), "0.00")

            TxtTotal.Text = CStr(Math.Round(Val(TxtTotal.Text)))
            TxtTotal.Text = Format(Val(TxtTotal.Text), "#,##0.00")
   
        End With
    End Sub

It works well when there are both Cash and Credit rows.

But it gives error when if there is only Credit amount rows and no Cash amount rows to this line -
Dim cashSubtotal = CDec(dt.Compute("SUM(Amount)", "TypeofSale = 'Cash'"))
As -
InvalidCastException was unhandled.
An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
Additional information: Conversion from type 'DBNull' to type 'Decimal' is not valid.
Troubleshooting tips:
Make sure the source type is convertible to destination type.
When casting from a number, the value must be number less than infinity.
and
when if there is only Cash amount rows and no Credit amount rows to this line -
Dim creditSubtotal = CDec(dt.Compute("SUM(Amount)", "TypeofSale = 'Credit'"))
As -
InvalidCastException was unhandled.
An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
Additional information: Conversion from type 'DBNull' to type 'Decimal' is not valid.
Troubleshooting tips:
Make sure the source type is convertible to destination type.
When casting from a number, the value must be number less than infinity.

How could I overcome this error ?

Thanks

 

[jmcilhinney]

Dim cashSubtotalSum = table.Compute("SUM(Amount)", "PaymentType = 'Cash'")
Dim creditSubtotalSum = table.Compute("SUM(Amount)", "PaymentType = 'Credit'")
Dim totalSum = table.Compute("SUM(Amount)", String.Empty)

Dim cashSubtotal = If(cashSubtotalSum Is DBNull.Value, Decimal.Zero, CDec(cashSubtotalSum))
Dim creditSubtotal = If(creditSubtotalSum Is DBNull.Value, Decimal.Zero, CDec(creditSubtotalSum))
Dim total = If(totalSum Is DBNull.Value, Decimal.Zero, CDec(totalSum))

 

[rajdh75]

Thanks
I have made changes as following

Dim cashSubtotal = CDec(dt.Compute("SUM(Amount)", "TypeofSale = 'Cash'"))
            Dim creditSubtotal = CDec(dt.Compute("SUM(Amount)", "TypeofSale = 'Credit'"))
            Dim total = CDec(dt.Compute("SUM(Amount)", String.Empty))

            Dim cashSubtotal = If(cashSubtotalResult Is DBNull.Value, Decimal.Zero, CDec(cashSubtotalResult))
            Dim creditSubtotal = If(creditSubtotalResult Is DBNull.Value, Decimal.Zero, CDec(creditSubtotalResult))
            Dim total = If(totalResult Is DBNull.Value, Decimal.Zero, CDec(totalResult))

But it give error on
cashSubtotalResult
as 'cashSubtotalResult' is not declared. It may be inaccessible due to its protection level.

creditSubtotalResult
as 'creditSubtotalResult' is not declared. It may be inaccessible due to its protection level.

totalResult
as 'totalResult' is not declared. It may be inaccessible due to its protection level.

 

[jmcilhinney]

I've fixed the code. I had edited the variable names where I declared them but not where I used them later. You really should have been able to work that out for yourself if you'd examined the code and tried to understand what it's doing.

 

[rajdh75]

Thanks,
I understood the code now.
It is working without error.
Thank you again.