Question
In my .Net project I am loading in a CSV file so I can take that data, do some stuff with it, and fire out the results into an excel file.
I'm handling this through a data table inside my code. In my results file, I have added a new column which I call 'resetdate' and I do it like this:
nettingTable.Columns.Add("resetdate");
The data is cleansed of any empty cells and the value set to a DBNull so that my database doesn't through an error. That code looks like this:
public DataTable PreProcess(DataTable dt)
{
foreach (DataColumn col in dt.Columns)
if (_config.ColumnNames.Contains(col.ColumnName))
{
foreach (DataRow row in col.Table.Rows)
{
// We only care about the date
// This trims out the any time values that may have automatically been added
if(row[col].ToString().Length > 10)
{
row[col] = row[col].ToString().Substring(0, 10);
}
if (row[col].ToString().Length == 0)
{
row[col] = DBNull.Value;
}
}
}
return dt;
}
Nothing ground breaking here and this has long worked. However, my new column, resetdate is meant to get populated from a column in my CSV file called nextresetdate which I am doing with the following code:
foreach (DataRow dr in nettingTable.Rows)
{
dr["resetdate"] = dr["nextresetdate"];
}
Once everything has been populated, it gets sent to my database where I have a table with a column called 'ResetDate' which has a value of 'DateTime' Again, this has all worked before without issue.
Now, when I try to run my code with the above changes, I get the following error message:
Rows: 2-453 Error: The given value of type String from the data source cannot be converted to type int of the specified target column.
The inner exception reads as:
InvalidOperationException: The given value of type String from the data source cannot be converted to type datetime of the specified target column.
I've tried amending my code to be something like:
foreach (DataRow dr in nettingTable.Rows)
{
// this was done both as I've wrote and passing
// in the intended value as a variable, same result
dr["resetdate"] = DateTime.Parse(dr["nextresetdate"]);
}
But I get the same error. What is it that I'm doing wrong here?
Accepted Answer
The overload of DataColumnCollection::Add() that takes only a column name assigns string as the data type for the new column, as described at that link. Have you tried the following instead?
nettingTable.Columns.Add("resetdate", typeof(DateTime));