You must Sign In to post a response.
  • Category: ASP.NET

    Cannot insert explicit value for identity column in table 'StatusChange' when IDENTITY_INS

    Hi I am designing web application when I click save after changing status it shows Cannot insert explicit value for identity column in table 'StatusChange' when IDENTITY_INSERT is set to OFF. can anyone help me to fix this.Below is the code for statuschange.

    Private Sub loadStatusHistory(ByVal alertID As String)

    Dim txtStatusHistory = ""
    Dim conSQL As New SqlConnection(conStrDRS)
    conSQL.Open()
    Dim cmdSQL As SqlCommand = New SqlCommand("SELECT [Status] ,format([Change_Datetime],'dd/MM/yyyy hh:mm:ss') ,[Changed_By] FROM [dbo].[StatusChange] WHERE [ID]='" & alertID & "'")
    cmdSQL.Connection = conSQL
    Dim adptSQL As New SqlClient.SqlDataAdapter(cmdSQL)
    Dim myDataSet As New DataSet()
    adptSQL.Fill(myDataSet)

    conSQL.Close()

    With myDataSet.Tables(0)
    For rowNumber As Integer = 0 To .Rows.Count - 1
    With .Rows(rowNumber)
    txtStatusHistory = txtStatusHistory + "<tr><td>" & .Item(0).ToString & "</td><td>" & .Item(1).ToString & "</td><td>" & .Item(2).ToString & "</td></tr>"
    End With
    Next
    End With

    lblDialogStatusHistory.Text = "<table border=""1px"" style=""width:100%;border-collapse: collapse""><tr><td style=""background-color: #f1f1c1;"">Status</td><td style=""background-color: #f1f1c1;"">Changed</td><td style=""background-color: #f1f1c1;"">Changed By</td></tr>" + txtStatusHistory + "</table>"

    End Sub
  • #764787
    Hi

    check this column StatusChange identity or not

    1.if identity yes means we no need pass value this field

    2.if identity no means we need must pass value this field

    How to create identity

    3.StatusChange int identity(1,1)

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #764790
    Hi,

    If you want to update the Identity column then you must and should set the "SET IDENTITY" table ON then only you can able to perform the action against Identity column,


    SET IDENTITY_INSERT #table ON

    INSERT INTO #table(id,name,city)VALUES(4,'kaavya','vijayawada'),(5,'kalpana','bangalore')

    SET IDENTITY_INSERT #table OFF


    If you still having doubts then refer below article, this might be helpful to you to clear your doubts.

    http://naveens-dotnet.blogspot.in/2013/08/how-to-set-identity-increment-on-off-in.html

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #764791
    Hi

    check this column StatusChange identity or not

    1.if identity yes means we no need pass value this field

    2.if identity no means we need must pass value this field

    How to create identity

    3.StatusChange int identity(1,1)

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #764795
    Hi all how to add this line because I already created table is there any way to add this

  • #764797
    In your table you may designed the "id" field as identity and not null. If you design like that the identity field will be auto increment you do not send the values for INSERT statement. But your identity setup may be off.

    Check whether the identity is on/off.

    SET IDENTITY_INSERT <tableName> ON
    Insert Statement

    By Nathan
    Direction is important than speed

  • #764801
    Hi Nathan this is my table what to change in this
    USE [DRS]
    GO

    INSERT INTO [dbo].[StatusChange]
    ([RegistrationID]
    ,[Status]
    ,[Changed_By]
    ,[Change_Datetime])
    VALUES
    (<RegistrationID, bigint,>
    ,<Status, varchar(2),>
    ,<Changed_By, char(100),>
    ,<Change_Datetime, datetime,>)
    GO

  • #764803
    Add that line as follows
    I hope "RegistrationID" may be identity. Correct?
    if yes do not send RegistrationId

    [INSERT INTO [dbo].[StatusChange]
    (
    [Status]
    ,[Changed_By]
    ,[Change_Datetime])
    VALUES
    (
    <Status, varchar(2),>
    ,<Changed_By, char(100),>
    ,<Change_Datetime, datetime,>)
    GO


    Still you want to update RegistrationID try the following

    USE [DRS]
    GO

    SET IDENTITY_INSERT [dbo].[StatusChange] OFF

    INSERT INTO [dbo].[StatusChange]
    ([RegistrationID]
    ,[Status]
    ,[Changed_By]
    ,[Change_Datetime])
    VALUES
    (<RegistrationID, bigint,>
    ,<Status, varchar(2),>
    ,<Changed_By, char(100),>
    ,<Change_Datetime, datetime,>)
    GO

    SET IDENTITY_INSERT [dbo].[StatusChange] ON

    By Nathan
    Direction is important than speed

  • #764807
    Hi Nathan I understood from your words I am asking how to add that new line in table because I have already created the table

  • #764808
    If you are trying from query analyser.
    Before insert data. simply run the command(SET IDENTITY_INSERT [dbo].[StatusChange] OFF
    ) in the query analyser

    By Nathan
    Direction is important than speed

  • #764823
    Hi Nathan its already there but it still shows error

    USE [DRS]
    GO

    /****** Object: Table [dbo].[StatusChange] Script Date: 2/26/2016 11:06:00 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[StatusChange](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [RegistrationID] [bigint] NULL,
    [Status] [varchar](2) NULL,
    [Changed_By] [char](100) NULL,
    [Change_Datetime] [datetime] NULL,
    CONSTRAINT [PK_StatusChange] PRIMARY KEY CLUSTERED
    (
    [ID] 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

    SET ANSI_PADDING OFF
    GO

  • #764825
    Hai Nagendra,
    It seems you are trying a value into IDENTITY column.
    You need to check your table whether do you have any identity column in your table.
    For Identity column, you cant insert the data.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #764833
    Hi
    Nagendra

    You can try this following steps you can achieve your issue

    Step1: Table Script Query
    ====================

    CREATE TABLE [dbo].[StatusChange](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [RegistrationID] [bigint] NULL,
    [Status] [varchar](2) NULL,
    [Changed_By] [char](100) NULL,
    [Change_Datetime] [datetime] NULL,
    CONSTRAINT [PK_StatusChange] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    SELECT * FROM STATUSCHANGE

    2. When you try to insert for the identity field you can meet this error i mention error given below i think this is ur issue so no need pass value for identity field so live it them i mention correct format the 3 option.

    INSERT INTO STATUSCHANGE VALUES(1,2,'RUNNING','A',GETDATE())

    --THIS ERROR OCCURES NOW

    Erorr
    ===

    Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table 'STATUSCHANGE' can only be specified when a column list is used and IDENTITY_INSERT is ON.


    3. This is right format working good no error through

    Normally identity field we no need pass

    INSERT INTO STATUSCHANGE VALUES(2,'OP','A',GETDATE())

    SELECT * FROM StatusChange

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #764837
    Hi Pawan So what can I do now?

  • #764849
    You are trying to insert something in column which is identity, You can turn on identity insert on the table like this so that you can specify your own identity values.
    see below sample
    SET IDENTITY_INSERT Table1 ON

    INSERT INTO Table1
    /*Note the column list is REQUIRED here, not optional*/
    (OperationID,
    OpDescription,
    FilterID)
    VALUES (20,
    'Hierachy Update',
    1)

    SET IDENTITY_INSERT Table1 OFF

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764860
    Copy and past the below code in to your query analyser and run. Let me know the status

    USE [DRS]
    GO

    SET IDENTITY_INSERT [dbo].[StatusChange] ON

    INSERT INTO [dbo].[StatusChange]
    ([RegistrationID]
    ,[Status]
    ,[Changed_By]
    ,[Change_Datetime])
    VALUES
    (1
    ,'ST'
    ,'Nathan'
    ,12/12/2015')

    GO

    By Nathan
    Direction is important than speed


Sign In to post your comments