SqlConnection and Transaction

[Guest]

Hello,
I'm using SqlConnection to insert data from one table into another using
SqlCommand after I start a transaction.
If I have convertion error for the insertion of the data, The transaction is
no longer valid.
I want to be able to commit the transaction even if there where an error,
because I'm doing other stuff befor the data insertion.

This is my sample code:

try
{
SqlConnection conn = new SqlConnection("Application
Name=UpgradeDLL;workstation id=Sharon"
+ ";packet size=4096;user id="
+ "sa;Password=;data source=\"192.168.110.30\""
+ ";persist security info=False;initial catalog=aa");
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand("alter table table1 add a3 int", conn,
tran);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
cmd = new SqlCommand("insert into table1 (a3) values('txt')", conn,
tran);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
catch(Exception exp)
{
System.Diagnostics.Debug.WriteLine(exp.Message);
}
finally
{
tran.Commit();
conn.Close();
}
}
catch(Exception exp1)
{
System.Diagnostics.Debug.WriteLine(exp1.Message);
}



I get this exception at tran.Commit()
exp1:
"The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION"

I want to be able to commit the transaction after the error.


Thanks,
Sharon

 

[Miha Markic [MVP C#]]

Hi,

Hello,
I'm using SqlConnection to insert data from one table into another using
SqlCommand after I start a transaction.
If I have convertion error for the insertion of the data, The transaction
is
no longer valid.
I want to be able to commit the transaction even if there where an error,
because I'm doing other stuff befor the data insertion.

So, don't use a transaction then or split the transaction into two separate
transactions.

 

[Sahil Malik]

The obvious problem here is ---"insert into table1 (a3) values('txt')". a3
is type INT.

And by the nature of transactions, it is ALL OR NONE, so just like Miha
said, if you want individual control on the commands, then you need
individual transactions.


- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik