Self Serviding: INSERT generated seems to assume a column is an IDENTITY column

Posts   
 
    
JayBee
User
Posts: 277
Joined: 28-Dec-2006
# Posted on: 07-Dec-2023 17:49:25   

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?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39709
Joined: 17-Aug-2003
# Posted on: 08-Dec-2023 08:47:47   

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) ?

Frans Bouma | Lead developer LLBLGen Pro
JayBee
User
Posts: 277
Joined: 28-Dec-2006
# Posted on: 08-Dec-2023 10:36:11   

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.

JayBee
User
Posts: 277
Joined: 28-Dec-2006
# Posted on: 08-Dec-2023 10:47:08   

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.

JayBee
User
Posts: 277
Joined: 28-Dec-2006
# Posted on: 08-Dec-2023 22:30:45   

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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39709
Joined: 17-Aug-2003
# Posted on: 09-Dec-2023 08:58:12   

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

Frans Bouma | Lead developer LLBLGen Pro
JayBee
User
Posts: 277
Joined: 28-Dec-2006
# Posted on: 09-Dec-2023 10:32:16   

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

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39709
Joined: 17-Aug-2003
# Posted on: 10-Dec-2023 10:00:04   

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

Frans Bouma | Lead developer LLBLGen Pro
JayBee
User
Posts: 277
Joined: 28-Dec-2006
# Posted on: 04-Jan-2024 23:01:16   

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.

Walaa avatar
Walaa
Support Team
Posts: 14975
Joined: 21-Aug-2005
# Posted on: 05-Jan-2024 10:16:53   

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).