0

I have a data table which has 12 items, I need to change the data type of 1st and 10th column

 DataTable dtExcel = null;
dtExcel = GetExcelContainerFor<TBL_TICKETING_ALTERNATE_CREDIT_CARD_LIST>().ImportFromExcel(excelInput.DataVaultFileName, excelInput.IsHeaderRowExists);

so dtExcel is the data table which has the 12 items

I am looping through each data row and trying to convert it to Int32

dtExcel.Select().ToList().ForEach(dataRow =>
                        {
                            dataRow[0] = (Convert.ToInt32(dataRow[0]));
                            dataRow[9] = (Convert.ToInt32(dataRow[9]));
                            
                        });

but as soon as the loops ends , the dtExcel data table has it's original data types.

I tried below coding as well but it didn't work as well(since it threw error as connot convert the datatype)

DataTable newTable = dtExcel.Clone();
                        for (int i = 0; i < dtExcel.Rows.Count; i++)
                        {
                            DataRow drNew = newTable.NewRow();
                            newTable.Columns[0].DataType = typeof(Int32);
                            newTable.Columns[9].DataType = typeof(Int32);
                            drNew.ItemArray = dtExcel.Rows[i].ItemArray;
                            newTable.Rows.Add(drNew);

}

How can I solve this issue? Do I need to create a new datable and loop through each items and then convert each and add?- if yes- can somebody share an example of how to achieve it?

Mint Money
  • 1
  • 2
  • 1
    You can't change the DataType of a column if it already has data. You'd need to copy the DataTable's data to a new one with the correct DataTypes. [Here's an example in VB](https://stackoverflow.com/a/50984859/8967612). – 41686d6564 stands w. Palestine Sep 02 '20 at 15:02
  • 1
    Does this answer your question? [How To Change DataType of a DataColumn in a DataTable?](https://stackoverflow.com/questions/9028029/how-to-change-datatype-of-a-datacolumn-in-a-datatable) – sr28 Sep 02 '20 at 16:08
  • I tried below coding but it threw exception as can't convert the data type DataTable newTable = dtExcel.Clone(); for (int i = 0; i < dtExcel.Rows.Count; i++) { DataRow drNew = newTable.NewRow(); newTable.Columns[0].DataType = typeof(Int32); newTable.Columns[9].DataType = typeof(Int32); drNew.ItemArray = dtExcel.Rows[i].ItemArray; newTable.Rows.Add(drNew); – Mint Money Sep 02 '20 at 16:19
  • @MintMoney You need to change the datatype of the column _**before**_ adding rows to it as shown in the two linked posts. – 41686d6564 stands w. Palestine Sep 02 '20 at 16:58
  • I tried that but ran into another issue, https://stackoverflow.com/questions/63710457/string-or-binary-data-would-be-truncated-the-statement-has-been-terminated-whi but this issue has been resolved, thank you appreciate your time – Mint Money Sep 02 '20 at 17:19

0 Answers0