Using Datareader inside Transaction Problem 3

[romh]

The SQL command is currently busy Open, fetching."

That is the error that I keep getting whenever I use the datareader to retrieve a record inside a transaction. But only when another command (insert with cmd.ExecuteNonQuery()) follows.
I think that .NET 1.1 does not like this. I remember installing 2.0 preview for testing, and the error did not occur. But using 2.0 for this project is not possible. Do you guys have any recommendations. I am specifically trying to get the identity value of the insert with SCOPE_IDENTITY() and saving it to a variable. I am trying not to use stored procs.

Thanks

 

[jzelhart]

If I remember correctly... (still sleepy but hope this helps.)

Are you trying to open more than one reader? If so you will need a separate connection for each.

Hope everyone is having a great day!

Thanks - Jennifer

 

[romh]

No, here is exactly what I'm trying to do:

SqlConnection con = new SqlConnection(ConnStr);
con.Open();
// begin transaction
SqlTransaction txn = con.BeginTransaction();

// open datareader
SqlCommand cmd = new SqlCommand(stringer1, con, txn);
SqlDataReader reader;
reader = cmd.ExecuteReader;
while(reader.Read())
{
// retrieve field values
}

// now insert a record
stringer = "Insert Into tblA "..... // Pseudo Code
cmd = new SqlCommand(stringer, con, txn);
recordsaffected2 = cmd.ExecuteNonQuery();

After this point, I get the

"The SQL command is currently busy Open, fetching

 

[link9]

Try closing the reader after your while loop.

-paul

The answer to getting answered -- faq855-2992

 

[romh]

Thanks, but if I close the reader before committing the transaction, it says that I cannot close the connection when a transaction is open!

Any other suggestions?

 

[Veep]

You only need to instantiate the transaction object once (I think). It appears that you're doing it here too:
// now insert a record
stringer = "Insert Into tblA "..... // Pseudo Code
cmd = new SqlCommand(stringer, con,[red]txn[/red]);
recordsaffected2 = cmd.ExecuteNonQuery();
That might be a problem.

 

[Veep]

Sorry, I worded that wrong. I don't think you need to refer to it again once the transaction is started.

 

[romh]

Thanks. I understood what you meant. But if I don;t include the txn in:

cmd = new SqlCommand(stringer, con,txn);

it tells me:
"ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

I guess that means that every command inside of the begintransaction should include the transaction.

Thanks

 

[Veep]

Use your original command object for the whole thing.

reader = cmd.ExecuteReader;
 while(reader.Read())
 {
    // retrieve field values
 }
  // now insert a record 
  stringer = "Insert Into tblA ".....    // Pseudo Code
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = stringer1
  int recordsaffected2 = cmd.ExecuteNonQuery();

 

[romh]

okay thanks alot. That might work. The thing is that here at the office I have Version 2.0 of .NET framework. After trying your suggestion, I did not receive an error message. At home, I have 1.1 .NET. Hopefully it will work.

Thanks

 

[Veep]

I did a little test on my machine sort of mimicking what you're doing. I made this a little verbose but it works fine. You do need to close the reader though. :

string connStr = SqlHelper.getConnString("PACS","PACS");
string strSql="SELECT * FROM tbl_p_Machine";
SqlConnection conn = new SqlConnection(connStr);
SqlTransaction trx;
conn.Open();
trx = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Transaction = trx;
cmd.Connection = conn;
SqlDataReader reader;
try
	{
		cmd.CommandType = CommandType.Text;
		cmd.CommandText = strSql;
		reader = cmd.ExecuteReader();
		while(reader.Read())
		{
			Response.Write("Reader is reading...<br/>");
		}
					reader.Close();
string strSql2="Insert tbl_p_Carrier(Carrier,UsedCurrently) VALUES ('Test Carrier',1)" +
	";Select NewID = @@Identity";
		cmd.CommandText=strSql2;
		int newID = Convert.ToInt32(cmd.ExecuteScalar());
		Response.Write("<br/>The new CarrierID is: " + newID.ToString());
		trx.Commit();
	}
catch (Exception ex)
	{
		Response.Write("It blew up! " + ex.Message.ToString());
	}

 

[romh]

Thanks alot for your time and help. I will try that in the next 2 hours and will let you know after that. I appreciate all of your help.

 

[romh]

That worked like a charm VEEP. The problem is that I needed to close the datareader.

 

[cappmgr]

I think link9 said something like that.
Marty

 

[romh]

sorry. You're right. Nothing against you Link9. I had never seen the reader.Close() command before. Out of all 3 books that I have, none of them mention closing it. They just say to close the connection, and that's what I though he meant.