How to select data between two dates from mysql DB

[සශික]

I'm new to vb.net :rolleyes: I have form and it contain two date time pickers(dtpfrom & dtpto) and lable (lbltotal) and button (btnview)
I want to pick data between two dates from mysql table.
mysql table name is 'daily_income'

can anyone help me ?

[Reverend Jim]

The synax is

SELECT * FROM daily_income
 WHERE someDate BETWEEN '2015-12-17' AND '2016-01-10'

[සශික]

yah I know syntax but I had problems it coding with vb.net... :/ can anyone help more ?

[Reverend Jim]

You'll need to add

Imports MySql.Data.MySqlClient

Then you'll need a connection string. Typically something like

Dim con As New OleDbConnection("Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;")

Patch in your own values for myServerAddress, myUsername and myPasswword. Then you need a command.

Dim cmd As New OleDbCommand("", con)    
cmd.CommandText = "SELECT * from daily_income" _
                  " WHERE mydatefield BETWEEN '2015-12-01' AND '2015-12-31'"

Then you have to open the connection.

con.Open()

Read all the records returned

Dim rdr As OleDbDataReader = cmd.ExecuteReader()

Do While rdr.Read
    ListView1.Items.Add(New ListViewItem({rdr.GetString(0), rdr.GetString(1), rdr.GetString(2)}))
Loop

And clean up.

rdr.Close()
con.Close()

[සශික]

okay I tried with using mine variables. but there is error under the "datagridview.items"

Dim cmd As New MySqlCommand("", con)
        cmd.CommandText = "SELECT * from daily_income WHERE adten BETWEEN '" & dtpstart.Value & "' AND '" & dtpend.Value & "''"
        con.Open()
        Dim dr2 As MySqlDataReader = cmd.ExecuteReader()
        Do While dr2.Read
            dgvreciept.Item.add(New ListViewItem({dr2.GetString(0), dr2.GetString(1), dr2.GetString(2)}))
        Loop
        dr2.Close()
        con.Close()

what is 'GetString(0)' ? why it use ?

[Reverend Jim]

GetString(num) returns the item at that column in the current record. You are going to have a problem though because you are creating a new ListViewItem (as per my example) but trying to add that into a DataGridView. Add the rows as follows:

dgvreceipt.Rows.Add(New String() {dr2.GetString(0), dr2.GetString(1), dr2.GetString(2)})