How to Filter a record using date range and show it to the data grid

[dimi231080]

hello,i have one application in vb net where i have 2 datetimepickers and one button and one datagrid.
When i press the button a sub is called(below) and it is supposed to take the datetimepicker1.text and datetimepicker2.text and display the existinf records between these dates .The column that stores the date(called-imerominia) is in varchar format.

Public Sub apo()
        conn()
        Try
            Dim query As String = Nothing
            mysqlconn = New MySqlConnection

            mysqlconn.Open()



            query = "Select * from patient where = imerominia BETWEEN >='" & DateTimePicker1.Value & "'AND imerominia <='" & DateTimePicker2.Value & " '"
            Dim querycomcheck As New MySqlCommand(query, mysqlconn)

            While reader.Read
                Dim imerominia As String = reader.GetString("imerominia")
                Dim imerominia1 As String = reader.GetString("imerominia")
                DateTimePicker1.Text = (imerominia)
                DateTimePicker2.Text = (imerominia1)

            End While





            sda.Fill(dbdataset)
            bsource.DataSource = dbdataset
            DataGridView1.DataSource = bsource
            sda.Update(dbdataset)
            reader = querycomcheck.ExecuteReader
        Catch ex As Exception
            MessageBox.Show("nothing")
        End Try

    End Sub

When executing it nothing is displayed.I have tried instead of datetimepicker.text to enter datetimepicker.value as well as to change in my datatbase the format of the column from varchar to date or datetime but again it skips off the query section and displays nothing.
The same thing but when searching only from one datetimepicker and not between the range ,WORKS perfectly..
I have been searching and testing many things and i am totally confused,any help would be really appeciated.

[EricFlamm]

If this line is copied accurately:

 "Select * from patient where = imerominia BETWEEN >='" & DateTimePicker1.Value & "'AND imerominia <='" & DateTimePicker2.Value & " '"

there's no way this would work - there's an extra "=" sign after "where", and the ">=" and "<=" are also incorrect (BETWEEN ... AND ...) replaces <= and >=. There's also an extra imerominia. So the query should read

 "Select * from patient where imerominia BETWEEN '" & DateTimePicker1.Value & "'AND  '" & DateTimePicker2.Value & " '"

However, I don't think this will work, since you're using strings and not date datatypes, so your database will end up using string comparison.

From the MySQL documentation:

For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

This would make your query look like:

"Select * from patient where  imerominia BETWEEN " & CAST(DateTimePicker1.Value AS DATE) & " AND " & CAST(DateTimePicker2.Value AS DATE)

Note that I also dropped the single quotes from the string and added a space before the word "AND"

[dimi231080]

Unfortunately i get 3 errors:it says 'cast is not declared ' and 'AS' is also underlined valid continuation or expression is expected.

Oh and sth else i forgot to mention in order to give a more clear view ,datetimepickers are declared in their properties as LONG,while now in my datatabase column-'imerominia' is varchar.

[dimi231080]

I just changed my dtatbase to format date and datetimepickers to custom format:yy-MM-dd and now roks ..thank you for you reply eric!