Add object to collection failed for ColumnCollection of table [name]

Question

I have the following code:

IEnumerable<Table> tables = dbtables.ToList()
                                    .Where(x => x.Name.StartsWith("PartStats"));

 foreach (var tbl in tables)
 {
    Table JoinTable = new Table(db, "ProductMaterial");
    JoinTable.Columns.Add(tbl.Columns[0]);

    string tblName = tbl.Columns[0].Name;
    string script =
                @"ALTER TABLE [dbo].[ProductMaterial]  
                   WITH CHECK ADD  CONSTRAINT [FK_" + tbl.Name +
                "] PRIMARY KEY([" + s;
    string script2 = @"]) REFERENCES [dbo].[" + tbl.Name + "] ([" + tblName + "])" ;
    string newscr = string.Concat(script, script2);
    AddPrimaryKey(tbl, newscr);

This used to work, but now when I run it, I get this error:

"Add object to collection failed for ColumnCollection of table ProductMaterial".

AddPrimaryKey() just executes the script on the SQL Server, and is fine. The problem is in the preceding code to that method call.

The server is on, I am using local admin account for the SQL Server (my windows account is an admin for the SQL Server).

Thanks

Answer 1

Perhaps wrap the call in a check whether the column already exists on the table.

if (!JoinTable.Columns.Contains(tbl.Columns[0].Name)
{
    JoinTable.Columns.Add(tbl.Columns[0]);
}

Could it be that you're trying to add multiple Tables named "ProductMaterial"?