CODEWITHSUNDEEP
c#sql-server
DSanches
DSanches

Reputation: 311

Violation of PRIMARY KEY constraint . Cannot insert duplicate key in object - ADO.NET

I'm trying to insert the data of a new person and I can at the first attempt but when and on the second attempt gives error violation of primary key and in fact, the person id (personId) is incremented each time it is trying to add a new person and so I realized the error is the primary key

table person 

CREATE TABLE [dbo].[Person] (
    [personID]    INT NOT NULL,
    [personName] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([personID] ASC)
);

method to create and insert the new person

protected void BtnCreate(object sender, EventArgs e)
    {
      SqlConnection connection = new SqlConnection(connectionString);
      //id to new person
      int id = 0;
      //command
      string command = "INSERT INTO [Person] ([personID], [personName]) VALUES (@id, @name)";

      SqlCommand cmd = new SqlCommand(command, connection);
      cmd.Parameters.AddWithValue("@id", id++);//id increase
      cmd.Parameters.AddWithValue("@name", name.Text);
      connection.Open();
      cmd.ExecuteNonQuery();
      connection.Close();
    }

any suggestion?

Upvotes: 2

Views: 2415

Answers (4)

DSanches
DSanches

Reputation: 311

Thank you all, all your answers are correct but I found a better solution for my case, I used the id as a static value initialized at least one and put out of function and it worked with no errors and works as I wanted and I thank your time and availability

my table don´t need to use identity because I intend to enter the ID increment every time you clicked on the button to create new person

CREATE TABLE [dbo].[Person] (
    [personID]    INT NOT NULL,
    [personName] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([personID] ASC)
);

I used static int id = -1, out of function and it´s work fine

//id to new person
private static int id = -1;

protected void BtnCreate(object sender, EventArgs e)
    {
      SqlConnection connection = new SqlConnection(connectionString);

      //command
      string command = "INSERT INTO [Person] ([personID], [personName]) VALUES (@id, @name)";

      SqlCommand cmd = new SqlCommand(command, connection);
      cmd.Parameters.AddWithValue("@id", id++);//id increase
      cmd.Parameters.AddWithValue("@name", name.Text);
      connection.Open();
      cmd.ExecuteNonQuery();
      connection.Close();
    }

Upvotes: 1

DaniDev
DaniDev

Reputation: 2631

If you are not concerned with the actual value of your personID field, (which your code seems to indicate). And, just want it to be unique and increment by 1.

In illustration of what @Steve has indicated, you could define your table that way: (in T-SQL)

CREATE TABLE [dbo].[Person] (
[personID] [int] IDENTITY(1,1) NOT NULL,
[personName] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([personID] ASC)
);

This will then eliminate your need to insert the personID

protected void BtnCreate(object sender, EventArgs e)
    {
      SqlConnection connection = new SqlConnection(connectionString);

      //command
      string command = "INSERT INTO [Person] ([personName]) VALUES (@name)";

      SqlCommand cmd = new SqlCommand(command, connection);
      cmd.Parameters.AddWithValue("@name", name.Text);
      connection.Open();
      cmd.ExecuteNonQuery();
      connection.Close();
    }

Upvotes: 1

Steve
Steve

Reputation: 216293

Your current code cannot work a second time because the id variable that you increment is a local variable allocated on the stack. When the method exits, everything on the stack is cleared. Clicking again the button you reenter the method but the variable is reset to zero and your increment sets again the variable to 1. Thus the duplicate exception for the personID field.

I recommend to change your personID column adding the IDENTITY property.
In this way the database calculates itself the next value to assign to the personID field and you don't need to remember what was the last id assigned to the table (and this is practically impossible to achieve in a safe way in a multiuser environment)

After adding the IDENTITY property, you could read back the value assigned by the database to your personID field changing your query to 

string command = @"INSERT INTO [Person] ([personName]) 
                   OUTPUT INSERTED.personID
                   VALUES (@name)";

and running the query using ExecuteScalar

  int id = Convert.ToInt32(cmd.ExecuteScalar());

Upvotes: 4

Alastair Brown
Alastair Brown

Reputation: 1616

Steve has a good answer, alternatively if can't re-arrange you DB schema, then you can do this:

protected void BtnCreate(object sender, EventArgs e)
{
  SqlConnection connection = new SqlConnection(connectionString);

  connection.Open();

  SqlCommand idcommand = new SqlCommand("select max([personID]) from Person", connection)
  //id to new person
  int id = ((int)idcommand.ExecuteScalar()) + 1;
  //command
  string command = "INSERT INTO [Person] ([personID], [personName]) VALUES (@id, @name)";

  SqlCommand cmd = new SqlCommand(command, connection);
  cmd.Parameters.AddWithValue("@id", id++);//id increase
  cmd.Parameters.AddWithValue("@name", name.Text);
  cmd.ExecuteNonQuery();
  connection.Close();
}

Upvotes: 0

Related Questions