How to display data between two dates in mysql vb-CodePudding

I have a form that is responsible for displaying sales that were made based on date. I have got two date pickers FROM & TO. What I want is when I want to search data example from 12 to 19th it only displays 13th and 19th, I want it to show dates between 12 and 19 i.e. 12th,13th,14th,15th,....till 19th, But it only shows two dates 13th and 19th which I haven't asked in the date picker. I selected 12 and 19 and it displayed 13 and 19. In my database, I can 12th date is there on the database. I tried to check my code where I went wrong and followed the same MySQL syntax for selecting data from the database based on data.

This is my code:

 Private Sub btnShowAll_Click(sender As Object, e As EventArgs) Handles btnShowAll.Click
    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString = "server=localhost;userid=root;password=root;database=golden_star"

    ''connecting data grid with the database
    Dim Sda As New MySqlDataAdapter
    Dim dbdataset As New DataTable
    Dim bSource As New BindingSource


    Try
        MysqlConn.Open()
        Dim Query As String
        Dim sum As Decimal = 0

        Query = "select * from sales where date between '"   FromDate.Text   "' and '"   ToDate.Text   "'"

        Command = New MySqlCommand(Query, MysqlConn)
        Sda.SelectCommand = Command
        Sda.Fill(dbdataset)
        bSource.DataSource = dbdataset
        DataGridView1.DataSource = bSource
        Sda.Update(dbdataset)


        For i = 0 To DataGridView1.Rows.Count - 1
            sum  = DataGridView1.Rows(i).Cells(8).Value
        Next

        lblSum.Text = sum
        MysqlConn.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)

    Finally
        MysqlConn.Dispose()
    End Try


End Sub

THIS IS THE OUTPUT OF THE CODE: https://snipboard.io/PdpcUz.jpg

I have already declared the MySQL command and the imports in my code above.

I don't know where I went wrong or have I missed a piece of code somewhere? Please help. Thank you.

CodePudding user response：

This is how I would write that code:

Query = "select * from sales where date >= @init AND date <= @end"
Command = New MySqlCommand(Query, MysqlConn)
Command.Parameters.Add("@init", MySqlDbType.Date).Value = From.Value.Date
Command.Parameters.Add("@end", MySqlDbType.Date).Value = To.Value.Date        
Sda.SelectCommand = Command
Sda.Fill(dbdataset)
bSource.DataSource = dbdataset
DataGridView1.DataSource = bSource

Now the query text has no more concatenations but just parameters placeholders and the search is executed using the standard operators.
The command itself is loaded with two parameters matching the placeholders'name. Important part is the parameter type that should match the database column type and finally I take the Value property that is a DateTime and not a string representation of it and use only the Date part of that value