[RESOLVED] search listview by date format
i have a listview with columns:
Quote:
ID, Contact_Name, Contact_Number, DateAndTime, Messages
I want to search the listview using "Date" in DateAndTime Column.
using textbox_textChanged
Code:
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....
Re: search listview by date format
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.
Re: search listview by date format
i am using dateAndTime picker...
Code:
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.
Re: search listview by date format
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:
Quote:
What is the data type of the DateAndTime column in your database?
Re: search listview by date format
my data type now is Date/Time... (in my database)
i also changed my query code to this
Code:
Dim sqlQuery As String = "SELECT From SentItems WHERE DateAndTime like '%" & Format(DateTimePicker2.Value, "M/d/yyyy") & "%'"
Re: search listview by date format
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:
Code:
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?
Re: search listview by date format
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?...
Code:
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
Re: search listview by date format
its working now sir... thank for your help...
my working code:
Code:
Dim dt As String
dt = CDate(TextBox6.Text)
Dim sqlQuery As String = "SELECT * From SentItems WHERE DateAndTime like '%" & CDate(dt) & "%'"
Re: [RESOLVED] search listview by date format
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:
Code:
Dim sqlAdapter As New OleDbCommand("SELECT From SentItems WHERE DateAndTime = @DateAndTime", cn)
sqlAdapter.SelectCommand.Parameters.AddWithValue("@DateAndTime", DateTimePicker2.Value.Date)
Re: search listview by date format
Quote:
Originally Posted by
chrisTemper
its working now sir... thank for your help...
my working code:
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.
