SQL Bulkcopy coverting data type

c# datetime parsing sqlbulkcopy sql-server

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));


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why