We're currently looking into a generic
connection/execute/commit fashion for a base object which
will perform some magic to connect to a db and call a
derived method to execute sql. Sometimes, however, the
query sent using the transaction has a {CALL} or SELECT
somewhere which seems to break the connection for a
following update; it throws an exception complaining about
the connection needing to be "open and available; current
state is Open, Fetching". Is there anything that can be
done to avoid this if you want/need a query/non-query on
the same connection/transaction?

Internally, the transaction/connection is passed about the
object, so a chain of commands is performed over it. Is it
possible to mix and match queries over a transaction or
does it have to be update/insert/delete only, no selects?
Also, is it safe to assume that the InvalidOperation
exception on Commit/Rollback is ok to "ignore"? ie, does
that simply mean the transaction is worthless?

This is all currently done using v1.1/Odbc through IDb
interfaces to Sql Server (kept generic because there will
ultimately be different database backends)

Hi, thanks for the prompt reply!

I forgot to mention in my original post it's using
IDataReader through command.ExecuteReader(), not filling a
DataSet - sorry about that.

Anyway, based on your example, it seems my code is quite
similar; the connection should have been opened elsewhere
before use with the query. I'll double check this though.
Incidentally, this doesn't seem to be isolated to Odbc,
using the SqlClient classes results in an equivalent exception.

Any other help would be appreciated, but I'll continue my
investigation and also post back anything of interest.

Thanks.

Hi Kev

Based on my understanding, the connection was closed after you use the a
DataAdapter with a SELECT command to fill a DataSet. The DataAdapter will
keep the original state of the connection after filling the DataSet. It
mean that the state of connection will be closed if the the connection
wasn't opened before filling. So try to check if you have opened the
connection. I've written a short example here. HTH.

DataSet ds = new DataSet();
this.sqlConnection1.Open();
SqlTransaction t = this.sqlConnection1.BeginTransaction();
this.sqlDataAdapter1.SelectCommand.Transaction = t;
try
{
this.sqlDataAdapter1.Fill(ds);
t.Commit();
}
catch
{
t.Rollback();
}
finally
{
this.sqlConnection1.Close();
}


If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi Kev,

The Command.ExecuteReader() method requires an open connection to return a
DataReader. If the connection is closed, an exception will be thrown. I
think you can try to step though the code to find out which line crashes
the application. And you can use the watch window to monitor the state of
the current connection, so that we can find out if the connection is still
opening after calling Command.ExecuteReader().

However, if the problem still persists, please help me collect more
information for further troubleshooting.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi again,

I've come to the conclusion a Commit/Rollback on an
IDbTransaction (ODBC) will complain if there is simply a
query (SELECT/CALL) rather than at least one nonquery
(INSERT/UPDATE). If a query is the only issued item on the
transaction, it will throw an InvalidOperationException; if
there is a nonquery mixed in, it works. I've not tried
this with a stored procedure that updates and selects back
yet though. For the time being, I'm catching the
InvalidOperation and ignoring it on the assumption there
was no "real" transaction.

Oh, and remember to explicitly close the DataReader if you
want to use the transaction/connection for another
(non)query :) Hope this all makes sense.

Cheers.

Hi Kev,

I've done some further reasearch and found that we have to close the
DataReader before committing a transaction. Here I write a short example
using DataReader. Hope it's helpful for you.

SqlDataReader sdr;
this.sqlConnection1.Open();
SqlTransaction t = this.sqlConnection1.BeginTransaction();
SqlCommand com = new SqlCommand("SELECT * FROM Table1",
this.sqlConnection1,t);
try
{
sdr = com.ExecuteReader();

//Do something to manipulating on the
DataReader.

sdr.Close(); //Remember to close the DataReader before committing
t.Commit();
}
catch
{
t.Rollback();
}
finally
{
this.sqlConnection1.Close();
}

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."