Text box to show sum of a GridDataView Field

Something like this, not sure how to define my binding dource

Private Sub TbxMetres_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TbxMetres.TextChanged
Dim dTable As New DataTable
        TbxMetres.Text = dTable.Compute("SUM(Drilled Total)", "")
End Sub

But
I think I would prefer to

Otherwise, loop through the gridview rows and do the calculation.

How?

Is your grid bound to datatable?  That textchanged event is the worst place to put that code. Put it in page load where you bind the grid.

Hi OK

Private Sub FrmPerformanceView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 Dim dTable As New DataTable
        dTable = PsqlDataSetPerformance.Tables("Performance")
        TbxMetres.Text = dTable.Compute("SUM(Drilled Total)", "")
End Sub

But still empty, this table is only populated when a few datetimepickers are selected and a view data button is clicked, maybe this is why it does not show any data?

if you are using loop to calculate result you can use Cell_EndEdit event and if the column index is similar to the column that has values to be sum, call a method that will iterate on all rows and sum field: e.g.,

    Private Sub dg1_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dg1.CellEndEdit
        If (e.ColumnIndex = dg1.Columns("drilledTotal")) Then
           TbxMetres.Text = DoCalc()  ' iterate on rows and sum field values and return sum
        End If
    End Sub

Select allOpen in new window

> a view data button is clicked

Then this code should be at the end of Click event handler of that button.

Hi I have this


Not sure what I am doing wrong?

Does it work if you pass a column name which does not have a space?

Should you not be calling Compute on dtFiltered?

HI

That makes more sense.
OK i put the code here but still get the error, I have also tried another column called AXT (one word) but to no avail

Private Function FilterSQL(ByVal connString As String, _
                               ByVal dateFrom As Date, _
                               ByVal dateTo As Date) As DataTable
        Dim strSQL As String = "SELECT * FROM Performance WHERE [Date] BETWEEN @DateFrom AND @DateTo"
        Dim lstFilters As New List(Of String)
        For Each s As String In ClbShaftSelect.CheckedItems
            lstFilters.Add(String.Format("(Shaft='{0}')", s))
        Next
        If lstFilters.Count > 0 Then
            strSQL += " AND (" & String.Join(" OR ", lstFilters.ToArray) & ")"
        End If
        Dim dt As New DataTable
        Using conn As New SqlClient.SqlConnection(connString)
            conn.Open()
            Using da As New SqlClient.SqlDataAdapter(strSQL, conn)
                da.SelectCommand.Parameters.AddWithValue("@DateFrom", dateFrom)
                da.SelectCommand.Parameters.AddWithValue("@DateTo", dateTo)
                da.Fill(dt)
            End Using
            conn.Close()
        End Using
        Dim dTable As New DataTable
        dTable = PsqlDataSetPerformance.Tables("Performance")
        TbxMetres.Text = dTable.Compute("SUM(Drilled Total)", "")
        Return dt
    End Function

Select allOpen in new window

Try changing line 24 to:

   TbxMetres.Text = dTable.Compute("SUM([Drilled Total])", "")

Select allOpen in new window

i.e., add missing "[","]" characters. When there is a space in between column names it is recommended to use "[" and "]".

Hope it helps!

You are still using dTable though. Does the table contain rows and columns?

Thanks for the additional help with this