Datertime picker with excel

[hennel]

I Have a excel sheet with dates that I want to use datetimepicker with (only date needed). I just want to click on datetimepicker, choose a date and it must show me the info in my data grid view. this is my button code.

I have a txt box that I have tried, but cannot get the date, I have now put a datetimepicker box, but cannot get ii to work.

any help on this.

 Private Sub BtnSearchDateOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearchDateOut.Click

        For i As Integer = 0 To dtGlobal.Rows.Count - 1
            If IsDBNull(dtGlobal.Rows(i)("Dateout")) Then
                dtGlobal.Rows(i)("Dateout") = ""
            End If
        Next

        Dim query = From item In dtGlobal.AsEnumerable() Where item.Field(Of String)("Dateout").StartsWith(txtSearchDateOut.Text) Select item
        If query.Count > 0 Then
            Dim newDT As DataTable = query.CopyToDataTable()

            MsgBox(newDT.Rows.Count.ToString() & " Date out found.")

            Dim frm As New Form()
            Dim dgv As New DataGridView()
            dgv.DataSource = newDT
            dgv.Refresh()
            frm.Controls.Add(dgv)
            dgv.Dock = DockStyle.Fill
            frm.Size = New Size(1400, 700)
            frm.Show()

        Else
            MsgBox("There is no Date for this found.")
        End If

    End Sub

[hennel]

Any help on this, all I need it to find out how choose a date like 07/07/2013, and then display the date, even if the date is in more than once.

Here are my load page code for this as well

Imports System.Data.OleDb

Public Class Tapes_info
    Private dtGlobal As New DataTable


    Private Sub Tapes_info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dtGlobal.Clear()
        Using cn As New OleDb.OleDbConnection
            Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = IO.Path.Combine(Application.StartupPath, "Backuptapes.xls"), .Provider = "Microsoft.ACE.OLEDB.12.0"}
            Builder.Add("Extended Properties", "Excel 12.0; IMEX=1;HDR=No;")
            cn.ConnectionString = Builder.ConnectionString
            cn.Open()
            Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}

                cmd.CommandText = "SELECT TOP 5130 F1 As Tapes, F2 As Containere, F3 as ContainerRef, F4 as DateOut FROM [Tapese$]"
                Dim dr As System.Data.IDataReader = cmd.ExecuteReader
                dtGlobal.Load(dr)
                LstTape.DisplayMember = "Tapes"
                LstTape.DataSource = dtGlobal
                txtContainer.DataBindings.Add("Text", dtGlobal, "Containere")
                txtContainerRef.DataBindings.Add("Text", dtGlobal, "ContainerRef")
                txtDateOut.DataBindings.Add("Text", dtGlobal, "Dateout")

            End Using
        End Using
    End Sub

[jkvt]

My guess is the date value in the database is formatted slightly different than the format of your date that the user enters (ie 07 vs 7). If you can convert the db date field to a date type, that would be best. If that is possible, this should work:

Dim dtConverted As DateTime

dtConverted = DateTimePicker1.Value

From item In dtGlobal.AsEnumerable() Where item.Field(Of DateTime) = dtConverted

If you cannot change the db field type, I would look to see if there is a way to parse the string version of the date on the db into a DateTime type, then setting that equal to dtConverted (remember dtConverted will have time in it too, so additional parsing might be necessary if that is going to get in the way). Mixing string and date types can be messy and lead to problems down the road.

[jkvt]

I made a mistake on the last line of code.. forgot the field name. It might be helpful.

From item In dtGlobal.AsEnumerable() Where item.Field(Of DateTime)("Dateout") = dtConverted

You might need a .ToString() after ("Dateout"). Not completely sure. I usually do this in C#.

[hennel]

Thank you for trying to help me. I have used your code, but get this error "Specified cast not valid"

 Private Sub BtnSearchDateOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearchDateOut.Click
  Dim dtConverted As DateTime
        dtConverted = DateTimePickerOutDate.Value
        Dim query = From item In dtGlobal.AsEnumerable() Where item.Field(Of DateTime)("Dateout") = dtConverted

        For i As Integer = 0 To dtGlobal.Rows.Count - 1
            If IsDBNull(dtGlobal.Rows(i)("Dateout")) Then
                dtGlobal.Rows(i)("Dateout") = ""
            End If
        Next

        If query.Count > 0 Then

            Dim newDT As DataTable = query.CopyToDataTable()
            MsgBox(newDT.Rows.Count.ToString() & " Date out found.")
            Dim frm As New Form()
            Dim dgv As New DataGridView()
            dgv.DataSource = newDT
            dgv.Refresh()
            frm.Controls.Add(dgv)
            dgv.Dock = DockStyle.Fill
            frm.Size = New Size(1400, 700)
            frm.Show()

        Else
            MsgBox("There is no Date for this found.")
        End If
    end sub 

[jkvt]

Hmm. Which line do you get that error on?

[hennel]

Sorry, it is on line 4

[hennel]

Still waiting for help on this..