How to change column data type in a DataTable that contains data?

Question

How can I change the data type of data column from an input DataTable (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)

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. To handle the case where the existing values cannot be cast 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