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();
DbCommand cmd;
try
{
cmd = db.GetSqlStringCommand("SET CHAINED OFF");
db.ExecuteNonQuery(cmd);
foreach (DataRowView pRowView in pDataView)
{
}
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!!!