I've a decimal field with 10 precision and 0 scale, it throw the following exception when it updating the value to database.

Exception: An exception was caught during the execution of an action query: Parameter value '-1120616.0000' is out of range.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\ActionQuery.cs:line 196
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 530
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions) in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 1538
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 1397

Inner Exception: Parameter value '-1120616.0000' is out of range.
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\ActionQuery.cs:line 167

I compile LLBLGen source code in debug mode and trace the exception, I found the DynamicQueryEngine create incorrect SqlParameter, it create a SqlParameter with 4 scale, and cause the exception. I've check the persistence info, it is correct, but the SqlParameter created does not match the persistence info.

In SqlServerSpecificCreator.cs, method CreateParameter(), line 86. I try to change the last paramter from "value" to "value.ToString()" can fix the problem, but I don't know if it is the correct ways to handle this problem.

Which runtim library version are you using?

04022007

Do you use a TypeConverter on this field?

I also suspect that the DBSpecific project doesn't match the DBGeneric project. Would you please refresh the catalog from the Designer and re-generate the code, and make sure your application is using the newly generated code rather than some older version?

Not using any type convertor.

I've refreshed the project and generated all the files, and I've verified all generated files too, all are correct. Therefore, I started to trace the exception in LLBLGen source code, and found that line causing the problem.

I'll try to re-produce it and get back to you.

(edit) Unfortunatly I couldn't reproduce it. Everything works as expected over here.

I tried the following for insert and update and both worked perfectly:

AppleEntity apple = new AppleEntity();
apple.Size = -1120616;

And the value was set in the database for a corresponding field of decimal(10, 0)

I found what's wrong, please try the following code, it will create a SqlParameter (p2) with 4 scale

decimal value1 = -1120616M;
decimal value2 = -1120616.0000M;
SqlParameter p1 = new SqlParameter("@Qty", SqlDbType.Decimal, 0, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Current, value1);
SqlParameter p2 = new SqlParameter("@Qty", SqlDbType.Decimal, 0, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Current, value2);

I've done some calculation on the qty field, which cause the tailing zero to the decimal value. Please change your sample code and try test it again, it maybe able to reproduce my cause.

decimal value = 10; 
AppleEntity apple = new AppleEntity();
apple.Size = -1120616;
apple.Size.ToString(); // "-1120616"
apple.Size -= value ;
apple.Size.ToString(); // "-1120606.0000"

Barry wrote:

I've a decimal field with 10 precision and 0 scale, it throw the following exception when it updating the value to database.

Exception: An exception was caught during the execution of an action query: Parameter value '-1120616.0000' is out of range.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\ActionQuery.cs:line 196
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 530
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions) in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 1538
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\DataAccessAdapterBase.cs:line 1397

Inner Exception: Parameter value '-1120616.0000' is out of range.
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() in D:\Program Files\Solutions Design\LLBLGen Pro v2.0\RuntimeLibraries\Sourcecode\Net2.x\ORMSupportClasses\ActionQuery.cs:line 167

I compile LLBLGen source code in debug mode and trace the exception, I found the DynamicQueryEngine create incorrect SqlParameter, it create a SqlParameter with 4 scale, and cause the exception. I've check the persistence info, it is correct, but the SqlParameter created does not match the persistence info.

In SqlServerSpecificCreator.cs, method CreateParameter(), line 86. I try to change the last paramter from "value" to "value.ToString()" can fix the problem, but I don't know if it is the correct ways to handle this problem.

The parameter creation routine is correct, it uses the precision and scale as defined in the persistence info. If that's wrong (i.e. you say your field has a scale of 0 instead of 4), could you please explain how it can be that the persistence info says 'scale 4' and it should have been 0 ? Is the generated code in-sync with the db?

Also, there is a small issue today with v2.0, that it doesn't validate scale/precision with the passed in value automatically. This means that if you don't validate these values on scale/precision, it CAN be that you are able to store 10.5000M in a decimal field with 0 scale, which then fails at runtime as you saw.

persistence info has 0 scale in generated code, it is sync with the db and correct, only the SqlParameter created with 4 scale

because the decimal value in field has tailing zero, like 10.5000M, it change the SqlParameter.Scale to 4 automatically when it is assigned to SqlParameter, so it does not sync with persistence info, and cause the exception when it try to update value to database

you can see the following code, I assign 0 scale to SqlParameter, but it will it change to 4 automatically

decimal value = 10.0000M;
SqlParameter p1 = new SqlParameter("@Qty", SqlDbType.Decimal, 0, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Current, value );
p1.Scale // 4

but it it strange that it won't change it to 4, if I construct SqlParameter like this

decimal value = 10.0000M;
SqlParameter p1 = new SqlParameter("@Qty", SqlDbType.Decimal, 0, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Current, value.ToString() );
p1.Scale // 0

Ok, this indeed is caused by the issue I discussed, the lack of automatic precision/scale testing. Would it be possible for you to validate this in a validator at the moment?

Is it possible to add this validation in EntityBase class? Like the max length validation of string and byte[] data type, it can prevents potential problems.

We will solve this issue, but for now you may validate it yourself using any of our validation techniques. Ref: LLBLGenPro manaul "Using the generated code -> Validation per field or per entity"

ok, thanks!