error filling a dataset...

[gloria]

I get a strange error when working with a dataset. I can fill the
dataset and assign it to a table. I get the count. However, when I
try to insert the rows from the dataset to another table I get the
following error: "Error Inserting: The connection is already Open
(state=Open)."

I moved my open and close and even commented them out. But I still get
errors: "Error Inserting: ExecuteReader requires an open and available
Connection. The connection's current state is Closed."

What am I doing wrong? I think I've tried all possible combinations of
open/close, but I'm obviously missing at least one.

Thanks!

--gloria


OleDbConnection loadnewpartsCN = new
OleDbConnection(newpartsClass.strConn);

OleDbDataAdapter newpartsqueryDA = new
OleDbDataAdapter(newpartsqueryStr,loadnewpartsCN);
DataSet newpartsqueryDS = new DataSet();
Console.WriteLine("Filling dataset...");
newpartsqueryDA.Fill(newpartsqueryDS);
Console.WriteLine("Dataset filled.");

string partnum, desc, serializedflag, status, hscode, coo;
int serializedint;

DataTable table888 = newpartsqueryDS.Tables[0];

int rowcnt = table888.Rows.Count;
Console.WriteLine("Row Cnt: " + rowcnt);

//loadnewpartsCN.Open();
foreach(DataRow row888 in table888.Rows)
{
partnum = row888["PartNumber"].ToString();
desc = row888["PartDescription"].ToString();
serializedflag = row888["Serialized"].ToString();
status = row888["Status"].ToString();
hscode = row888["HS_Code"].ToString();
coo = row888["COO"].ToString();

if (serializedflag == "False")
serializedint = 0;
else
serializedint = 1;

string insertnewpartsStr = "insert into table_BAXPartsMaster " +
"values(" + newpartsClass.reg_subinv + ",'" + partnum + "','" + desc +
"',"
+ serializedint + ",'" + status + "','" + hscode + "','" +
coo + "'," + "1," + " NULL," + " NULL," + " 'I'," + " 0," +
" getdate()," + " getdate()," + " NULL);" +
"insert into table_BAXPartsMaster " +
"values(" + newpartsClass.fis_subinv + ",'" + partnum + "','" + desc +
"',"
+ serializedint + ",'" + status + "','" + hscode + "','" +
coo + "'," + "1," + " NULL," + " NULL," + " 'I'," + " 0," +
" getdate()," + " getdate()," + " NULL)";

try
{
OleDbCommand insertnewpartsCmd = new
OleDbCommand(insertnewpartsStr,loadnewpartsCN);
OleDbDataReader insertnewpartsRdr = insertnewpartsCmd.ExecuteReader();
}
catch (Exception ex2)
{
Console.WriteLine("Error Inserting: " + ex2.Message);
}

}
//loadnewpartsCN.Close();

 

[Guest]

Hi gloria,
Before reading your code, lemme say something that might help you understand
your problem and solve it urself..
First, regarding the connnection, since you are working with datasets, you
are using a disconnected model, what that means you u in this situation is
this : After you fill the dataset, close the connection and forget about it,
cause ur data now is in the dataset already.
Secondly, after getting the data in the dataset, what you have to do is to
modify the data in the dataset and not touch the db again (unless u want more
data that is not in the dataset that you just filled).
So the bottom line is this -> you are using a disconnected data model...
Would you like to take a look at ur code again and try to refine ur question
and repost?
Hth...
R. Thomas

I get a strange error when working with a dataset. I can fill the
dataset and assign it to a table. I get the count. However, when I
try to insert the rows from the dataset to another table I get the
following error: "Error Inserting: The connection is already Open
(state=Open)."

I moved my open and close and even commented them out. But I still get
errors: "Error Inserting: ExecuteReader requires an open and available
Connection. The connection's current state is Closed."

What am I doing wrong? I think I've tried all possible combinations of
open/close, but I'm obviously missing at least one.

Thanks!

--gloria


OleDbConnection loadnewpartsCN = new
OleDbConnection(newpartsClass.strConn);

OleDbDataAdapter newpartsqueryDA = new
OleDbDataAdapter(newpartsqueryStr,loadnewpartsCN);
DataSet newpartsqueryDS = new DataSet();
Console.WriteLine("Filling dataset...");
newpartsqueryDA.Fill(newpartsqueryDS);
Console.WriteLine("Dataset filled.");

string partnum, desc, serializedflag, status, hscode, coo;
int serializedint;

DataTable table888 = newpartsqueryDS.Tables[0];

int rowcnt = table888.Rows.Count;
Console.WriteLine("Row Cnt: " + rowcnt);

//loadnewpartsCN.Open();
foreach(DataRow row888 in table888.Rows)
{
partnum = row888["PartNumber"].ToString();
desc = row888["PartDescription"].ToString();
serializedflag = row888["Serialized"].ToString();
status = row888["Status"].ToString();
hscode = row888["HS_Code"].ToString();
coo = row888["COO"].ToString();

if (serializedflag == "False")
serializedint = 0;
else
serializedint = 1;

string insertnewpartsStr = "insert into table_BAXPartsMaster " +
"values(" + newpartsClass.reg_subinv + ",'" + partnum + "','" + desc +
"',"
+ serializedint + ",'" + status + "','" + hscode + "','" +
coo + "'," + "1," + " NULL," + " NULL," + " 'I'," + " 0," +
" getdate()," + " getdate()," + " NULL);" +
"insert into table_BAXPartsMaster " +
"values(" + newpartsClass.fis_subinv + ",'" + partnum + "','" + desc +
"',"
+ serializedint + ",'" + status + "','" + hscode + "','" +
coo + "'," + "1," + " NULL," + " NULL," + " 'I'," + " 0," +
" getdate()," + " getdate()," + " NULL)";

try
{
OleDbCommand insertnewpartsCmd = new
OleDbCommand(insertnewpartsStr,loadnewpartsCN);
OleDbDataReader insertnewpartsRdr = insertnewpartsCmd.ExecuteReader();
}
catch (Exception ex2)
{
Console.WriteLine("Error Inserting: " + ex2.Message);
}

}
//loadnewpartsCN.Close();

 

[gloria]

I fill the dataset, but then I would like to do some manipulation and
then insert it into another table in the same schema. I would think I
would open a connection, execute a reader, and then close it. This
seems pretty labor-intensive (but not a big deal because it will be
happening off hours, so time is not a huge deal). I also thought I
could open a connection and then execute a reader multiple time then
after the loop, close the connection. Do readers have special
properties with respect to connecting to the database?

Thanks.

--gloria