Hey guys I have a new problem. I am using mysql as my database. I want to get the sum of an amount between 2 dates heres my code for mysql:

select sum(amount) from orderline group by orderlinedate between 'Datehere' and 'Datehere';

But my problem is how do i get it so that the "sum(amount) " will be placed in a textbox in vb.net 2010.
Here is my current code. This code is in a button and whats supposed to happen is that when i click on the button the sum will go to the textbox
but nothings happening.

Private Sub cmdCal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCal.Click
        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If

        Dim sqlstatement As MySqlCommand = New MySqlCommand
        sqlstatement.Connection = connection
        Dim sqlreader As MySqlDataReader

        Try
            sqlstatement.CommandText = "select sum(amount) from orderline group by orderlinedate " & _
                                         "between '" & txt1st.Text & "' and '" & txt2nd.Text & "';"

            sqlreader = sqlstatement.ExecuteReader()
            While (sqlreader.Read())
                txtTotal.Text = (sqlreader("amount"))
            End While

            sqlstatement.Dispose()
            sqlreader.Close()

        Catch ex As Exception

        End Try
    End Sub

So thats my problem guys. Some help would be appreciated

Recommended Answers

All 4 Replies

The sum function only return you 1 value. So the question is why do you need to use a group by? I am not sure if the sql works. As you are using a sum function, it will give you a new value in which you have not name it. Normally when you use a sum function you set it up with a name like:

select sum(amount) as amount from orderline

Then you can retrieve the value using the name amount.

Also, it is good to have a try catch when you open a connection and running your sql statement. That way if there is any problem, you can display the problem.

txtTotal.Text = (sqlreader("amount"))

Should be sqlreader("amount").ToString(). Though sqlreader("amount") should work as well in this scenario.

When expecting only 1 value and especially if you are assigning that value to a variable/textbox/label you should use ExecuteScalar.
This way you don't waste resources bringing in a reader for just 1 value and it's a lot easier to handle and control.

Try this:

        Try
            sqlstatement.CommandText = "select sum(amount) from orderline group by orderlinedate " & _
                                         "between '" & txt1st.Text & "' and '" & txt2nd.Text & "';"

           txtTotal.Text = sqlstatement.ExecuteScalar


            sqlstatement.Dispose()


        Catch ex As Exception
        'There is no point in catching the error and not displaying something. This leads to frustration as nothing hapens
        msgbox(ex.Message) 
        End Try

Hello Wen Cai. Your suggestion worked. Thanks alot and thank you to all who took the time to answer.

Glad to be of help. = )

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.