question about gridview datasource to SQL db

[DougP]

I want to get the total hours for what is in the Gridview, I have this code filling it.
is there a way to extract the recordsource and then find the Total hours somehow? the totlhours will not be in the gridview I want them in a textbox on the form below it.
I know in Access I can use Recordssetclone on a subform?

       Dim cnn As SqlConnection

        cnn = New SqlConnection(gblconnectionString)

        Try
            cnn.Open()
            Dim queryTrans As String
            queryTrans = "SELECT tp.Vendor, ds.ResourceLastName AS LName, ds.ResourceFirstName AS FName, " & _
                "tp.ResourceType, tp.OffshoreOnShore AS Shore, tp.Year, tp.Month, tp.Day, " & _
                "tp.SPMID, tp.CostTracker, tp.ADActivityCode AS ADA, tp.HoursWorked AS Hours, " & _
                "tp.ApprovedBy, tp.ApprovedDate, tp.SOWTimeReportingUniqueID AS ID " & _
                ",tp.WeekEndDate " & _
                "FROM SOWTimeReporting AS tp INNER JOIN SOWTimeSheetDateSubmitted AS ds " & _
                "ON tp.ResourceLastName = ds.ResourceLastName " & _
                "AND tp.ResourceFirstName = ds.ResourceFirstName " & _
                "WHERE  (tp.Manager = '" & Manager & "')"

            ' "AND tp.WeekEndDate = ds.WeekEndDate  " & _
            '(tp.WeekEndDate = @Date) AND

            Dim daTrans As New SqlDataAdapter
            Dim dtTrans As New DataTable

            daTrans.SelectCommand = New SqlCommand(queryTrans, cnn)
            daTrans.Fill(dtTrans)
            'DataGridViewTrans.Columns
            Me.GridView1.DataSource = dtTrans
            Me.GridView1.DataBind()

DougP

 

[jbenson001]

If you just want to get a total, you can loop through the rows in dtTrans and total whatever columns you need to.

 

[MarkSweetland]

You can also use LINQ to query the datatable and perform the totals. Something like (not tested):

Dim TotalHours = Aggregate Hours In dtTrans.AsEnumerable
                       Into Sum(order.Field(Of Integer)("Hours"))

 Dim lbl As New Label
 lbl.Text = TotalHours.ToString

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach