Joe Adventa Joe Adventa - 2 months ago 13
Vb.net Question

How to add new column based on existing column in a datatable in VB?

I'm trying to build a code to process text file from a fingerprint machine into a report.

text file input example,

001;0000000000200004;01-07-2016;15:31;01;01


with semicolon delimiter,

machinecode;personid;dateDD-MM-YYYY;timeHH:MM;jobid;unused


this is my last successful code

Imports System.IO
Imports System.Windows.Forms

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim lines = IO.File.ReadAllLines("C:\Users\Adventa\Documents\Visual Studio 2010\Projects\AbsensiALU\AbsensiALU\Absen.txt")
Dim tblabsen = New DataTable
Dim colCount = lines.First.Split(";"c).Length

For i As Int32 = 1 To colCount
'Dim header As String
tblabsen.Columns.Add(New DataColumn("Column_" & i, GetType(String)))
Next
For Each line In lines
Dim objFields = From field In line.Split(";"c)

Dim newRow = tblabsen.Rows.Add()


newRow.ItemArray = objFields.ToArray()

Next
Dim table1 As New DataTable
table1 = tblabsen.Copy()

table1.Columns(0).ColumnName = "No. Mesin"
table1.Columns(1).ColumnName = "NIK"
table1.Columns(2).ColumnName = "Tanggal"
table1.Columns(3).ColumnName = "Jam"
table1.Columns(4).ColumnName = "Keterangan"

DataGridView1.DataSource = table1
DataGridView1.AutoResizeColumn(0)
DataGridView1.AutoResizeColumn(1)
DataGridView1.AutoResizeColumn(2)
DataGridView1.AutoResizeColumn(3)
DataGridView1.AutoResizeColumn(4)

DataGridView1.Columns(5).Visible = False


Dim rowData As String = ""

'dt is the name of the data table
For Each row As DataRow In table1.Rows
For Each column As DataColumn In table1.Columns
rowData = rowData & column.ColumnName & "=" & row(column) & " "
Next
rowData = rowData & vbNewLine & vbNewLine
Next



End Sub



Private Sub DataGridView1_CellContentClick_1(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick


End Sub

End Class


now i want to add a column into it that based on table1.columns(4) with probably an IF/CASE operator, so the new columns for example will be like this

Dim kete As String
Select Case tblabsen.Columns(4).GetType.ToString()
Case "00"
kete = "Masuk"
Case "01"
kete = "Pergi"
Case "02"
kete = "Kembali"
Case "03"
kete = "Pulang"
Case Else
kete = "Error"
End Select


assuming that 'kete' could be written into the new column.

Thanks in advance.

Answer

You have create your own DataTable, so you need to add one extra column first

For i As Int32 = 1 To colCount + 1
    'Dim header As String
    tblabsen.Columns.Add(New DataColumn("Column_" & i, GetType(String)))
Next

After that, you need to assign your value in ForEach

For Each line In lines
    Dim objFields = line.Split(";"c).ToList()
    Dim newRow = tblabsen.Rows.Add()

    Dim kete As String
    Select Case objField(4).GetType.ToString()
        Case "00"
            kete = "Masuk"
        Case "01"
            kete = "Pergi"
        Case "02"
            kete = "Kembali"
        Case "03"
            kete = "Pulang"
        Case Else
            kete = "Error"
    End Select
    objFields.Add(kete)

    newRow.ItemArray = objFields.ToArray()
Next

And also you need to handle this extra column in the rest of your code (i.e. resize, column name)

Comments