I'm trying to insert 6000 recoreds from 2 MS Access table to an excel sheet. Where near about 1000Records get inseted but after that I'm getting error like Cannot open any more tables, on myCommand.ExecuteNonQuery(); statement
////////////////////////////////////////////////////////////
public DataTable joinConfAndGoods()
{
MyConnection.Open();
con.Open();
OleDbCommand cmdSelectNGoods = new OleDbCommand("select * from NewGoods", con);
OleDbDataReader drNewGoods = cmdSelectNGoods.ExecuteReader();
bool rowsGoods = drNewGoods.HasRows;
if (rowsGoods)
{
while (drNewGoods.Read())
{
string sqlSelectNewConf = "select * from NewConf where (( Order='" + newOrder + "')and(Yield='" + quantity + "')) ";
OleDbCommand cmdSelectGoods = new OleDbCommand(sqlSelectNewConf, con);
OleDbDataReader drConf = cmdSelectGoods.ExecuteReader();
bool rowsConf = drConf.HasRows;
if (rowsConf)
{
while (drConf.Read())
{
if (dateG == dateC && newOrder == orderStringConf && yieldStringConf == quantityYield)
{
int scarp = Convert.ToInt32(stringScrap);
int mvntType = Convert.ToInt32(stringMvntType);
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
myCommand.Connection = MyConnection;
confText = confText.Trim();
if (confText == "")
{
//Do nothing
}
else
{
sql = "INSERT INTO [Sheet1$] (order1, postingDate2,material,materialDesc,mvntType,quantity,EntryUnit,StoreLocation,Batch,MatDoc,WorkCntr,Scrap,ConfText) values ('" + order + "', '" + dateG + "','" + material + "','" + materialDesc + "','" + mvntType + "','" + quantity + "','" + entryUnit + "','" + storLocation + "','" + batch + "','" + matDoc + "','" + workCenter + "','" + scarp + "','" + confText + "')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
}
if (addedRecords == Convert.ToInt32("732"))
addedRecords = addedRecords + 0;
addedRecords++;
joinedRecords++;
}
}
}
}
con.Close();
MyConnection.Close();
}
return table;
}
////////////////////////////////////////////////////////////