How to get datetimepicker to work

hendrikbez

Active member
Joined
Nov 24, 2005
Messages
35
Location
South Afica
Programming Experience
1-3
0 down vote favorite

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.

Hera are my load page info
VB.NET:
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

Here is my button code, I want to display any date that I want.
VB.NET:
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
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
To clarify, are the date values actually being imported from the Excel file as type String or as Date? Also, does the data contain just the date or the time as well?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
I think the values are imported as type string.
I'm more interested in what they are than what you think they are. If I go by what you think they are and it turns out that you were wrong then I've wasted my time and yours. Find out for sure.
I just want the date for this.
I didn't ask you what you want. I asked you what the data actually contains.
 

hendrikbez

Active member
Joined
Nov 24, 2005
Messages
35
Location
South Afica
Programming Experience
1-3
I'm more interested in what they are than what you think they are. If I go by what you think they are and it turns out that you were wrong then I've wasted my time and yours. Find out for sure.I didn't ask you what you want. I asked you what the data actually contains.

1. it is by string.
2. Is should have been date, but my spelling checker change it to data (sorry for this one).
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
And I'm still waiting for an answer to my question. I asked you whether the data contains just the date or the date and time as well. It's a simple question and should be simple enough to answer.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
Sorry, I did say in one of the message it is the date only, I don't need the time.
No you didn't say that. I don't care what you WANT, as I said earlier. All I care about is what the data CONTAINS. Anyway, we seem to have finally established that the text read from Excel contains only date and no time. Now I just have to re-familiarise myself with the original question and I can try to provide a solution.
 

hendrikbez

Active member
Joined
Nov 24, 2005
Messages
35
Location
South Afica
Programming Experience
1-3
No you didn't say that. I don't care what you WANT, as I said earlier. All I care about is what the data CONTAINS. Anyway, we seem to have finally established that the text read from Excel contains only date and no time. Now I just have to re-familiarise myself with the original question and I can try to provide a solution.

Hi Jmcihinney

Just want to know it you did have time to look at this for me.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
Load your data into a DataTable, bind that to a BindingSource and bind that to your DataGridView. Set the Format of your DataTimePicker to Custom and the CustomFormat to the same that that the Excel data is being imported in, e.g. "dd/MM/yyyy". Handle the ValueChanged event of your DateTimePicker and use code like this to filter the data:
myBindingSource.Filter = String.Format("SomeColumn = '{0}'", myDateTimePicker.Text)
 
Top Bottom