locked
sum() function RRS feed

  • Question

  • Hi, I'm trying to get my label to display the sum of a column in my dataset, i set the label to be databound to the bindingsource, but when i go in to add a query I get "The schema returned by the new query differs from the base query."  I've googled the error and I kind of understand what it means, but I don't know how else to tell the label to get the sum() of a column.  My query I tried is as follows:
    SELECT SUM(Fee) FROM dbo.Financial

    edit: can i just use a stored procedure or somethinglike that?

    edit2: I know this is because I use the same fill command to fill my dataset which paints the datagrid with multiple columns, however in the label I would only like the sum() of one particular column to be displayed, and i knwo this is why i'm getting that error but i don't know how else to formulate the command
    Tuesday, May 27, 2008 6:43 PM

Answers

  • you can use the Compute function in the DataTable. This will return the calculation for the sum of the row.

     

    yes you can use a SPROC if you wanted but this would be an overhead if you already have the data filled in a dataset, bound it, then calling the database again - its ineffecient.

     

    So....

     

    Me.label1.Text = myDataTable.Compute("SUM (ColumnName)", String.Empty)

     

     

    hopefully, this should get you the result in the first row and column of the datatable after the rowfilter has been applied

     

    Tuesday, May 27, 2008 11:24 PM
    Moderator

All replies

  • you can use the Compute function in the DataTable. This will return the calculation for the sum of the row.

     

    yes you can use a SPROC if you wanted but this would be an overhead if you already have the data filled in a dataset, bound it, then calling the database again - its ineffecient.

     

    So....

     

    Me.label1.Text = myDataTable.Compute("SUM (ColumnName)", String.Empty)

     

     

    hopefully, this should get you the result in the first row and column of the datatable after the rowfilter has been applied

     

    Tuesday, May 27, 2008 11:24 PM
    Moderator
  • well i used that and it worked fine for some things, but it is not working for others and i've been testing and debugging all day and still have no idea why it's doing what it's doing.

    I am trying to calculate  sales tax (.06) for the sum of the column Fee; this is what i have:

    Tax = Me.EbtblsDataSet.Financial.Compute("Sum(Fee)*0.06", "").ToString()

    The statement is correct to my knowledge, but in some circumstances it returns a value of "".

    For example, I included it in my formload procedure:

        Private Sub frmAdd_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'EbtblsDataSet.Procedures' table. You can move, or remove it, as needed.
            Me.ProceduresTableAdapter.Fill(Me.EbtblsDataSet.Procedures)

    Tax = Me.EbtblsDataSet.Financial.Compute("Sum(Fee)*0.06", "").ToString()

        End Sub

    And it contained a value of null, but when i put it in my button click procedure (which also contains other compute statements)

        Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
            'Adds record to the dataset/database, makes sure txtFee is numeric
            'otherwise will get an exception, reloads the datasource at the end
            'to refresh

            If txtFee.Text = "" Then
                txtFee.Text = 0.0
            End If
            If IsNumeric(txtPay.Text) = False Then
                MsgBox("Error: Payment field can only consist of numbers.")
            Else
                Dim Payment As Double
                Dim Fee As Double
                Dim Balance As Double
                Payment = txtPay.Text
                Fee = txtFee.Text
                Balance = Fee - Payment

                FinancialTableAdapter.Insert(Form1.intPID, DateTimePicker1.Value, cboDesc.Text, txtCPT.Text, txtMOD1.Text, txtMOD2.Text, txtPay.Text, txtFee.Text, Balance, False, False)
                Me.Close()
                Form1.DataGridView1.EndEdit()
                FinancialTableAdapter.FillByintPID(EbtblsDataSet.Financial, Form1.intPID)
                Form1.DataGridView1.DataSource = EbtblsDataSet.Financial
                Form1.TotalFee = Me.EbtblsDataSet.Financial.Compute("Sum(Fee)", "").ToString()
                Form1.TotalPay = Me.EbtblsDataSet.Financial.Compute("Sum(Payment)", "").ToString()
                Form1.Label1.Text = Form1.TotalFee - Form1.TotalPay
    Tax = Me.EbtblsDataSet.Financial.Compute("Sum(Fee)*0.06", "").ToString()


                MessageBox.Show("Entry added successfully!", "", MessageBoxButtons.OK, MessageBoxIcon.None)
            End If
        End Sub

    It works. Now, the problem here is that I need this code snippet to eventually run in an indexchanged event for a combo box.  So, I can't keep it in the button click event, but i'm not sure why it works there.  I also tried moving the other compute statements such as
    Form1.TotalPay = Me.EbtblsDataSet.Financial.Compute("Sum(Payment)", "").ToString() to the form load, and it did not work either returning the same value of "".  I don't quite understand why it's returning that value in certain procedures but not others, the procedure shouldn't matter, it's referring back to the sql database.

    So if anyone has any insight on this it would be greatly appreciated!
    Thursday, May 29, 2008 8:33 PM