Using DataTable values to insert into another DataTable

I think the main problem here is that your loop indexes and conditions are wrong, which leads to the invalid cast when you retrieve the data. You have this code:

for (i = 2; i <= (dt.Columns.Count - 2); i++)
{
    for (int j = 0; j < (dt.Columns.Count); j++)
    {
        //decimal? month1 = dt.Rows[i].Field<decimal?>(j);
        //decimal? month2 = dt.Rows[i].Field<decimal?>(j + 2);

        decimal? month1 = (decimal?)dt.Rows[i][j];
        decimal? month2 = (decimal?)dt.Rows[i][j + 2];

        fDt.Rows[i][j - 1] = ((month1 - month2) / month1) * 100;
    }            
} 

The inner loop, which indexes the columns of the table, starts with j = 0. But column 0 contains the customer ID, and column 1 contains the customer name. Neither of these are decimals, so when you retrieve dt.Rows[i][j] and cast it to decimal? it causes an InvalidCastException. This loop should start at index 2 (to skip over the ID and name columns) and the loop condition should be j < dt.Columns.Count - 1 because there is one less column in the destination table than the source table.

Next problem: It appears you intend the outer loop variable i to index into the rows collection, but you are starting the loop with index 2 instead of 0 and the loop condition is going against the number of columns, not rows. So you will end up skipping over the first two rows and will probably miss a lot of rows at the end depending on how many rows you have compared to columns. This looks like it might have been a copy-paste error or something.

There's more:

• If you are retrieving month1 from column j, then month2 should be the next month, or j + 1. You have it as j + 2. This will give you the wrong results and also end up throwing an IndexOutOfRangeException on the last month.

• I think your subtraction may be backward. If the later month (month2) represents an increase over the earlier month (month1), then you want to show that as a positive number, right? So you would need (month2 - month1) / month1 * 100. You have it as (month1 - month2)....

• You are not properly handling possible null values in the table. In a DataTable, a null is represented by the value DBNull, which cannot be cast to a decimal?. So that could be another source of an InvalidCastException. The Field<T> extension method handles the conversion between DBNull and null for you, so I would recommend using that. (It looks like you were using this method at one time but have since commented it out.) But note, however, that if you do a calculation on two nullable decimals where one or the other has a null value, the result is also null. And when you try to set the null value in the fDt row after the calculation, you will have the same problem in reverse, because the DataTable requires DBNull instead of null. So you should use the SetField<T> method to handle storing the value back into the table.

• You are also not considering the possibility that month1 may be zero, which could lead to a DivideByZeroException in your calculation.

Now that you have posted the rest of your code I noticed a few other issues:

• You're not copying the column data types from the first table to the second.

• In the loop where you are adding the rows to the destination table and copying the customer IDs and names into it, you are starting with row index 1 instead of 0. This will skip the first row. You will also miss the last row, because your loop condition is i < dt.Rows.Count - 1 instead of i < dt.Rows.Count. (You can actually combine this code with the outer calculation loop for simplicity.)

• Your method is returning the wrong table-- it should return fDt, not dt.

After fixing all these problems (and some simplification creating the ID and name columns), the code should look something like this, which should be pretty close to what you want:

public DataTable GetPerDiff(DataTable dt)
{
    var fDt = new DataTable();

    // Copy columns for customer name and ID
    fDt.Columns.Add(dt.Columns[0].ColumnName, dt.Columns[0].DataType);
    fDt.Columns.Add(dt.Columns[1].ColumnName, dt.Columns[1].DataType);

    // Create the PerDiff columns
    for (int j = 2; j < dt.Columns.Count - 1; j++)
    {
        string colName = "PerDiff" + dt.Columns[j + 1];
        fDt.Columns.Add(colName, dt.Columns[j + 1].DataType);
    }

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        fDt.Rows.Add(dt.Rows[i][0], dt.Rows[i][1]);

        for (int j = 2; j < dt.Columns.Count - 1; j++)
        {
            decimal? month1 = dt.Rows[i].Field<decimal?>(j);
            decimal? month2 = dt.Rows[i].Field<decimal?>(j + 1);

            if (month1 != decimal.Zero)
            {
                fDt.Rows[i].SetField(j, (month2 - month1) / month1 * 100);
            }
        }
    }
    return fDt;
}