SqlDataAdapter and transaction

[Guest]

Hello,
I'm using SqlDataAdapter to insert data from one table into another with
SqlCommand after I start a transaction.
If I have convertion error for the insertion of the data, The transaction is
no longer valid, I'ts rolled back.

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.ExecuteNonQuery();
cmd = new SqlCommand("insert into table1 (a3) values('txt')", conn, 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 want to commit the transaction even if there was an error.
But 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

 

[Guest]

I guess I'm a little confused here because I don't see where you're using a
DataAdapter. By the same token, you don't necessarily need to use a data
adapter to do what you're trying to do.

Try setting the transaction property of each of your command objects to the
transaction you want the commands to run in. In other words, after a
statement like this:

SqlCommand cmd = new SqlCommand("alter table table1 add a3 int", conn,
tran);

You need something like this:

cmd.Transaction = tran

I think what is happening is that you've instantiated a transaction and a
couple of commands, but you haven't done anything to associate these objects
with one another.

HTH

 

[Guest]

Hi,
I did set the transaction property, sorry that i did not copy it in my
sample code.
I'm not using SqlDataAdapter, only SqlConnection and SqlCommand.


Sharon