"Cannot update identity column 'NextId'."

 

Still a newbie at Lightspeed, just upgraded from trial to professional, and have worked through the orientation CRUD demo video and examples I have found on the support blogs and comments.

 

I am now totally stuck with the following error, and cannot see what is different in this little test project from the replication of the CRUD demo that does work.

 

The error is: "Cannot update identity column 'NextId'."

 

Scenario:

sqlexpress database has two tables:  Birds and Sightings with a one-many relationship of birds to sightings.

 

A WinForms project that has a set of simple textedit boxes to capture my user input to post to the database.

 

App.config is as such:

 

 

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <configSections>

    <section name="lightSpeedContexts"

             type="Mindscape.LightSpeed.Configuration.LightSpeedConfigurationSection, Mindscape.LightSpeed" />

  </configSections>

 

  <lightSpeedContexts>

    <!-- TODO: Check pluralizeTableNames setting -->

    <!-- TODO: Add identityMethod="..." if not using KeyTable -->

    <add name="Development"

         connectionStringName="Development"

         dataProvider="SqlServer2005"

         identityMethod="KeyTable"

         pluralizeTableNames="True" />

  </lightSpeedContexts>

 

  <connectionStrings>

    <add name="Development"

         connectionString="Data Source=KENNETHJAMES-PC\SQLEXPRESS;Initial Catalog=LS_Demo;Integrated Security=True;Pooling=False"/>

  </connectionStrings>

 

</configuration>

 

Repository.cs is as such:

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Mindscape.LightSpeed;

 

namespace LSTest2WinForm

{

    public static class Repository

    {

        private static LightSpeedContext<ModelUnitOfWork> _context;

 

 

        public static LightSpeedContext<ModelUnitOfWork> Context

        {

            get

            {

                if (_context == null)

                {

                    _context = new LightSpeedContext<ModelUnitOfWork>();

                    _context.ConnectionString = LightSpeedContext.Default.ConnectionString;

                    _context.PluralizeTableNames = LightSpeedContext.Default.PluralizeTableNames;

                    _context.IdentityMethod = IdentityMethod.KeyTable;

 

                }

                return _context;

            }

        }

    }

}

 

 

BirdSighting database has three tables:

 

Bird - which includes the ID int NonNullable primary key column

Sightings - which also includes the ID column and BirdID for the link back to the parent table.

KeyTable - which has one column, NextId that is int and nonnullable.  In the database, there is one record with the value 1.

 

From the properties window in Server Explorer for the field NextId, it lists the following:

(Name) NextId

Data Type int

Identity Increment 0

Identity Seed 0

Is Identity False

Length 4

Nullable False

Precision 10

Scale 0

 

The code in the main form is as such:

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using Mindscape.LightSpeed;

 

namespace LSTest2WinForm

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        private void simpleButtonAddSpecies_Click(object sender, EventArgs e)

        {

 

            using (ModelUnitOfWork uow = Repository.Context.CreateUnitOfWork())

            {

                Bird bird = new Bird();

                bird.Species = textEditSpecies.Text;

                bird.Category = textEditCategory.Text;

                bird.Description = textEditDescription.Text;

                bird.WwCounty = radioGroupWWCounty.Text;

                bird.SpringSeen = radioGroupSpringSeen.Text;

                bird.SummerOccurence = radioGroupSpringOccurence.Text;

 

                uow.Add(bird);

                uow.SaveChanges();

            }

 

 

        }

    }

}

 

 

The line    uow.Add(bird);      fails with "SqlException was unhandled: Cannot update identity column 'NextId'."

 

In my other console application learning project, a very similar scenario works just fine.   I can't seem to spot any difference between the two projects that would have an impact on how the KeyTable is used.

 

In fact, I started over afresh with this project to make a "clean" attempt, and the result is still the same.

 

Suggestions?

 

Thank you.

 

Kenneth James

 

P.S. Found Mindscape on the VIstaDB blog a week ago, I was struggling with MS EntityFramework last summer and fall and finally just gave up.  With Mindscape, I see this is something more manageable for my limited needs and skills.

 

Hi Kenneth,

The issue you are seeing is because your identity method is "KeyTable". This relies on there being a table in your database called "KeyTable" with a field called "NextId". You can find the SQL scripts for generating this table in your LightSpeed install directory.

How are you wanting to manage the Id field for each table in your database? If you're not wanting to use the KeyTable pattern (for example, you may just have auto-incrementing id's set in your database) then you should change the IdentityMethod property on the LightSpeedContext. The help page about the supported identity methods is here.

Let me know if that helps and thank you very much for purchasing LightSpeed :-)

John-Daniel Trask

 Thanks for your quick reply.

 

I do have a KeyTable in the database.  Its properties were described above as:

 

KeyTable - which has one column, NextId that is int and nonnullable.  In the database, there is one record with the value 1.

 

From the properties window in Server Explorer for the field NextId, it lists the following:

(Name) NextId

Data Type int

Identity Increment 0

Identity Seed 0

Is Identity False

Length 4

Nullable False

Precision 10

Scale 0

I removed the trial edition of Lightspeed this morning and installed the new professional edition I just purchased, but the problem still exists.

Again, thank you for your help.

Kenneth James

Hi Kenneth,

Sorry about that - you did indeed say you had it in there. The message is being generated from SQL Server, not from LightSpeed and from my quick checking it would appear that for some reason SQL Server thinks "NextId" is an identity field when it shouldn't be. I realise from what you have quoted that Server Explorer is saying it's not an identity field so something fishy is certainly going on.

Could you please try deleting the KeyTable from your database (it has no dependencies so it should be fine) and recreating it? If possible, please use the script from the install directory. Also ensure you have a single value in there - only one row effectively (that can be "1" or "1000", it doesn't really matter :-)

Let me know how you get on,

John-Daniel Trask

 

I have tried numerous variations on the theme, to include rebuilding the sqlexpress database again from scratch, and copying in the KetTable script from the Mindscape install directory. 

 

In my little WinForms test app, it consistently comes up with this error.

 

However, when I build a new console app following the Video Demo, it does behave as it is expected.  No problem there.

 

Since I first found Mindscape mentioned on the VistaDB blog, and I use VistaDB data bases, I started to repeat me learning with VistaDB tables.

 

And now, I cannot even get connected to a VistaDB table.

 

I see there is already a thread started reference to VistaDB connection issues, so I am posting my situation with VistaDB there.

 

But in the interim, I have no idea what I have done differently between the WinForms app and the Console app that would cause this.

 

Thanks again for your advice.

 

Kenneth James

 

Hi Kenneth,

Just regarding the issue with SQL Server, are you able to back up the database and email it to me (support@mindscape.co.nz)?

We will answer the VistaDB connectivity questions in the thread you created for that issue.

I hope that helps,

John-Daniel Trask

 I have just e-mailed my test solution with backup database.

 

Kenneth James

Hi Kenneth,

The issue did turn out to be that the NextId field was set to be an identity (note that this is different from being a primary key - just to be confusing!).

Attached is a screenshot from my SQL Server Management Tools showing what needs to change. If you change "Is Identity" to "No" then you can save happily with your application.

I hope that helps and thank you for sending the repro. Please let me know if you need any additional assistance with this.

Kind regards,

John-Daniel Trask

 That change certianly fixed that problem.

Thanks for great support and for a great LS product.  :)

Kenneth James