Hi

I am using the UpdateEntitiesDirectly method to update a large number of entities. Is there a maximum limit to the number of parameters the action query can take? I need to do a bulk update of 2 or 3 fields for around 8,000 rows but I get the following exception: "A severe error occurred on the current command. The results, if any, should be discarded.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. " Looking at the query and parameters all looks ok and this same code works with a small update - for example 5 rows.

What happens when you try to run the generated query against the database?

Also would you please post more details: Database type? Generated Query?....etc. Check the following thread for possible helpful information: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725

Thanks for the link - this was my first question on the forum Hopefully this is all the info you need:

LLBLGenPro 1.0.2005.1 Final Runtime file version 1.0.20051.60207

stacktrace: An exception was caught during the execution of an action query: A severe error occurred on the current command. The results, if any, should be discarded.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. : at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.UpdateEntitiesDirectly(IEntity2 entityWithNewValues, IRelationPredicateBucket filterBucket)

Using Adapter Vs.NET 2003 C# template for SqlServer with a SqlServer 2000 database

            MmClientEntity client = new MmClientEntity();
            client.DoNotCall = true;
            client.FTCDoNotCall = true;
            client.Fields[(int) MmClientFieldIndex.DNCEffectiveDateTimeUTC].ExpressionToApply = (MmClientFields.CreatedDateTimeUTC + 90);

            IPredicateExpression filter = new PredicateExpression();
            filter.Add( PredicateFactory.CompareRange( MmClientFieldIndex.Phone, false, phoneNumbers) );

            using (DataAccessAdapter adapter = new DataAccessAdapter()) 
            {
                return adapter.UpdateEntitiesDirectly(client, new RelationPredicateBucket(filter));
            }

Here's the generated Query (I have truncated the paramter list to make it more readable and changed the phone number to be a fake:

UPDATE [MoneyMasters].[dbo].[mmClient] SET [DNCEffectiveDateTimeUTC]=[MoneyMasters].[dbo].[mmClient].[CreatedDateTimeUTC] + @LO30851,[DoNotCall]=@DoNotCall,[FTCDoNotCall]=@FTCDoNotCall WHERE ( ( ( [MoneyMasters].[dbo].[mmClient].[Phone] IN (@Phone2,.....,@Phone8363))))

Parameter: @LO30851 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 90.
Parameter: @DoNotCall : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: True.
Parameter: @FTCDoNotCall : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: True.
Parameter: @Phone2 : AnsiString. Length: 255. Precision: 0. Scale: 0. Direction: Input. Value: (555) 555-5555.

If I copy that into query analyzer and replace the parameter names with the values listed then the query runs OK and updates the rows.

Using thousands of parameters in a range predicate (IN clause) is not recommended at all. So to work around this, here are a couple of suggestions:

1- If there are any commonality between the phone numbers being updated, try to filter out the table based on those commonalities, like for example you are updating all the numbers that starts with (555). Or there are no numbers in-between that are not updated so you may use a BETWEEN predicate.

2- You may update them in chunks (say 100 record per chunk) and you may use the same database connection and possibly use a transaction to carry out all the updates.

Thanks for your help. Unfortuantely, there is no commonality between the phone numbers so I think I will break it up into chunks within a transaction. Thanks again for your time.