[RESOLVED] search listview by date format

[chrisTemper]

i have a listview with columns:

ID, Contact_Name, Contact_Number, DateAndTime, Messages

I want to search the listview using "Date" in DateAndTime Column.

using textbox_textChanged

Try


      Dim sqlQuery As String = "SELECT From SentItems WHERE DateAndTime like '%" & TextBox6.Text & "%'"


            Dim sqlCommand As New OleDbCommand
            Dim sqlAdapter As New OleDbDataAdapter
            Dim Table As New DataTable

            With sqlCommand
                .CommandText = sqlQuery
                .Connection = cn

            End With
            With sqlAdapter
                .SelectCommand = sqlCommand
                .Fill(Table)

            End With
            lvSent.Items.Clear()

            For i = 0 To Table.Rows.Count - 1
                With lvSent
                    .Items.Add(Table.Rows(i)("ID"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(Table.Rows(i)("Contact_Name"))
                        .Add(Table.Rows(i)("Contacts_Number"))
                        .Add(Table.Rows(i)("DateAndTime"))
                        .Add(Table.Rows(i)("SentMessage"))
                        .Add(Table.Rows(i)("Status"))


                    End With

                End With
            Next

        Catch ex As Exception

        End Try

the format of date in textbox6 = M/d/yyyy

the format of date in listview = M/d/yyyy

the problem is every time I searched and the result found(matched). All items in listview appear also even it do not match the text in textbox. please help guys... thanks....

[jmcilhinney]

What is the data type of the DateAndTime column in your database? Your code suggests that it is textual, which is ludicrous when pretty much all databases have at least one data type specific to dates/times. If that is the case then you should fix your database schema if at all possible because it is broken.

On a similar note, why are you using a TextBox, which accepts and text at all, when there is a DateTimePicker control that is specifically for date/time input and display?

So, first up, can you fix those two issues or not? Once we know that then we can look at the specifics of what the correct VB code should look like.

[chrisTemper]

i am using dateAndTime picker...

Private Sub DateTimePicker2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DateTimePicker2.TextChanged

        TextBox6.Text = Format(DateTimePicker2.Value, "M/d/yyyy")

    End Sub

so the result in text was like (ex. 4/3/2014)

and I have the result in my listview 4/3/2014 but even other items shows.. but if the result do not matched to any item the result is zero. my listview is empty.


I am saving the dataAndTime in my database like this.

DataAndTime.Today

[jmcilhinney]

If you have a DateTimePicker then why are you using a TextBox at all? Why not just use the DateTimePicker? If you want it to display that particular format then set the Format property to Custom and the CustomFormat property to "M/dd/yyyy". Done.

As for the rest, I asked you a question and you didn't answer it, which is wasting both your time and mine so is very annoying. The very first thing I said in my previous post was:

What is the data type of the DateAndTime column in your database?

[chrisTemper]

my data type now is Date/Time... (in my database)

i also changed my query code to this

Dim sqlQuery As String = "SELECT From SentItems WHERE DateAndTime like '%" & Format(DateTimePicker2.Value, "M/d/yyyy") & "%'"

[jmcilhinney]

Right then. The first point is that you don't use LIKE with anything but text. If you're comparing a date to a date then you use =, <, >, etc. Your query should look something like this:

Dim sqlQuery As String = "SELECT From SentItems WHERE DateAndTime = @DateAndTime"

That brings up another possible issue though. Your column name is DateAndTime, which implies that it contains a date and a time, while you're filter seems to be using a date only. Is it the case that your records contain both a date and a time and you want to retrieve all those where the date is a particular value regardless of the time?

[chrisTemper]

i am using just the date.. in my database it only shows the date..

here is my new code...

how can i search the date I picked in my dateandtime picker or in the output in my textbox?...

Dim sqlQuery As String = "SELECT From SentItems WHERE DateAndTime = @DateAndTime"
            Dim DateAndTime As OleDbParameter = New OleDbParameter("@DateAndTime", OleDbType.Date)
            


            Dim sqlCommand As New OleDbCommand
            Dim sqlAdapter As New OleDbDataAdapter
            Dim Table As New DataTable

            With sqlCommand
                .CommandText = sqlQuery
                .Connection = cn

            End With
            With sqlAdapter
                .SelectCommand = sqlCommand
                .Fill(Table)

            End With
            lvSent.Items.Clear()

            For i = 0 To Table.Rows.Count - 1
                With lvSent
                    .Items.Add(Table.Rows(i)("ID"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(Table.Rows(i)("Contact_Name"))
                        .Add(Table.Rows(i)("Contacts_Number"))
                        .Add(Table.Rows(i)("DateAndTime"))
                        .Add(Table.Rows(i)("SentMessage"))
                        .Add(Table.Rows(i)("Status"))


                    End With

                End With
            Next

[chrisTemper]

its working now sir... thank for your help...

my working code:

            Dim dt As String
            dt = CDate(TextBox6.Text)
            Dim sqlQuery As String = "SELECT * From SentItems WHERE DateAndTime like '%" & CDate(dt) & "%'"

[jmcilhinney]

If you're only using the date then I have to question why the column is named DateAndTime but that's something for you to worry about. As for your code, you can simplify it and use the date selected by the user like this:

Dim sqlAdapter As New OleDbCommand("SELECT From SentItems WHERE DateAndTime = @DateAndTime", cn)

sqlAdapter.SelectCommand.Parameters.AddWithValue("@DateAndTime", DateTimePicker2.Value.Date)

[jmcilhinney]

its working now sir... thank for your help...

my working code:

            Dim dt As String
            dt = CDate(TextBox6.Text)
            Dim sqlQuery As String = "SELECT * From SentItems WHERE DateAndTime like '%" & CDate(dt) & "%'"

No. That's terrible code.