How to change column data type in a data table using vb.net

Question

How can I change the data type of data column from an input data table (already filled) in VB.NET, then I'll put the code in a Blue Prism Code Stage where I have in input:

1. Name of the field (column) that I want to change the data type

2. The data type that I want to convert

3. Input Collection (data table)

Example:

Dim InputDT As New DataTable
InputDT.Columns.Add(New DataColumn("test"))
dt.Columns("test").DataType = GetType(Date)

Thanks

Answer 1

If the DataTable is already filled with data, you cannot change the type of any of its columns. If you try to do that, you will receive an ArgumentException with a very straightforward message:

Cannot change DataType of a column once it has data.

A good alternative would be to create a new DataTable (or clone the existing one), change the column type, and then fill it with the data from the old DataTable.

Something like this should work:

Dim InputDT As New DataTable
InputDT.Columns.Add(New DataColumn("test"))
InputDT.Rows.Add("1/1/2018")

Dim clonedDT As DataTable = InputDT.Clone()
clonedDT.Columns("test").DataType = GetType(Date)
For Each row As DataRow In InputDT.Rows
    clonedDT.ImportRow(row)
Next

Note that in order for this to work, the data in that column must be valid for the new type.

Edit:

To handle the case where the existing values cannot be casted to the new type, you can use a Try.. Catch statement like the following:

' ...
Try
    For Each row As DataRow In InputDT.Rows
        clonedDT.ImportRow(row)
    Next
Catch ex As ArgumentException
    ' An error occurred, use 'ex.Message' to display the error message. Example:
    Console.WriteLine(ex.Message)
End Try