VS 2015 Change property of a column in datagrid-VBForums
Results 1 to 3 of 3

Thread: Change property of a column in datagrid

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2015
    Posts
    12

    Change property of a column in datagrid

    Hello,
    I am wondering if anybody can have a look to my issue and help me with the code.
    here is the situation:
    I have a simple but old access '97 DB (mdb) who is feed by an third party program.
    I want to collect some data out of it present it in a datagrid and export this into a excel file.

    This all works fine, except for one thing:
    One of the column (0) has dates in it (ex.25/11/2015) but the mdb is missconfigured and gives a 5digit (numbers) in stead of.

    now may question is how can change the gridview so that it adapts the colomn and transforms the 5digits into a real date.

    Here is my code:
    Code:
    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        End Sub
    
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Progwsrb\Data\WSRB.mdb"
    
        Dim MyConn As OleDbConnection
        Dim da As OleDbDataAdapter
        Dim ds As DataSet
        Dim tables As DataTableCollection
        Dim source1 As New BindingSource
    
        Private Sub btn_filter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_filter.Click
            MyConn = New OleDbConnection
            MyConn.ConnectionString = connString
            ds = New DataSet
            tables = ds.Tables
            da = New OleDbDataAdapter("Select AfvalDatum, AfvalUur, AfvalWeegnr, AfvalNummerplaat, AfvalProduct, AfvalAantalkg, AfvalGemeente, AfvalKlantCode from [TblAfval] WHERE AfvalKlantCode = 'RABACO' ORDER BY AfvalKlantCode", MyConn) 'Change items to your database name
    
            da.Fill(ds, "TblAfval") 'Change items to your database name
            Dim view As New DataView(tables(0))
            source1.DataSource = view
            DataGridView1.DataSource = view
    
            DataGridView1.Columns(0).ValueType = GetType(Date)
    
        End Sub
    Here are 2 screenshot out of excel to make it more clear:
    Name:  in_excel_org_colomA.jpg
Views: 91
Size:  32.1 KB


    Name:  wijz_excel_eigenschappen_colomA.jpg
Views: 82
Size:  46.0 KB

    Here is a screenshot of the actual gridview
    Name:  screenshot_VB.jpg
Views: 75
Size:  37.0 KB

    Many thanks for all your help

    Kindly regards,
    Koen

  2. #2
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Change property of a column in datagrid

    Koen,

    The integer value you are seeing for the date is referred to as an Office Automation Date.

    To see this, give this code a try:
    Code:
    Dim d As DateTime = DateTime.FromOADate(42130)
    and you will see that you can retrieve the proper date value.

    Please answer this question before I recommend a solution for the DGV:

    Do you need to edit the date values in the DGV?

    Edit:

    Never mind answering that question. On reflection it will be best to just transform the OADate to a .Net Date when read from Access.
    Last edited by TnTinMN; Nov 10th, 2015 at 11:42 AM.

  3. #3
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Change property of a column in datagrid

    Something like this should work. It uses a datareader to first set the DataTable schema. It then changes the type of the column with the AODate to DateTime and finally fills the table converting the AODate to a DateTime as it reads each record.

    Code:
    Dim dt As New DataTable
    Using conn As New OleDb.OleDbConnection("Your Connection String"), _
          cmd As New OleDb.OleDbCommand("Your Selection Query", conn)
       conn.Open()
       Using rdr As IDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
          For Each r As DataRow In rdr.GetSchemaTable().Rows
             Dim col As New DataColumn()
             With col
                .ColumnName = CStr(r("ColumnName"))
                .DataType = CType(r("DataType"), Type)
                .ReadOnly = CBool(r("IsReadOnly"))
                .AutoIncrement = CBool(r("IsAutoIncrement"))
                .Unique = CBool(r("IsUnique"))
             End With
             dt.Columns.Add(col)
          Next
    
          Dim dateFieldIndex As Int32 = rdr.GetOrdinal("AfvalDatum")
          dt.Columns(dateFieldIndex).DataType = GetType(DateTime)
          Dim values(0 To rdr.FieldCount - 1) As Object
          Do While rdr.Read()
             rdr.GetValues(values)
              values(dateFieldIndex) = DateTime.FromOADate(CDbl(values(dateFieldIndex)))
    
             dt.Rows.Add(values)
          Loop
       End Using ' rdr
       conn.Close()
    End Using

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.