i have a very simple program for manipulating two tables in a SQL 2000 database, this project is written in ASP.Net 1.1 (becuase another section thats already written is in 1.1).

Background:

there are two tables 1) posting PK - JobId int 2) Listing PK - ListId int FK - JoibId int

I already have everything up and working except when it comes to updateing an entry. In the LLBLgen designer i didn't specify any thing that was not already in the schema for the DB (mostly because i have been using it for about 2 days :0)

Problem: the problem arises when i try to modify an entity that was just fetched. it turns out that if i save the entity using the adapter it doesn't seem to be using the fetched entity at all because it keeps telling me that i didn't set a required field on the entity. the field was not modified in the editing process and i did verify that it is there on the entity that i fetched.

the required field is a createdate on the entity

An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'CreateDate', table 'dbo.JobDB_Posting'; column does not allow nulls. INSERT fails. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

[ORMQueryExecutionException: An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'CreateDate', table 'DPSQ440.DPSQ440_RAIN.JobDB_Posting'; column does not allow nulls. INSERT fails. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.] SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() +668 SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute() +90 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) +134 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(ArrayList queueToPersist, Boolean insertActions) +1199 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) +1078 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave) +40 Job.Core.Managers.PostingManager.Save(JobDbPostingEntity post) +58 JobUI.Common.Controls.Content.JobEdit.SaveButton_Click(Object sender, EventArgs e) +631 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292

LLBLgen ver. 1.0.2005.1 Final code template: Adapter scenario (full/safe)(1.0.2005.1.03032006) database template: c# template for sqlserver (1.0.2005.1)(1.0.2005.1.111705)

Connection.cs

Adapter property:

        public DataAccessAdapter Adapter
        {
            get
            {
                string connectionString = String.Empty;
                string catalogName = String.Empty;
                string schemaName = String.Empty;

                catalogName = ConfigurationSettings.AppSettings["CatalogName"];
                schemaName = ConfigurationSettings.AppSettings["SchemaName"];

                if (IsLive == "0")
                    connectionString = ConfigurationSettings.AppSettings["LocalConnectionString"];
                else
                    connectionString = ConfigurationSettings.AppSettings["LiveConnectionString"];

                CatalogNameOverwriteHashtable catalogChange = new CatalogNameOverwriteHashtable();
                catalogChange.Add("DPSQ440", catalogName);

                SchemaNameOverwriteHashtable schemaChange = new SchemaNameOverwriteHashtable();
                schemaChange.Add("DPSQ440_RAIN", schemaName);

                DataAccessAdapter adapter = new DataAccessAdapter(connectionString, false, catalogChange, schemaChange);
                adapter.CommandTimeOut = 180;
                return adapter;
            }
        }       

Save code:

        public PostEntity Save(PostEntity post)
        {
            Connection connection = new Connection();
            using(DataAccessAdapter adapter = connection.Adapter)
            {
                adapter.SaveEntity(post);
            }
            return post;
        }
        
        public ListingEntity Save(ListingEntity listing)
        {
            Connection connection = new Connection();
            using(DataAccessAdapter adapter = connection.Adapter)
            {
                adapter.SaveEntity(listing);
            }
            return listing;
        }
        

I found a work around by using the adapter.UpdateEntitiesDirectly() method with the PK value, but i would still love to know why using the save method created a dupe + changes in the database...

ascholfield wrote:

but i would still love to know why using the save method created a dupe + changes in the database...

How are you fetching, binding and passing the entity to your Save methods? Please post some snippets of that. Please check this as well.

ok here are three of my fetch methods:

        public EntityCollection ListJobsByDate()
        {
            EntityCollection results = new EntityCollection(new JobDbPostingEntityFactory());
            Connection connection = new Connection();
            using(DataAccessAdapter adapter = connection.Adapter)
            {
                PredicateExpression predEx = new PredicateExpression(PredicateFactory.CompareValue(JobDbPostingFieldIndex.EndDate, ComparisonOperator.GreaterEqual, DateTime.Now));
                predEx.Add(PredicateFactory.CompareValue(JobDbPostingFieldIndex.Active, ComparisonOperator.Equal, true));
                RelationPredicateBucket bucket = new RelationPredicateBucket(predEx);
                adapter.FetchEntityCollection(results ,bucket);

            }
            results.SupportsSorting = true;
            results.Sort(((int)JobDbPostingFieldIndex.CreateDate), ListSortDirection.Descending);
            return results;
        }
        public EntityCollection ListJobsByUser(int Uid)
        {
            EntityCollection results = new EntityCollection(new JobDbPostingEntityFactory());
            Connection connection = new Connection();
            using(DataAccessAdapter adapter = connection.Adapter)
            {
                PredicateExpression predEx = new PredicateExpression(PredicateFactory.CompareValue(JobDbPostingFieldIndex.Creator, ComparisonOperator.Equal, Uid));
                RelationPredicateBucket bucket = new RelationPredicateBucket(predEx);
                adapter.FetchEntityCollection(results ,bucket);

            }
            results.SupportsSorting = true;
            results.Sort(((int)JobDbPostingFieldIndex.CreateDate), ListSortDirection.Descending);
            return results;
        }

the process is basically fetch by one method or another, display, fectch again, edit then save

in order to save it i just re fetch is using the PK, read in the changes from the web page, chage the object then pass it to the save method in the above post

ascholfield wrote:

