Hi Marcus,

I don't know for sure, but I'm betting that the SqlDataReader closes the connection once it's done with it (I've not used DataReaders). Your best bet is to open the connection in your UpdateData() method and close it there when you're done with it, rather than opening the connection in the SetupConnection() method.

~~Bonnie

>Hi,
>
>Just for test purpose I wrote a few methods, see below.
>Now I run into a strange behavior:
>If I call con=SetupConnection() and then UpdateData(con) everything works like expected.
>If I run ShowData(con) before I run UpdateData(con) I get an error:
>"Begin transaction needs an open and available connection; connection status = open"
>
>Can anyone explain this to me? Does the command.ExecuteReader() do anything to the connection and how can I avoid this?
>
>Marcus
>
>
>

>public SqlConnection SetupConnection()
>		{
>			SqlConnection con = new SqlConnection();
>			con.ConnectionString = @".....";
>			con.Open();
>                        return con;
>                }
>
>public void ShowData(SqlConnection con)
>		{
>		SqlCommand command = new SqlCommand("SELECT * FROM Categories WHERE CategoryId = 8",con);
>
>		SqlDataReader dr = command.ExecuteReader();
>		dr.Read();
>		
>		MessageBox.Show(dr.GetValue(2).ToString());
>		
>		}
>
>public void UpdateData(SqlConnection con)
>		{
>		
>			SqlCommand command = new SqlCommand("UPDATE Categories SET Description = 'Seaweed and fish **New**' WHERE CategoryId = 8",con);
>
>			SqlTransaction transaction = con.BeginTransaction();
>			command.Transaction = transaction;
>			command.ExecuteNonQuery();
>			transaction.Commit();
>                 }
>
>