Dave Kekish - 2004-11-29

Hi,

I'm trying to do a transaction based update on a table.

I need it to
a) Get all the rows in a table for a given set of criteria
b) delete the oldest row
c) update the raiming rows and
d) insert a new row.

_dm.QueryCriteria.Clear();
_dm.QueryCriteria.And(JoinPath.System_User.Columns.ID,ID,MatchType.Exact);
CCF.Cardio.Data.System_User _User = _dm.GetSystem_User(FetchPath.System_User.Password_History.All);
CCF.Cardio.Data.Password_HistoryCollection _PasswordList = _User.Password_Historys.SortBySequence(SortDirection.Ascending);   

                                        if (_PasswordList.Count > 2)
{
    CCF.Cardio.Data.Password_History _PasswordHistory = _PasswordList[2];
                        _PasswordHistory.Delete();
}
                   
//increase the sequence number by 1.
foreach (CCF.Cardio.Data.Password_History     
    _PasswordHistory in _PasswordList)
{                    _PasswordHistory.Sequence++;
}
   
string sql = _dm.CommitAllDebug();
                   
the problem is that when I take a look at the sql generated.   Its never going to work.   The delete comes last in the generated statement, so I end up with a key violation.

I've tried to wrap it in a transaction and using the Transaction.Save method but it doesn't appear to work.   Does someone have a example that uses the transaction or failing that see something I'm doing wrong?

Thanks
Dave
                    CCF.Cardio.Data.Password_History _pass = _dm.NewPassword_History(1,password,_User);
                    _pass.Modified_DT = DateTime.Now;
                    _pass.System_User = _User;
                    _pass.Modified_ID = (SqlInt32)_User.ID;
                   
                    _PasswordList.Add(_pass);
                   
                    _dm.CommitAll();