Can not Open any more table...

Posted by Pramodmca09 under C# on 9/24/2009 | Views : 5238 | Status : [Member] | Replies : 2
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;

}





////////////////////////////////////////////////////////////




Responses

Posted by: Neeks on: 9/25/2009 [Member] Bronze

Up
0
Down
Please close the connection before getting the records from other Select Command and again open it to execute second Select command.

I thinks It'll work

Please let me know if still error

Thanks

Pramodmca09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pramodmca09 on: 9/25/2009 [Member] Starter

Up
0
Down
Hi... Thanx ... for help ....I have created two connection for same database. I fetching data from 1st table using 1st connection and 2nd for 2nd table. I'm opening and closing 2nd connction in 1st While loop. its working...

I will try your option...Thnx alots...

Pramodmca09, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response