Column does not belong to table

[majidkorai]

Hey

Guyz I am having problem when i read the data from a datareader into a data table.

The whole scenario is this that I want to read data from two diffrent databases, the table strcutre is same. You can say that other one is the copy of 1st one. I want to read out the data from one table in one database and merge the results with the one read form the other database.

What i am doing is reading data from two databases into data readers. now i just have to merge them. what i did took one datatable and want to merge the data in two datareader object into that data table. frst one went ok. but when i was inserting the rows from the 2nd one it gave this error.
see the code bellow and suggest plz:

public static DataTable PopupateDataTableFromDataReader(DataTable dt, clsDataReader dr)
        {
 
            DataTable dtSchema = dr.dr.GetSchemaTable();
 
            // You can also use an ArrayList instead of List<>
            ArrayList listCols = new ArrayList();
 
            if (dtSchema != null)
            {
                foreach (DataRow drow in dtSchema.Rows)
                {
                    string columnName = System.Convert.ToString(drow["ColumnName"]);
                    DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
                    column.Unique = (bool)drow["IsUnique"];
                    column.AllowDBNull = (bool)drow["AllowDBNull"];
                    column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                    listCols.Add(column);
                    if(dt.Columns.IndexOf(column.ColumnName) == -1)
                    {
                        dt.Columns.Add(column);
                    }
                }
            }
 
            // Read rows from DataReader and populate the DataTable
            while (dr.dr.Read())
            {
                DataRow dataRow = dt.NewRow();
                for (int i = 0; i < listCols.Count; i++)
                {
                    dataRow[((DataColumn)listCols[i])] = dr.dr[i];
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }    
 
        public static clsDataReader  executeReturn2(string sqlCommand)
        {
            try
            {
 
                SqlConnection   co =initConnection(2);
                clsDataReader dataReader=new clsDataReader();
                SqlCommand   myCommand = new SqlCommand(sqlCommand, co);
                SqlDataReader dr= myCommand.ExecuteReader();
                dataReader.co =co ;
                dataReader.dr =dr;
 
                return dataReader;
            }
            catch(Exception e)
            {
                throw new Exception("An error occured while trying to run the following: " + sqlCommand,e); 
            }
        }
 

[PRR]

If you wish to merge two similar tables then DataSet.Merge() is far easier to use. You have to make sure that the primary key column exists though.

Merging Datasets

To give a brief idea consider a table Emp1: with columns empid, empname and second table Emp2: with columns empid, empjoindate.
You could have data of Emp1 in dataset1 and Emp2 in dataset2.

select empid,empname from Emp1;
select empid,empjiondate from Emp2;
 

You could easily merge the dataset by calling

dataset1.Merge(dataset2.Tables[0]);