Member Avatar

I'm new to :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 ?

Recommended Answers

All 5 Replies

The synax is

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

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

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.


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)}))

And clean up.

Member Avatar

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 & "''"
        Dim dr2 As MySqlDataReader = cmd.ExecuteReader()
        Do While dr2.Read
            dgvreciept.Item.add(New ListViewItem({dr2.GetString(0), dr2.GetString(1), dr2.GetString(2)}))

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

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)})
Be a part of the DaniWeb community

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