in order to save it i just re fetch is using the PK, read in the changes from the web page, chage the object then pass it to the save method in the above post

Please post that snippet as well, where you refetch, modify and send the object to your save methods.

this is the save click method that i am using for the fetching, modifying that saving to the db

        private void SaveButton_Click(object sender, EventArgs e)
        {
            string title = Title.Text;
            string jLocation = JLocation.Text;
            string jAgency = JAgency.Text;
            bool activateCHK = ActivateCHK.Checked;
            DateTime createDate = DateTime.Now;
            DateTime endDate = EndDate.SelectedDate;
            
            PostingManager postingManager = new PostingManager();

            int postingId = PostingID;
            JobDbPostingEntity post = new JobDbPostingEntity();

            
            post.Active = activateCHK;
            post.EndDate = endDate;
            post.Jagency = jAgency;
            post.Jlocation = jLocation;
            post.CreateDate = DateTime.Now;
            post.Creator = UserID;
            post.Title = title;
            post.JobId = postingId;

            postingManager.Save(post, post.JobId);

            editPanel.Visible = false;          
            ClearForm();
        }

here is the adapter that i am using (just for clarity) in the class Connection

        public DataAccessAdapter Adapter
        {
            get
            {
                string connectionString = String.Empty;
                string catalogName = String.Empty;
                string schemaName = String.Empty;

                catalogName = ConfigurationSettings.AppSettings["CatalogName"];
                schemaName = ConfigurationSettings.AppSettings["SchemaName"];

                if (IsLive == "0")
                    connectionString = ConfigurationSettings.AppSettings["LocalConnectionString"];
                else
                    connectionString = ConfigurationSettings.AppSettings["LiveConnectionString"];

                CatalogNameOverwriteHashtable catalogChange = new CatalogNameOverwriteHashtable();
                catalogChange.Add("name", catalogName);

                SchemaNameOverwriteHashtable schemaChange = new SchemaNameOverwriteHashtable();
                schemaChange.Add("name", schemaName);

                DataAccessAdapter adapter = new DataAccessAdapter(connectionString, false, catalogChange, schemaChange);
                adapter.CommandTimeOut = 180;
                return adapter;
            }
        }

here is my fetch method

        public JobDbPostingEntity Fetch(int PostId)
        {
            JobDbPostingEntity post = new JobDbPostingEntity(PostId);

            Connection connection = new Connection();
            using(DataAccessAdapter adapter = connection.Adapter)
            {
                adapter.FetchEntity(post);
            }
            return post;
        }

here is my save method

        public JobDbPostingEntity Save(JobDbPostingEntity post)
        {
            Connection connection = new Connection();
            using(DataAccessAdapter adapter = connection.Adapter)
            {
                adapter.SaveEntity(post, true);
            }
            return post;
        }

OK so was playing around with it trying to get the SaveEntity() method to work and i discovered that saving with the same instance of my adapter class that i used to fetch it will work correctly, now the question becomes is there a way to save an entity and all related entity collection/ relations while not having the original instance of the connection open?

just tried editing the IsNewField to set it to false explicitly and it didn't work, it should have (by reading http://llblgen.com/documentation/2.6/hh_start.htm#Using%20the%20generated%20code/Adapter/Databinding%20at%20designtime%20and%20runtime/gencode_databinding_adapter.htm) saved the update in the database. the thing is that it didn't update the object, it didn't create a copy either...

OK i figured out that since my object has relation properties in a collection on it and i was adding objects to that collection as new objects the DAL thought that the first entity had to be new because the child collection was new, i proved this by not modifying anything in the child collection when i saved it, just set the PK and that's it. but my next question is that while it works this way (BTW i feel like an idiot because i didn't see this sooner) how do you specify a relational object collection that you will be modifying almost every time without re createing the main object, must you always delete the collection of relations or is there a better way?

ascholfield wrote:

how do you specify a relational object collection that you will be modifying almost every time without re createing the main object, must you always delete the collection of relations or is there a better way?

You should be able to use the same Entity/EntityCollection fetched to edit it and save it back.

the problem is that when object A is fetched it comes with a collection of object B. using object A I modify the object B collection by adding new items and deleting some items. then I save object A and the collection of object B is saved, but for some reason it saves the collection as new objects linked to a brand new copy of object A.

ok, i got it to the point where it's not saving a duplicate of object A but the collection of object B does not delete the deleted objects. before i save object A i go through and delete the object B collection and remove all objects from the collection in memory, the problem now is that it doesn't actually delete them in the database.

here is the code that i use

 Object A  = objectManager.Fetch(Id);
 objectManager.DeleteCollection(A.BCollection);
 A.Bcollection.Clear();
 // remake the collection as new objects on A
 //modify A
 //save A

ok so A saves fine with the updated info and the PK didn't change so it's not remakeing it any more, but the first collection B that I called the DeleteCollection function on is still there.

any ideas?

OK so i feel very stupid..... the problem with deleting the B collection was that as I passed it to the delete method it was an empty collection so the delete method worked, just on an empty collection. now i am deleting them by fetching and deleting by their FK value but why didn't the collection populate with the passing (using) of the collection. and how do i populate a sub collection without actually going through it's elements?

You should use PrefetchPaths or maybe delete via DeleteEntitiesDirectly. Use prefetchPatchs if you already has the collection fetched, if not _DeleteEntitiesDirectly _should be sufficient.