0

I'm just feeling my way in the world of LINQ, and I've managed to get my VB app to SELECT from my Azure DB, now I'm trying to insert a new user.

I'm getting the error description 'Cannot insert explicit value for identity column in table x when IDENTITY_INSERT is set to OFF'.

I know there are similar questions on this subject, but it seems that most of them are having the problem because they ARE trying to insert an explicit value, and need to change the flag to ON. I'm trying to do the insert while letting the DB assign the ID value, so I believe the IDENTITY_INSERT flag should be off, and I need to change my VB or LINQ statement to tell it not to try to assign a value to my ID column.

Here is my code in the VB form button that runs the insert:

Private Sub btn_AddUser_Click(sender As Object, e As EventArgs) Handles btn_AddUser.Click
    Dim db As New stem1DataContext()

    Dim usr As New Users With
        {.firstname = txt_firstName.Text,
        .lastname = txt_lastName.Text,
        .username = txt_username.Text,
        .password = txt_password.Text,
        .email = txt_email.Text}

    db.Users.InsertOnSubmit(usr)

    Try
        db.SubmitChanges()
    Catch
        MsgBox("Form not valid:  " & Err.Description)
    End Try
End Sub

The usr statement has all fields in the table, besides the ID column. I don't really see where my code is trying to insert a value for the ID column, but it may be doing it somewhere behind the scenes and I need to explicitly tell it not to.

The SQL for the Users table is below:

CREATE TABLE [dbo].[Users] (
[Id]        INT          IDENTITY (1, 1) NOT NULL,
[username]  VARCHAR (50) NOT NULL,
[firstname] VARCHAR (50) NOT NULL,
[lastname]  VARCHAR (50) NOT NULL,
[email]     VARCHAR (50) NOT NULL,
[password]  NCHAR (10)   NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

Any help would be greatly appreciated.

Comintern
  • 21,855
  • 5
  • 33
  • 80
Terry Field
  • 105
  • 9
  • For anyone else who comes across this, I solved it by going into the .dbml for my connection, and under the ID column for the table, looked at the properties and set the property "Auto Generated Value" to TRUE. – Terry Field Dec 25 '16 at 05:57
  • I also realised, the reason I had this issue. When I first created the table, I forgot to set the ID field to an Identity column. I later went back in and updated the table. It turns out this update doesn't modify the dbml, which I guess makes sense, but you need to know that you need to go in and make this additional change to avoid the issue. – Terry Field Dec 25 '16 at 05:59
  • 1
    You should post your own answer for this question not display as Not Answered any more. – shadow Dec 25 '16 at 08:50
  • Thanks for the tip - answer added. – Terry Field Aug 07 '17 at 21:45

1 Answers1

1

For anyone else who comes across this, I solved it by going into the .dbml for my connection, and under the ID column for the table, looked at the properties and set the property "Auto Generated Value" to TRUE.

I also realised, the reason I had this issue. When I first created the table, I forgot to set the ID field to an Identity column. I later went back in and updated the table. It turns out this update doesn't modify the dbml, which I guess makes sense, but you need to know that you need to go in and make this additional change to avoid the issue.

Terry Field
  • 105
  • 9