Member Avatar for Tito_3

i have a simple database where i want to sum Amount of one one text box at the footer of my form textbox. Am using access as the database

  • 4 Contributors
  • 8 Replies
  • 507 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by Minimalist

Dani AI

Generated

As pointed out, a grid is the right control for multi-row data; 's idea of updating on change fits WinForms; and 's suggestion to push aggregation into the database is often the best-performing choice. Three practical patterns follow (ASP.NET WebForms examples first, then a WinForms option). Each one handles DBNull and numeric parsing and can be run after an insert so the footer textbox always shows the grand total.

Server-side compute (fast, easy for small/medium sets — uses DataTable.Compute):

' fill DataTable from Access and bind
Using cn As New OleDb.OleDbConnection(connStr)
    Dim da As New OleDb.OleDbDataAdapter("SELECT Amount FROM MyTable", cn)
    Dim dt As New DataTable()
    da.Fill(dt)
    GridView1.DataSource = dt
    GridView1.DataBind()

    Dim totalObj As Object = dt.Compute("SUM(Amount)", "")
    Dim total As Decimal = If(totalObj Is Nothing OrElse totalObj Is DBNull.Value, 0D, Convert.ToDecimal(totalObj))

    Dim txtFooter As TextBox = TryCast(GridView1.FooterRow.FindControl("txtFooterTotal"), TextBox)
    If txtFooter IsNot Nothing Then txtFooter.Text = total.ToString("N2")
End Using

Row-by-row during binding (gives per-row control/formatting; reset the accumulator before each DataBind):

Private runningTotal As Decimal = 0D

Protected Sub GridView1_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GridView1.RowDataBound
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim amt As Decimal = 0D
        Decimal.TryParse(Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Amount")), amt)
        runningTotal += amt
    ElseIf e.Row.RowType = DataControlRowType.Footer Then
        Dim lbl As Label = TryCast(e.Row.FindControl("lblTotal"), Label)
        If lbl IsNot Nothing Then lbl.Text = runningTotal.ToString("N2")
    End If
End Sub

WinForms (DataGridView) — recalc on RowsAdded/RowsRemoved/CellValueChanged:

Private Sub RecalcTotal()
    Dim total As Decimal = 0D
    For Each r As DataGridViewRow In DataGridView1.Rows
        If Not r.IsNewRow Then
            Dim v = r.Cells("Amount").Value
            Dim amt As Decimal = 0D
            Decimal.TryParse(Convert.ToString(v), amt)
            total += amt
        End If
    Next
    txtFooterTotal.Text = total.ToString("N2")
End Sub

Quick tips: for very large tables prefer a SQL SUM (ExecuteScalar) to avoid transferring rows; always handle DBNull and text-to-decimal parsing; after inserting a record either rebind or update the running total directly; if using Access .accdb, ensure the correct ACE OLEDB provider and process bitness (x86/x64).

Recommended Answers

All 8 Replies

Member Avatar for Tito_3

i want the text box in my footer to show grand total whener i add new record

Member Avatar for rproffitt

That's not the real problem. For VB, you could write an on change event handler that you sum it up and change the text box value as you wish. I often find folk struggling with this if they didn't complete a programming course or finish a book on programming in vb.net.

Answer: Use an on change event handler.

Member Avatar for Tito_3

are you helping or judging? i havent gone to a programming course just learning on myself

Member Avatar for rproffitt

Your choice. Now that I know you haven't completed eiither I hope you'll research the on change event and code that to do what you want.

Member Avatar for Reverend Jim

Typically, if you want to display multirow data from a database you use a DataGridView rather than a Textbox. I'm assuming multirow otherwise you wouldn't need to do a sum.

Member Avatar for Minimalist

O.K. I assume that you have added a totalsum row to your database which will give you a field that contains the total. If not then check this out:
https://www.gcflearnfree.org/access2016/how-to-create-calculated-fields-and-totals-rows/1/

To display this one field in your textbox you need a query against your database and display the result. Here is how to do this:
https://www.daniweb.com/programming/software-development/threads/461537/how-to-show-access-database-value-in-textbox

Edited by Minimalist
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.