VB.NET

Date retrieval problem via access database

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: May 2009

Posts: 20

Reputation: BlkR is an unknown quantity at this point

Solved Threads: 0

BlkR

Offline

Newbie Poster

Date retrieval problem via access database

Aug 3rd, 2009

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.....

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
 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
 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

Help with Code Tags

VB.NET Syntax (Toggle Plain Text)

txtdate.Text = slist.Text

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

•

Join Date: Feb 2009

Posts: 3,373

Reputation: sknake has much to be proud of

Solved Threads: 608

sknake

Offline

.NET Enthusiast

Re: Date retrieval problem via access database

Aug 3rd, 2009

Use parameterized queries to avoid date formatting issues.

Example:

 Help with Code Tags 
 vb.net Syntax (Toggle Plain Text) 
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
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