Working with multiple sql sum results

[jdavis2]

Hello everyone

I am having a bit of a problem with displaying the values returned by a sum query. Heres my situation :

SELECT SUM(fldMaterial) AS Expr1, SUM(fldBob) AS Expr2, SUM(fldBrian) AS Expr3
FROM tbljobs

I have 3 columns of interest that I just want to sum all value. This works great in query builder.

Now on a form I want to display each return in a textbox.
A simple line of code will display the first result but I have no idea on how to access the last two.

TextBox1.Text = CStr(Me.TbljobsTableAdapter.GetTotals)

In my simple train of thought I would have thought something like this would work.
TextBox1.Text = CStr(Me.TbljobsTableAdapter.GetTotals,Expr1)
TextBox2.Text = CStr(Me.TbljobsTableAdapter.GetTotals,Expr2)
TextBox3.Text = CStr(Me.TbljobsTableAdapter.GetTotals,Expr3)

P.S - Why it that when I answer one question 5 more pop up?? haha

 

[cjard]

You might have noticed that in selecting 3 columns you are actually selecting a grid of data.

Presumably GetTotals is a method that returns a TblJobsDataTable, so you do either:

MyDataGridView.DataSource = [COLOR=#0000ff]Me[/COLOR][SIZE=2].TbljobsTableAdapter.GetTotals()[/SIZE]

If you have a datagridview on your form


Or you would do this:

Dim dt as [B]SomeDataSetIDontKnowWhatYouCalledIt[/B].TblJobsDataTable = [COLOR=#0000ff]Me[/COLOR][SIZE=2].TbljobsTableAdapter.GetTotals()[/SIZE]
Text1.Text = dt.Expr1
Text2.Text = dt.Expr2
Text3.Text = dt.Expr3

The reason your existing code doesnt work is because CStr() takes just one argument, but youre passing in 2 (the comma). If CStr does take 2 arguments (and i couldnt find a version that does, but I work with the VB6 compatibility turned off so i dont even have it available - i had to google for it) then it certainly wouldnt take arguments of type (DataTable, Object)


Your code MIGHT work if you replaced the commas with periods .

BUT DO NOT DO THIS. Every time you call GetTotals() the database goes and sums all the things in the datatable. You would hence be calling GetTotals 3 times, causing 3 database operations to run. You can understand how this is a potential waste of resources, i'm sure

Like every long-runing op we should always:
Get the result, cache the result locally, reuse the cached result

 

[jdavis2]

Ahhh completely understand now. Was thinking outside the box when I didnt need to Thank you very much!