search for date inf vb.net from excel to datagrid

hendrikbez

I really need help with this. 1. I have a excel file with columns with text and some with only the date in it (07/16/2013). This date can be in more than one row. 2. I then have a form where I can display all the info in the form (Eg like if I choose server, it will show me that server info on the form with all the other details that goes with it. I get the info form my excel file. 3. I then have a button to search for info eg Server names, it then make a data grid view and show my all the servers that start with eg. Prasa. 4. Now my problem is I cannot get it to search for a date. I need help with this. Here is my main code and my button date search code. Is the a sample of this or can someone help me with this.

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

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 it

Mycroft Holmes

2 main issues, using excel as a data store (probably not going to change) and the main issue is that you are storing your dates as string/text. ALWAYS use the date or datetime data type for storing and manipulating date.

Never underestimate the power of human stupidity RAH

Lost User

Mycroft Holmes wrote:

you are storing your dates as string/text

I've lost count of the number of questions with that 'feature'. I wonder who is teaching these people?

Use the best guess

Mycroft Holmes

To be honest I don't think anyone is teaching them, I remember when I was learning I stored dates as text, I then discovered the data type, that was in the 80s mind you. If these people are just diving in to build some hobby app without ever having done any reading I would expect these questions. The seriously frightening thing is they may have been taught!

Never underestimate the power of human stupidity RAH

hendrikbez

Yes I do not have any training in vb.net. I am trying to lean it on my own, as the courses here in south Africa is very expensive. Where must the date be not string/text, I don’t understand where I should change it.