none
Cannot insert explicit value for identity column in table 'persons' when IDENTITY_INSERT is set to OFF. RRS feed

  • Question

  • Hello. I got this error message in visual studio 2010 express  because I set my id into a primary key and because I set the Identity specification in 'yes' and Identity increment and identity seed  = '1' . Is there any problem in my code? Thank you in advance. How can I set ON the IDENTITY_INSERT without changing the Identity Specification?

    ______________________________________________________________________________________________________


    Imports System.Data.SqlClient
    Public Class Form1
        Dim myconnection As SqlConnection
        Dim mycommand As SqlCommand
        Dim dr As SqlDataReader
        Dim dr1 As SqlDataReader
        Dim ra As Integer
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            myconnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
            myconnection.Open()
            mycommand = New SqlCommand("insert into persons(id,name) values ('" & TextBox1.Text & "','" & TextBox2.Text & "')", myconnection)
            mycommand.ExecuteNonQuery()
            MessageBox.Show("New Row Inserted")
            myconnection.Close()
        End Sub
    End Class

    Tuesday, August 20, 2013 7:41 PM

Answers

  • This is not a Linq to SQL question, you are using ADO .Net Dataset and questions of this type should be posted in 

    If in the database you have insert identity on and seed equal to 1 then the database will generate the field and you will not be able to circumvent that operation. 


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Tuesday, August 20, 2013 7:54 PM

All replies

  • This is not a Linq to SQL question, you are using ADO .Net Dataset and questions of this type should be posted in 

    If in the database you have insert identity on and seed equal to 1 then the database will generate the field and you will not be able to circumvent that operation. 


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Tuesday, August 20, 2013 7:54 PM
  • I'm confused, why are you trying to insert a value for an auto incrementing identity column?

    If this is the case, you need to fill the row / insert values THEN if you want to read it / write it onto a form, do that in a seperate step.

    This is what I would do:

    1) you have to insert the values into the row without the auto increment, it will fill it self

    2) write another LINQ statement to read the full row if you want to write it on to a form

    I might need some clarity here might be needed but if it's been answered in ADO .Net Dataset than so be it


    Friday, August 23, 2013 8:58 PM