Date retrieval problem via access database

[BlkR]

Hi,

Having troubles with the conversion of date in a textbox i think.... currently txtDate.text has the date format of dd/mm/yyyy and im trying to grab data from the database that is equal to that date and put it in a listview. Problem Im haivng is the conversion from MM/dd/yyyy and dd/MM/yyyy which is so damn frustrating. My access database has the format of dd/MM/yyyy and yet it won't retrieve the data needed

here is part of the code.....

If dtpSelection.Checked And dtpSelection2.Checked Then
            MyConn = New ADODB.Connection
            MyConn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MenuItems.mdb;")
            MyConn.Open()
            'MessageBox.Show(FormatDateTime(dtpSelection.Value.ToShortDateString))
            MyRecSet = MyConn.Execute("SELECT Item_ID, ItemName, Price, Qty, Total, Description, OrderDate FROM trans2 where (((trans2.OrderDate) = #" & txtdate.Text & "#))")

            Do Until MyRecSet.EOF
                var = MyRecSet.Fields.Item("Item_ID").Value
                var2 = MyRecSet.Fields.Item("ItemName").Value 'variable2 = MyRecSet.Fields.Item("column2").Value
                var3 = MyRecSet.Fields.Item("Price").Value
                var4 = MyRecSet.Fields.Item("Qty").Value
                var5 = MyRecSet.Fields.Item("Total").Value
                var6 = MyRecSet.Fields.Item("OrderDate").Value
                var7 = MyRecSet.Fields.Item("Description").Value.ToString
                ' lvItems.Clear()
                lvwItem2 = lvItems.Items.Add(var6)
                lvwItem2.SubItems.Add(var)
                lvwItem2.SubItems.Add(var2)
                lvwItem2.SubItems.Add(var3)
                lvwItem2.SubItems.Add(var4)
                lvwItem2.SubItems.Add(var5)
                lvwItem2.SubItems.Add(var7)
                ' Listbox2.AddItem(variable2)
                MyRecSet.MoveNext()
            Loop
            MyConn.Close()
        End If

the problem i think is that the the value inside txtdate.text was retrieve from another listview and was grabbed like this

txtdate.Text = slist.Text

any help would good thanks guys..... and ive tried these cdate, formatdatetime etc

[sknake]

Use parameterized queries to avoid date formatting issues.

Example:

Private Sub DisplayPersonData(ByVal first_name As String, _
    ByVal last_name As String)
    ' Open the connection.
    connUsers.Open()

    ' Make a Command for this connection
    ' and this transaction.
    Dim cmd As New OleDb.OleDbCommand( _
        "SELECT * FROM People WHERE FirstName=? AND " & _
            "LastName=?", _
        connUsers)

    ' Create parameters for the query.
    cmd.Parameters.Add(New _
        OleDb.OleDbParameter("FirstName", first_name))
    cmd.Parameters.Add(New OleDb.OleDbParameter("LastName", _
        last_name))

    ' Execute the query.
    Dim db_reader As OleDbDataReader = _
        cmd.ExecuteReader(CommandBehavior.SingleRow)

    ' Display the results.
    If db_reader.HasRows Then
        db_reader.Read()
        txtFirstName.Text = _
            db_reader.Item("FirstName").ToString
        txtLastName.Text = _
            db_reader.Item("LastName").ToString
        txtStreet.Text = db_reader.Item("Street").ToString
        txtCity.Text = db_reader.Item("City").ToString
        txtState.Text = db_reader.Item("State").ToString
        txtZip.Text = db_reader.Item("Zip").ToString
    Else
        For Each ctl As Control In Me.Controls
            If TypeOf ctl Is TextBox Then ctl.Text = ""
        Next ctl
    End If

    ' Close the connection.
    connUsers.Close()
End Sub

Borrowed from: http://www.vb-helper.com/howto_net_db_parameterized_query.html