VB.net Tableadapter query not showing desired results

I have a simple VB datagridview form that is used to update a table in my MSSQL DB.
Form looks like this:
2017-12-22_10-12-42.png
I need to run the query and get sum total of all the values.
Here is my TableAdapter  query.
 Private Sub SearchBTN_Click(sender As Object, e As EventArgs) Handles SearchBTN.Click
        Me.CV_Allocation_TblTableAdapter.FillMatterID(CVDataSet.CV_Allocation_Tbl, Nothing)
        StatusLabel1.Text = "Ready"
        Try

            If CheckBox1.Checked = False Then
                Me.CV_Allocation_TblTableAdapter.FillMatterID(CVDataSet.CV_Allocation_Tbl, TextBox1.Text)
            Else
                Me.CV_Allocation_TblTableAdapter.FillExactMatterid(CVDataSet.CV_Allocation_Tbl, TextBox1.Text)
                MsgBox(Me.DataTable1TableAdapter.FillSUM(CVDataSet.DataTable1, TextBox1.Text))
            End If
        Catch ex As Exception

        End Try

Open in new window


 2017-12-22_10-20-45.png
SELECT        SUM([Partner Origination] + [Responsible Partner] + [Case Manager]) AS Total
FROM            cv_allocation_tbl
WHERE        (MatterID = @matterid)
GROUP BY MatterID

Open in new window


When I run my form I get a value of 1 for Msgbox when I expect 100.

2017-12-22_10-24-30.png
LVL 27
yo_beeDirector of Information TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Me.CV_Allocation_TblTableAdapter.FillExactMatterid(CVDataSet.CV_Allocation_Tbl, TextBox1.Text)
                MsgBox(Me.DataTable1TableAdapter.FillSUM(CVDataSet.DataTable1, TextBox1.Text))

Is there any reason you're passing DataTable1 instead of the CV_Allocation_Tbl?
yo_beeDirector of Information TechnologyAuthor Commented:
No particular reason other then I do not know how to accomplish the suming for this.

This is the reason for me posting for suggestion.
louisfrCommented:
You can use the DataTable's Compute method to compute a sum:
CVDataSet.CV_Allocation_Tbl.Columns.Add("ColumnSum", typeof(int), "[Partner Origination] + [Responsible Partner] + [Case Manager]");
var total = CVDataSet.CV_Allocation_Tbl.Compute("SUM(ColumnSum)", $"MatterID='{"A103-7"}'");

Open in new window

yo_beeDirector of Information TechnologyAuthor Commented:
Here is what I did to solve my issue.  
I created a new query under the original table adapter as recommended by Kyle.
Instead of me created a one or multiple record return I created a scaler query that returned only a single record.

    Private Sub SearchBTN_Click(sender As Object, e As EventArgs) Handles SearchBTN.Click
        Me.CV_Allocation_TblTableAdapter.FillMatterID(CVDataSet.CV_Allocation_Tbl, Nothing)
        StatusLabel1.Text = "Ready"
        Try

            If CheckBox1.Checked = False Then
                Me.CV_Allocation_TblTableAdapter.FillMatterID(CVDataSet.CV_Allocation_Tbl, TextBox1.Text)
            Else
                Me.CV_Allocation_TblTableAdapter.FillExactMatterid(CVDataSet.CV_Allocation_Tbl, TextBox1.Text)
                TextBox2.Text = (Me.CV_Allocation_TblTableAdapter.ScalarQuery1(TextBox1.Text))
            End If
        Catch ex As Exception

        End Try

    End Sub

Open in new window

2017-12-26_12-56-08.png2017-12-26_12-56-56.png

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yo_beeDirector of Information TechnologyAuthor Commented:
I ended up coming up with something slightly different than my original code. The reason I gave Kyle points is his recommendation of keeping within the same tableadapter.  I did not know know to leverage Louis recommendation of compute method, but it seemed like another viable option from what I saw on the internet.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.