vb.netvisual-studiolinq-to-sqldatatablesubmitchanges

Visual studio - IDENTITY_INSERT is set to OFF


I'm trying to add records to a local database using Visual Studio 2015 and linq to sql commands, but I get an error IDENTITY_INSERT is set to OFF.

I'm not inserting a value for the primary key and Identity Specification is set to True. This is the generated script file:

CREATE TABLE [dbo].[MONTE_CARLO] (
    [PK]        INT             IDENTITY (1, 1) NOT NULL,
    [ID]        INT             NOT NULL,
    [BOOK_COMP] NCHAR (10)      NOT NULL,
    [GREEK]     NCHAR (10)      NOT NULL,
    [DP1]       DECIMAL (18, 6) NOT NULL,
    [DP2]       DECIMAL (18, 6) NOT NULL,
    [DP3]       DECIMAL (18, 6) NOT NULL,
    [DP4]       DECIMAL (18, 6) NOT NULL,
    [DP5]       DECIMAL (18, 6) NOT NULL,
    [DP6]       DECIMAL (18, 6) NOT NULL,
    [DP7]       DECIMAL (18, 6) NOT NULL,
    [DP8]       DECIMAL (18, 6) NOT NULL,
    [DP9]       DECIMAL (18, 6) NOT NULL,
    [DP10]      DECIMAL (18, 6) NOT NULL,
    [DP11]      DECIMAL (18, 6) NOT NULL,
    [DP12]      DECIMAL (18, 6) NOT NULL,
    [DP13]      DECIMAL (18, 6) NOT NULL,
    [DP14]      DECIMAL (18, 6) NOT NULL,
    [DP15]      DECIMAL (18, 6) NOT NULL,
    [DP16]      DECIMAL (18, 6) NOT NULL,
    [DP17]      DECIMAL (18, 6) NOT NULL,
    [DP18]      DECIMAL (18, 6) NOT NULL,
    [DP19]      DECIMAL (18, 6) NOT NULL,
    [DP20]      DECIMAL (18, 6) NOT NULL,
    [DP21]      DECIMAL (18, 6) NOT NULL,
    CONSTRAINT [PK_MONTE_CARLO] PRIMARY KEY CLUSTERED ([PK] ASC)
);

This is the code to update the datatable. I'm passing a dictionary (dct)to the function which handles the updating.

Dim mcQuery = From updtMC In DATA.MONTE_CARLOs        'ID exists so update data
                          Where updtMC.ID = CStr(dct("ID")) And updtMC.BOOK_COMP = CStr(dct("BOOK_COMP")) And updtMC.GREEK = CStr(dct("GREEK"))
                          Select updtMC
            For Each updtMC As MONTE_CARLO In mcQuery

                updtMC.ID = dct("ID")
                updtMC.BOOK_COMP = dct("BOOK_COMP")
                updtMC.GREEK = dct("GREEK")
                updtMC.DP1 = dct("DP1")
                updtMC.DP2 = dct("DP2")
                updtMC.DP3 = dct("DP3")
                updtMC.DP4 = dct("DP4")
                updtMC.DP5 = dct("DP5")
                updtMC.DP6 = dct("DP6")
                updtMC.DP7 = dct("DP7")
                updtMC.DP8 = dct("DP8")
                updtMC.DP9 = dct("DP9")
                updtMC.DP10 = dct("DP10")
                updtMC.DP11 = dct("DP11")
                updtMC.DP12 = dct("DP12")
                updtMC.DP13 = dct("DP13")
                updtMC.DP14 = dct("DP14")
                updtMC.DP15 = dct("DP15")
                updtMC.DP16 = dct("DP16")
                updtMC.DP17 = dct("DP17")
                updtMC.DP18 = dct("DP18")
                updtMC.DP19 = dct("DP19")
                updtMC.DP20 = dct("DP20")
                updtMC.DP21 = dct("DP21")
            Next
        End If

        DATA.SubmitChanges()

Monte_Carlo settings

linq to sql files for Monte_Carlo:

<Global.System.Data.Linq.Mapping.TableAttribute(Name:="dbo.MONTE_CARLO")>  _
Partial Public Class MONTE_CARLO
    Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged

    Private Shared emptyChangingEventArgs As PropertyChangingEventArgs = New PropertyChangingEventArgs(String.Empty)

    Private _PK As Integer

    Private _ID As Integer

    Private _BOOK_COMP As String

    Private _GREEK As String

    Private _DP1 As Decimal

    Private _DP2 As Decimal

    Private _DP3 As Decimal

    Private _DP4 As Decimal

    Private _DP5 As Decimal

    Private _DP6 As Decimal

    Private _DP7 As Decimal

    Private _DP8 As Decimal

    Private _DP9 As Decimal

    Private _DP10 As Decimal

    Private _DP11 As Decimal

    Private _DP12 As Decimal

    Private _DP13 As Decimal

    Private _DP14 As Decimal

    Private _DP15 As Decimal

    Private _DP16 As Decimal

    Private _DP17 As Decimal

    Private _DP18 As Decimal

    Private _DP19 As Decimal

    Private _DP20 As Decimal

    Private _DP21 As Decimal

    Private _BOOK As EntityRef(Of BOOK)

Solution

  • Based on naming conventions in entity framework columns with name Id are considered as Id, probably you are having this problem. Try to change the name of field ID.