This Row Already Belongs To This Table

Question

I get an error 'this row already belongs to this table' from the following code:

public static DataTable AddNewAllocations(string pCaseNo, ref DataTable pTable)
    {
        try
        {
            string sqlText = "SELECT UserID FROM tblUsers;";
            aSqlQuery aQ = new aSqlQuery(sqlText, "table");
            DataTable userTable = aQ.TableResult;

            foreach (DataRow userRow in userTable.Rows)
            {
                int allocAlready = 0;
                foreach (DataRow allocRow in pTable.Rows)
                {
                    if (allocRow["FeeEarner"].ToString() == userRow["UserID"].ToString())
                    {
                        allocAlready = 1;                            
                    }
                }
                if (allocAlready == 0)
                {
                    string strUser = userRow["UserID"].ToString();          
                    decimal fees = cTimesheet.UserFees(strUser, pCaseNo);
                    int intCaseNo = Int32.Parse(pCaseNo);
                    if (fees > 0)
                    {
                        Object[] array = new object[8];
                        array[0] = 0;
                        array[1] = intCaseNo;
                        array[2] = DateTime.Today;
                        array[3] = strUser;
                        array[4] = fees;
                        array[5] = 0;
                        array[6] = fees;
                        array[7] = true;
                        pTable.Rows.Add(array);
                    }
                }
            }
            return pTable;
        }

        catch (Exception eX)
        {
            throw new Exception("cAllocation: Error in NewAllocations()" + Environment.NewLine + eX.Message);
        }

When I step through the code I can see that the error is thrown on the second occasion the following line is accessed:

pTable.Rows.Add(array);

Given that I create a new object array each time the code enters the loop, I fail to see why I am receiving this error message, which suggests to me the same row is being added multiple times. Why does the code see each loop as adding the same datarow when the row is generated by a new object array each time?

Answer 1

Another approach is to create a NewRow() at the beginning of the loop, assign its data, then Rows.Add() at the bottom of the loop.

{       
        // initialization code 
        // ...

        foreach (DataRow row in dt.Rows)
        {
            row.Delete();
        }
        Oda.Update(ds, "USERTABLE");

        DataRow  dr;

        foreach (var userRecord in urList)
        {
            dr = dt.NewRow();
            dr["username"] = userRecord.userName;
            dr["firstname"] = userRecord.firstName;
            dr["lastname"] = userRecord.lastName;
            dr["createdon"] = userRecord.createdOn;

            dt.Rows.Add(dr);
        }
        Oda.Update(ds, "USERTABLE");
}

Answer 2

The code that finally worked was this:

public static DataTable AddNewAllocations(string pCaseNo, DataTable pTable)
    {
        try
        {
            DataTable newTable = NewAllocationTable(); 

            string sqlText = "SELECT UserID FROM tblUsers;";
            aSqlQuery aQ = new aSqlQuery(sqlText, "table");
            DataTable userTable = aQ.TableResult;

            foreach (DataRow userRow in userTable.Rows)
            {
                int allocAlready = 0;
                foreach (DataRow allocRow in pTable.Rows)
                {
                    if (allocRow["FeeEarner"].ToString() == userRow["UserID"].ToString())
                    {
                        allocAlready = 1;                            
                    }
                }

                if (allocAlready == 0)
                {
                    string strUser = userRow["UserID"].ToString();          
                    decimal fees = cTimesheet.UserFees(strUser, pCaseNo);
                    int intCaseNo = Int32.Parse(pCaseNo);
                    if (fees > 0)
                    {
                        Object[] array = new object[8];
                        array[0] = 0;
                        array[1] = intCaseNo;
                        array[2] = DateTime.Today;
                        array[3] = strUser;
                        array[4] = fees;
                        array[5] = 0;
                        array[6] = fees;
                        array[7] = true;
                        newTable.Rows.Add(array);
                    }
                }
            }

            foreach (DataRow row in pTable.Rows)
            {
                newTable.ImportRow(row);
            }

            newTable.DefaultView.Sort = "AllocID";
            return newTable;
        }

        catch (Exception eX)
        {
            throw new Exception("cAllocation: Error in NewAllocations()" + Environment.NewLine + eX.Message);
        }
    }

I think the key was using ImportRow rather than Rows.Add. I still use Rows.Add in my method but only when adding rows to a newly created table. I then loop through the existing table which was passed in as a paramater, and use ImportRow to add each row of the paramater table, to the newly created one. I then pass the new combined table out in my return statement, rather than a modified parameter table.