Display sum of records

[jpinto]

Hello,

I need to display on a form, on a textbox, the value of a sum of records that is on a Query on my database. I've the following code:

con2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=""C:\Programaçao\Visual Basic\Inapal\ReportIt\ReportIt.mdb"";"

con2.Open()

sSQL2 = "SELECT Qtd  FROM [TotalScrapQuery] WHERE ((Data LIKE'" & DateReport.Text & "') AND (Turno LIKE'" & ShiftReport.Text & "'))"

da2 = New OleDb.OleDbDataAdapter(sSQL2, con2)

ds2.Clear()

da2.Fill(ds2, "TotalScrapQuery")

con2.Close()

How can I make something like:

ScrapBox.text=Qtd

Thanks,

João Pinto

 

[TipGiver]

Do something like:

SELECT SUM(Qtd) ...

and instead of filling a dataset, fill a datatable. Then get the item 0,0 (row, col) of the datatable and set it to the textbox

 

[djj55]

There is also the ExecuteScalar you can use with the SELECT SUM(Qtd)...
djj

 

[jpinto]

TigGiver,

Can you please show me some sample code of what you mean?

Thanks,

João Pinto

 

[djj55]

Try:

sSQL2 = "SELECT [COLOR=red]SUM(Qtd)[/color] FROM [TotalScrapQuery] WHERE ((Data LIKE'" & DateReport.Text & "') AND (Turno LIKE'" & ShiftReport.Text & "'))"

djj

 

[TipGiver]

Hi,

        con2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=""C:\Programaçao\Visual Basic\Inapal\ReportIt\ReportIt.mdb"";"
        con2.Open()

        sSQL2 = "SELECT SUM(Qtd)  FROM [TotalScrapQuery] WHERE ((Data LIKE'" & DateReport.Text & "') AND (Turno LIKE'" & ShiftReport.Text & "'))"

        Dim da2 As New OleDb.OleDbDataAdapter(sSQL2, con2)

        ds2.Clear()

        Dim dt As DataTable
        da2.Fill(dt, "TotalScrapQuery")

        con2.Close()

        ScrapBox.Text = dt.Rows(0).Item(0).ToString()

.. Something like this.