Stored Procedure is executed but no update done.

redgoblin83 posted on Monday, April 23, 2007

I do an update of an item and its update method is called, and also the execute non query.

But the database is not updated! Is it possible it has someting to do with the transaction state that csla uses? I thought that in a project that I did a year ago I changed a setting of csla so there was no pending transactions if this is what is hapening here.

I checked the Stored Procedure and the parameters so there is no problem at that poitn.

What can I do ?

Thank you all,

red,

 

William replied on Monday, April 23, 2007

If you are using TransactionTypes.TransactionScope (CSLA 2.x) for your DataPortal_Update method, then any exception thrown and caused the method to exit votes "Abort" to the entire transaction scope. Thus, TransactionScope will automatically rollback the entire transaction.

Regards,
William

redgoblin83 replied on Monday, April 23, 2007

Willam you mean in the object where the DataPortal_Update is called ? If so there is no TransactionTypes.TransactionScope defined.  Or do you mean in the csla source code itself ?

That's mine update for my object:

private void ExecuteUpdate(SqlConnection cn, Services_RW parent)

{

using (SqlCommand cm = cn.CreateCommand())

{

cm.CommandType = CommandType.StoredProcedure;

cm.CommandText = "DLG_UpdateService_RW";

AddUpdateParameters(cm, parent);

cm.ExecuteNonQuery();

MarkOld();

}//using

}

 

Thanks,

Red

William replied on Monday, April 23, 2007

When no TransactionTypes attribute is applied to DataPortal_Update, CSLA assumes manual transaction type. I assume you have an active database transaction when your ExecuteUpdate method executes. Thus, instead of passing in SqlConnection object, you should pass in SqlTransaction object. Then your code needs to explicitly make SqlCommand participates in the same transaction.

private void ExecuteUpdate(SqlTransaction tx, Services_RW parent)
{
    using (SqlCommand cm = tx.Connection.CreateCommand())
    {
        cm.Transaction = tx;
        cm.CommandType = CommandType.StoredProcedure;
        cm.CommandText = "DLG_UpdateService_RW";

        AddUpdateParameters(cm, parent);

        cm.ExecuteNonQuery();

        MarkOld();
    }//using
}


Regards,
William

Bayu replied on Monday, April 23, 2007

You said that you checked to stored proc and params. How did you verify it is correct? Visually? Have you actually tried calling the proc manually, from some admin interface?

The thing is that this discussion has veered into transactions right from the start, while perhaps your proc is just not doing an update at all.

Just making sure we aren't searching for a solution to a non-existent problem. ;-)

Bayu

redgoblin83 replied on Tuesday, April 24, 2007

Good point,

Changed the code and then I got some nice message, the current user didn't had the rights to write to the tables, d'ont know why my sql server 2005 is secured for the moment. My fautl perhaps while installing the sql server.

Now it seems it is working with the two code samples.

Thank you all,