I need some advice on how to best deal with this scenario (using VS2005 and LLBL v2.6 and DevExpress ASPXGrid)

I have a table with an autoincrement PK and two non-null character fields, Name and Description. The web page is a no-frills grid with an edit form for inserting and updating. When the user leaves Name or Description blank and attempts to insert, the db throws the "can't insert null into a non-null column" exception and is shown to the user. So the user corrects the situation and supplies a value for the field that was null, and tries to save again.

Looking at the uow.GetEntityElementsToInsert() in the debugger, it now contains two elements. The first with the null value that failed to insert, and the second with the corrected non-null value. So uow.Commit() fails again, when the first element is attempted to be inserted.

What I've done to work around it, is catch the exception that happens on uow.Commit() and issue uow.Reset() and then rethrow the exception so the exception is seen by the user. It seems like there ought to be a better way, and any advice would be appreciated.

Thanks, Richard

Hi Richard,

Could you please show us your relevant declarative ASPX code to see how the grid/llblgenprodatasurce is configured?

What LLBLGen version and Runtime Library version are you using (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7720)?

Thanks for the speedy reply David.

LLBL v2.6 final October 6th, 2008 runtime lib v2.6.09.0305

        <dxwgv:ASPxGridView ID="ASPxGridView1" runat="server" AutoGenerateColumns="False"
            DataSourceID="dsTest" KeyFieldName="Id">
            <Columns>
                <dxwgv:GridViewCommandColumn VisibleIndex="0">
                    <EditButton Visible="True">
                    </EditButton>
                    <NewButton Visible="True">
                    </NewButton>
                    <DeleteButton Visible="True">
                    </DeleteButton>
                    <ClearFilterButton Visible="True">
                    </ClearFilterButton>
                </dxwgv:GridViewCommandColumn>
                <dxwgv:GridViewDataTextColumn FieldName="Id" VisibleIndex="1">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="Name" VisibleIndex="2" Width="200px">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="Description" VisibleIndex="3" Width="200px">
                </dxwgv:GridViewDataTextColumn>
            </Columns>
        </dxwgv:ASPxGridView>
        <llblgenpro:llblgenprodatasource2 id="dsTest" runat="server" AdapterTypeName="LLBLGenWebTest.DatabaseSpecific.DataAccessAdapter, LLBLGenWebTestDBSpecific" 
        DataContainerType="EntityCollection" EntityFactoryTypeName="LLBLGenWebTest.FactoryClasses.TestTableEntityFactory, LLBLGenWebTest" 
        CacheLocation="Session" LivePersistence="False" OnPerformSelect="dsTest_PerformSelect" OnPerformWork="dsTest_PerformWork">
        </llblgenpro:llblgenprodatasource2>

        protected void dsTest_PerformSelect(object sender, PerformSelectEventArgs2 e) {
            TestTableManager testTableManager = DataServicesUtils.NewTestTableManager;
            EntityCollection<TestTableEntity> testTableList = testTableManager.GetTestTables();
            foreach (TestTableEntity testTable in testTableList) {
                e.ContainedCollection.Add(testTable);
            }
        }

        protected void dsTest_PerformWork(object sender, PerformWorkEventArgs2 e) {
            TestTableManager testTableManager = DataServicesUtils.NewTestTableManager;
            testTableManager.Save(e.Uow);  // this gets an adapter and calls uow.Commit(adapter,true)
        }

Thanks again.

Ohh, I see this is what is happen:

• You enter values on the new row of the grid.
• You leave the row by clicking somewhere else.
• The grid fires the insert to the LLBLGenProDataSource do it as well
• The NULL value is invalid so it fires an exception.
• For some reason the dirty entity is still in the UOW.
• Enter the valid value, leave the row.
• Now there are two objects to save.

Could you please show the commit code snippet? Could you please try the commit with autocommit = true?

Here's the commit code - it is using the autocommit = true. The uow.Reset() was added to work around this issue.

Looked around the forums some more, and this seems similar http://llblgen.com/tinyforum/Messages.aspx?ThreadID=15028&HighLight=1

public class TestTableManager {

        protected DataAccessAdapter GetAdapter() {
            return new DataAccessAdapter(this.connectionString);
        }

        public void Save(UnitOfWork2 uow) {
            using (DataAccessAdapter adapter = this.GetAdapter()) {
                try {
                    uow.Commit(adapter, true);
                }
                catch (Exception ex) {
                    uow.Reset();    // added this to remove entity causing original exception
                    throw ex;
                }
            }
        }

        public EntityCollection<TestTableEntity> GetTestTables() {
            EntityCollection<TestTableEntity> testTables = new EntityCollection<TestTableEntity>();

            SortExpression sorter = new SortExpression(TestTableFields.Id | SortOperator.Ascending);

            using (DataAccessAdapter adapter = this.GetAdapter()) {
                adapter.FetchEntityCollection(testTables, null, 0, sorter, null);
            }

            return testTables;
        }
}

It makes sense to me what is happening - the first entity is still there after the exception occurs. Wasn't sure if the correct thing to do is clearing out everything with a uow.Reset(), or if there was some way to 'reuse' the original entity when the user tried to insert the second time after the correction was made.

Thanks for your help. Richard

If you use the UOW without databinding I think you wouldn't want the uow gets cleared after a rollback, because maybe you want to use those failed entities.

So at databinding scenario maybe would be the right thing to trap the error somewhere (an event in the grid for instance) and clear the UOW.