Mythul Mythul - 1 year ago 176
SQL Question

How to use DataAdapter with SqlTransaction in C#?

I need to create 2 buttons, one for starting a transaction (

) and one for committing a transaction (

I got stuck trying to implement it for the

When I press the
I get the error that the transaction has already completed and is no longer usable.

public void startTransaction(IsolationLevel isolationLevel) {
transaction = connection.BeginTransaction(isolationLevel);
Console.WriteLine("Transaction started !");

public void commitTransaction() {
Console.WriteLine("Transaction commited !");

public DataTable readAllRezervari() {
try {
String selectSql = "SELECT * FROM Rezervari;";
SqlCommand sqlCommand = new SqlCommand(selectSql, connection, transaction);
rezervariDataAdapter.SelectCommand = sqlCommand;
rezervariDataAdapter.Fill(rezervariDataSet, "Rezervari");
catch (Exception e) {
Console.WriteLine("ERROR: " + e);
try {
catch (Exception e2) {
Console.WriteLine("ERROR: " + e2);
finally {
rezervariDataTable = rezervariDataSet.Tables["Rezervari"];
return rezervariDataTable;

Answer Source

It appears you are trying to use Transaction Commit and Rollback as a way for the user to commit or rollback their changes. I always believed it was incase there was an issue with the code or the data you could prevent multiple changes from being partially commited resulting in bad data. I really know more than c# so forgive me if my format is off.

Here is a thought:commit all their data to a datatable and store it in a session variable or view state

something like

 rezervariDataTable  = session("reservari");

if the user hits commit button: It does the code to write the transaction to the database.

If the user hits rollback:

     reservariDataTable = Nothing;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download