• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

Text box to show sum of a GridDataView Field

Hi Experts

I have a textbox called TbxMetres
I also have a field in my gridview table called [Drilled Total]

How do I code the TextBox to show the sum of the field
0
GrahamSA
Asked:
GrahamSA
  • 5
  • 5
  • 2
1 Solution
 
CodeCruiserCommented:
What is the grid bound to? If datatable, then you can just do

txtMetres.Text = dTable.Compute("SUM(ColumnName)", "")

http://msdn.microsoft.com/en-us/library/system.data.datatable.compute.aspx

Otherwise, loop through the gridview rows and do the calculation.
0
 
GrahamSAAuthor Commented:
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?
0
 
CodeCruiserCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
GrahamSAAuthor Commented:
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?
0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
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

Open in new window

0
 
CodeCruiserCommented:
> a view data button is clicked

Then this code should be at the end of Click event handler of that button.
0
 
GrahamSAAuthor Commented:
Hi I have this

Error 2
Not sure what I am doing wrong?
0
 
CodeCruiserCommented:
Does it work if you pass a column name which does not have a space?

Should you not be calling Compute on dtFiltered?
0
 
GrahamSAAuthor Commented:
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

Open in new window

0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
Try changing line 24 to:
   TbxMetres.Text = dTable.Compute("SUM([Drilled Total])", "")

Open 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!
0
 
CodeCruiserCommented:
You are still using dTable though. Does the table contain rows and columns?
0
 
GrahamSAAuthor Commented:
Thanks for the additional help with this
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now