How to use select in vb.net 2010

[Iamkenny]

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

[wen_cai]

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.

[adam_k]

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

[Iamkenny]

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

[wen_cai]

Glad to be of help. = )