c# Transaction with multi-statements

See more:

C#

.NET

SQL-Server

Hi guys,

I tried to add a transaction when I insert row by row from a datatable into my database, here is my code:

public string addNewItems(DataView pDataView, string pTableName, int pTableId, string pUserName)
     {
         string returnString = string.Empty;
         Database db = AseDatabase;

         using (DbConnection connection = db.CreateConnection())
         {
             connection.Open();
             DbTransaction transaction = connection.BeginTransaction();
             //OdbcParameter parameter;
             DbCommand cmd;
             try
             {
                 cmd = db.GetSqlStringCommand("SET CHAINED OFF");
                 db.ExecuteNonQuery(cmd);

                 foreach (DataRowView pRowView in pDataView)
                 {
                    //I build the string sqlcmd

                    //cmd = db.GetSqlStringCommand(sqlcmd);

                    // I add parameters

                    // I execute the cmd in transaction:
                    // db.ExecuteNonQuery(cmd, transaction);


                 }

                 transaction.Commit();
             }
             catch (Exception ex)
             {
                 returnString = ex.Message;
                 transaction.Rollback();
                 throw;
             }

             return returnString;
         }
     }

The problem comes from the fact that when I try to insert, it shows an error :

ERROR [ZZZZZ][Sybase][ODBC Driver][Adaptive Server Enterprise]The résidente SQL procedure 'psi_spy' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.	

It is really strange, since when I tried to run my code with a simple created test table, it goes well. But the below error shows just when I run my code with my working tables...

I also checked the option insert of my working table, it seems it works well...

Could anyone help me please?



P.S:

As you can see, I "set chained off" below.

The real problem is, if I set chined off in the transaction like this:

cmd = db.GetSqlStringCommand("SET CHAINED OFF");
                    db.ExecuteNonQuery(cmd,transaction);

then the transaction just doesn't work..

if I set chined off outside the transaction like this:

cmd = db.GetSqlStringCommand("SET CHAINED OFF");
                    db.ExecuteNonQuery(cmd);

then the error just shows...



thank you in advance!!!