Filter datagridview with datetimepicker

[lupao]

Hello everybody, im new..I want to filter my datagridview with datetimepicker.. I have 2 datetimepicker in my form and a datagridview. I want to filter my datagridview by date using datetimepicker,. what I did was I put the value of my two datetimepicker into two texboxes ive done it evrytime i change the value of my DTpicker, so, the input is not a problem,. but im confused on how to code it for filtering my datagridview with the input on my textbox where the 1st textbox is the start date and the 2nd textbox is the end date..

pleease help me...

here's the code for my query

Dim d1 As String = Me.TextBox2.Text
Dim d2 As String = Me.TextBox3.Text

Dim query As String
query = "select * from attendance where AttendanceDate like '" & ???? & "'"
Dim connection As New MySqlConnection(connStr)
Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(query, connection)
Dim dset As New DataSet
Dim dadp As New MySql.Data.MySqlClient.MySqlDataAdapter
dset.Clear()
connection.Open()
dadp.SelectCommand = cmd
dadp.Fill(dset, "attendance")
DataGridView1.DataSource = dset.Tables(0)

the textboxes hir has already the value of my DTpicker..

 

[rcombs4]

I would highly recommend using parameterized queries. They are much easier to read and safer.

With that said, in my line of work I use that functionality ALL the freaking time. I almost always go with a where clause like:

WHERE AttendanceDate BETWEEN @SDate AND @EDate

Only thing is that the BETWEEN functionality gets a little strange with dates with a time of 12:00:00AM.

If I had to run this query I would...

Dim query As String = "select * from attendance where AttendanceDate BETWEEN @SDate AND @EDate"
Dim connection As New MySqlConnection(connStr)
Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(query, connection)
cmd.parameters.addwithvalue("@SDate",txtTextBox2.text)
cmd.parameters.addwithvalue("@EDate",txtTextBox3.text)
Dim dset As New DataSet
Dim dadp As New MySql.Data.MySqlClient.MySqlDataAdapter

connection.Open()
dadp.SelectCommand = cmd
dadp.Fill(dset, "attendance")
DataGridView1.DataSource = dset.Tables(0)

Of course you might want to verify that the textboxes are in a valid datetime format.

 

[rcombs4]

Also, the BETWEEN functionality works as Inclusive for the first parameter and exclusive with the second.

Meaning, it includes the first date in the filter but excludes the second date. So to search today(2/16/2010) you would have:
SDate: 2/16/2010
EDate: 2/17/2010

 

[lupao]

thank you so much men...you really did help me...

 

[lupao]

hello rcombs4..

i have a little problem dude,,

in filtering my datagridview..it doesn't show all the result that must be shown..for example..i my start date is 2/1/2010 and my end date is 2/28/2010, or in short i would like to filter a record for a whole month like this one..

it doesn't include the dates like 2/3/2010, 2/4/2010, 2/5/2010, 2/6/2010 to 2/9/2010.
it will only include the days starting from 1 and 2 like 2/1/2010 and 2/10/2010 to 2/28/2010..
and if my start date is 2/1/2010 end date 2/9/2010..the result will also include the records that does not belong to the start and end input with 2/10/2010 to 2/19/2010, and 2/20/2010 to 2/28/2010..


please help me again, i really need this..please please rcombs4..

 

[rcombs4]

*to use the BETWEEN function you have to go an extra date. Your second date should be 3/1/2010. The DB interprets '2/28/2010' as '2/28/2010 12:00:00' Meaning the very beginning of the 28th. So your query will not give you any day for the 28th.*

But to address your issue about showing very strange dates...

That happens when the field you are trying to use is not the correct data type.

Logically you are tying to say give me all data that has an AttendanceDate for the month of February. But the DB doesn't know you are working with dates. So it is looking at everything as a string/varchar.

Make sure you specify that the field is a date and everything should magically start working.

 

[lupao]

Your right rcombs4..I figured that out a while ago..the data type of my field is varchar..but i'm having a dificult time changing it..

does this mean the code has no problem?only the data type of my field right?
i'm really a newbie..

here's my code for my textbox and datetimepicker, I really hope that this is also right..

Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged
Dim obj As Date
Dim str As String
obj = DateTimePicker1.Value
str = obj.ToShortDateString()
TextBox2.Text = obj
End Sub

thanks rcombs4, Im hoping for ur consideration and a reply..
ure really a big help.......

 

[lupao]

I'm sorry but I desperately need your help..
here is my existing code that reads the data from another DB table which is employee..and insert the date and EmpID into the table attendance1..

I really don't know what to do in order to insert the date in my table attendance1..

the previous data type of attendancedate is VARCHAR, and I've already changed it into DATE, and I've also tried everything I could to change the code inorder to insert the date properly on my attendancedate field..but I'm already worn out of idea..I hope you could help me with this one also..

Private Sub createRec()

Try
Dim rowsEffected As Integer = 0
Dim connection As New MySqlConnection(connStr)
Dim query As String = "Select * from employee"
Dim cmd As New MySqlCommand(query, connection)
Dim dr As MySqlDataReader
connection.Open()
rowsEffected = cmd.ExecuteNonQuery()
dr = cmd.ExecuteReader

While (dr.Read())

Dim id As String = dr.GetString("EmployeeID")
Dim d8d As String = Date.Now.Date.ToShortDateString
Dim q2 As String = "Insert into attendance1 (EmployeeID, AttendanceDate) VALUES ('" & id & "','" & d8d & "')"
Dim con As New MySqlConnection(connStr)
Dim cm As New MySqlCommand(q2, con)
con.Open()
cm.ExecuteNonQuery()
con.Close()

End While
connection.Close()

Catch ex As Exception

' MsgBox("Record Exits!", MsgBoxStyle.Information)

End Try
End Sub