I m using SQL Server 2014 and have a simple 2 column tabel:

/****** Object:  Table [dbo].[AAA_QualityCode]    Script Date: 7-12-2023 17:43:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AAA_QualityCode](
    [Code] [int] NOT NULL,
    [Description] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_AAA_QualityCode] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I am using LLBLGen 5.8.6 and generate SelfServicing code for asp.net 4.8. When I run the following code I get an exception:

            QualityCodeEntity qa = new QualityCodeEntity();
            qa.Code = -1;
            qa.Description = "Ongedefinieerd";
            qa.Save();

According to the exception info, the following Query is executed:

Query: INSERT INTO [HITc_Configuration].[dbo].[AAA_QualityCode] ([Description]) VALUES (@p2) ;SELECT  @p1 = SCOPE_IDENTITY()
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.
    Parameter: @p2 : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "Ongedefinieerd".

The exception created is because the Code field is null instead of -1.

What could be wrong?

I can't reproduce it, query inserts -1 just fine here (on 5.10).

Could you check if you have a validator set on the entity/field which might reject values < 0 ? Also could you check after setting Code if this reports true? qa.Fields.GetIsChanged((int)QualityCodeFieldIndex.Code) ?

This is the code:

using System; using System.Collections.Generic; using System.Linq; using System.Text; using HITc.AAA; using HITc.AAA.EntityClasses;

namespace Tester { class Program { static void Main(string[] args) { Test01(); Console.ReadLine();

    }

    private static void Test01()
    {
        QualityCodeEntity qa = new QualityCodeEntity();
        qa.Code = 2;
        qa.Description = "Ongedefinieerd";

        var changed = qa.Fields.GetIsChanged((int)QualityCodeFieldIndex.Code);
        Console.WriteLine(changed);
        qa.Save();
    }

}

}

I put a breakpoint at qa.Save(); The value of 'changed' is true at that point. It still gives the error.

I dropped the table, recreated it with the script as in the first mail. Sync LLBLG with it. Still no difference. I tried to run it against the same tabel but in another database instance. Same error.

Since the project was generated with previous versions of LLBLGen, I decided to delete all LLBLGen generated code and completely regenerate the definitions. This did not solve the problem.

I solved it. Had to remove the entity from the project and then regenerate it after syncing with the database. Then it was ok.

Before doing this I playes around with changing the mapping SequenceField which could be changed from blank to @@IDENTITY to @@SCOPE-IDENTITY . Probably when syncing LLBLGen does not check if these settings are still valid.

The last time I worked on t his code was in 2009. We are now upgrading to asp.net 4.8 and LLBLGen from 2.6 / 4.0 to 5... The definition of the column hasn't changed from 2009 till today.

Tho what is different now in the new entity? Did it have a sequence defined on the mapping for the pk field?

The table hasn't changed. The pk field (name Code) was and is an integer that never was an identity / sequence value.

Hmm. still odd tho... well keep an eye on it, if it doesn't work again, be sure to send us the project file!

I just was able to recreate the problem in LLBLGen 4.2 (I have to create code for Server 2003, 32-bit).

Define a table with a key column with Identity on (1,1). Generate a LLBLGen model, generate code, save it, work with it.

I have to migrate date from an existing table to this table but I want to keep the identifiers of the origimal table, so I remode the Identity in SQL Server (directly in the table definition, since LLBLGen does not seem to support the identity insert flag). Trying to run code with inserts of the identity value fails because the generated code does not promote the key value because in its model it is still an identity column. So, open the model and try to sync it with the database. After syncing look under the schema tree on the right of the screen and view the details of the key field. In my case it did not refresh the identity info to off / not present and it still shows that is an identity column.

Due to a lack of time I have not checked this in 5.8.

I've just checked with v.5.8

1- Created a table with an Identity PK

2- Synched the Relation Model Data in the LLBLGen Designer

3- Reverse-Engineered the table into an Entity

4- Went back to the Database and dropped the table and recreated it without the Identity on the PK field.

5- Re-synched the Relation Model Data in the LLBLGen Designer

6- Checked the Catalog Explorer (tree on the right), and Is Identity flag was removed from the PK Field.

7- Checked the Project Explorer (tree on the right), and the ReadOnly flag was still check for the PK Field.

So all you need to do is un-check the Read-Only flag in the Fields tab of the Entity. (Right Click on the entity and press Edit).

Walaa wrote:

I've just checked with v.5.8

1- Created a table with an Identity PK

2- Synched the Relation Model Data in the LLBLGen Designer

3- Reverse-Engineered the table into an Entity

4- Went back to the Database and dropped the table and recreated it without the Identity on the PK field.

5- Re-synched the Relation Model Data in the LLBLGen Designer

6- Checked the Catalog Explorer (tree on the right), and Is Identity flag was removed from the PK Field.

7- Checked the Project Explorer (tree on the right), and the ReadOnly flag was still check for the PK Field.

So all you need to do is un-check the Read-Only flag in the Fields tab of the Entity. (Right Click on the entity and press Edit).

I am now using 5.8 and still encounter this problem. In your step 4 I only changed the is identity in the database. When i resynced the relation model data, the catalog explorer still shows 'Is identity' on true. Then i closed LLBLGen, opened it and looked at the catalog explorer again. Now the 'is identity' is empty. I regenerated the code and run my program. In the program I do fill the identifier, but it is not passed by LLBLGen and i get the error that the field cannot be empty. I went into LLBLGen and had to change the ReadOnly field, as you described. This does not solve the problem, I still get the error that a NULL value cannot be inserted.

What does help is deleting the reengeneered entity from the project and regenerate it. For me this is not an acceptable solution.

The issue is that the Sequence (SCOPE_IDENTITY() ) is still present in the Pk field mapping in the entity, while the target pk field isn't an identity field anymore. There's currently no rule to remove a system sequence on a field mapping like this. This is an issue indeed, as system sequences (like @@IDENTITY and SCOPE_IDENTITY()) on a field mapping require the field to be marked as identity so the DB will insert the value. As that's not the case here, the insert will always fail.

The code currently checks if a sequence has to be set, but it doesn't reset it to null. The reason is that the sequence might have been set manually, either because it's not a system sequence or because the table uses an insert trigger (which pulls the identity value from the sequence). So we can't bluntly reset it, however in your situation that also introduces a problem.

So we'll look into adding a setting for this in 5.12 (so based on the setting a system sequence on a mapping will be reset in this case), as we can't simply reset it because of the reason above. We also will think of adding a warning for this so you at least get notified. We might end up with just the warning tho and not a setting, we have to think about whether this is going to be problematic for people who have mixed setups: some tables which have triggers and some tables which don't have triggers. It's a niche / edge case tho.

For you today, you can manually fix it without deleting the entity (however you'll find this out the hard way when inserting an entity): go into the mappings tab, set the sequence to the empty one (so no sequence) and uncheck the readonly checkbox on the field itself. Then regenerate the code.