I want to filter the values in the listview using the date range selected in two datetime picker.
Here is my code:
Dim reportstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Fe\Desktop\SADsystem\SADsystem\stockroom.accdb;Jet OLEDB:Database Password=stockroom"
Dim reportconn As New OleDbConnection
Dim date1 As Date
Dim date2 As Date
date1 = Convert.ToDateTime(DateTimePicker1.Value)
date2 = Convert.ToDateTime(DateTimePicker2.Value)
Try
reportconn.ConnectionString = reportstring
reportconn.Open()
Dim strCmd As String = "SELECT * FROM itemequipment WHERE DateofAcquisition BETWEEN '" & _
Format(date1, "MM/dd/yyyy") & "' AND '" & _
Format(date2, "MM/dd/yyyy") & "' ORDER BY DateofAcquisition ASC;"
Dim SqlCommand As New OleDbCommand
Dim SqlAdapter As New OleDbDataAdapter
Dim USERTABLE As New DataTable
With SqlCommand
.CommandText = strCmd
.Connection = reportconn
'.Parameters.AddWithValue("@dt1", DateTimePicker1.Text.Trim)
'.Parameters.AddWithValue("@dt2", DateTimePicker2.Text.Trim)
End With
With SqlAdapter
.SelectCommand = SqlCommand
.Fill(USERTABLE)
End With
main.ListView1.Items.Clear()
For i = 0 To USERTABLE.Rows.Count - 1
With main.ListView1
.Items.Add(USERTABLE.Rows(i)("ID"))
With .Items(.Items.Count - 1).SubItems
.Add(USERTABLE.Rows(i)("PropertyNumber"))
.Add(USERTABLE.Rows(i)("Article"))
.Add(USERTABLE.Rows(i)("Description"))
.Add(USERTABLE.Rows(i)("Unitofmeasure"))
.Add(USERTABLE.Rows(i)("UnitValue"))
.Add(USERTABLE.Rows(i)("Remarks"))
.Add(USERTABLE.Rows(i)("DateofAcquisition"))
.Add(USERTABLE.Rows(i)("BalanceperCardQTY"))
.Add(USERTABLE.Rows(i)("OnhandpercountQTY"))
.Add(USERTABLE.Rows(i)("TotalAmount"))
End With
End With
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
it displays an error "Data type mismatch in criteria expression." The data type of DateofAcquisition in my database is set to date/time in short format. Please help me fix my code