Primary Key Violation (duplicate key value?)

Question

I'm creating a program that adds/edits/deletes products from a database.

Everything else works great, but whenever I try to add a new product (testing with '1234'), I get a primary key violation error.

Violation of PRIMARY KEY constraint 'PK_Products'. Cannot insert duplicate key in object 'dbo.Products'. The duplicate key value is (1234 ). The statement has been terminated.

This product number most definitely does not already exist, and still doesn't exist when I search for it afterwards.

Can anyone help me figure out what may be causing this? I am not able to pinpoint the issue.

This is my AddProduct method.

public static bool AddProduct(Product product)
{
    SqlConnection connect = MMABooksDB.GetConnection();
    string insert = "INSERT Products (ProductCode, Description, UnitPrice) VALUES (@code, @description, @price)";
    SqlCommand insertCmd = new SqlCommand(insert, connect);
    insertCmd.Parameters.AddWithValue("@code", product.Code);
    insertCmd.Parameters.AddWithValue("@description", product.Description);
    insertCmd.Parameters.AddWithValue("@price", product.Price);
         try
            {
                connect.Open();
                int counter = insertCmd.ExecuteNonQuery();
                string selectStatement =
                    "SELECT IDENT_CURRENT('Products') FROM Products";
                SqlCommand selectCommand = new SqlCommand(selectStatement, connect);
                 int ProductCode;
                object dbProductID = selectCommand.ExecuteScalar();
             if (dbProductID != null || dbProductID != DBNull.Value)
                    ProductCode = 0;
                else
                    ProductCode = Convert.ToInt32(dbProductID);
                if (counter>0)
                    return true;
                else
                    return false;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connect.Close();
            }
}

And this is the Product class

    public class Product
    {
        public string code;
        public string description;
        public decimal price;

        public Product() { }

        public Product(string code, string description, decimal price)
        {
            this.Code = code;
            this.Description = description;
            this.Price = price;
        }

        public string Code
        {
            get
            {
                return code;
            }
            set
            {
                code = value;
            }
        }

        public string Description
        {
            get
            {
                return description;
            }
            set
            {
                description = value;
            }
        }

        public decimal Price
        {
            get
            {
                return price;
            }
            set
            {
                price = value;
            }
        }

        public string GetDisplayText()
        {
            return code + ", " + price.ToString("c") + ", " + description;
        }

        public string GetDisplayText(string sep)
        {
            return code + sep + price.ToString("c") + sep + description;
        }
    }
}

You guys have already helped me with this program once tonight, and I am very grateful. Thank you for any insight you can provide. Please let me know if any further information would be useful.

Read Aaron Bertrand's Bad habits to kick: making assumptions about IDENTITY in which he also explains why you should stay away from IDENT_CURRENT — marc_s
Patrick, while running through the debugger for the ~5th time to get you the line number, I finally figured out what was happening. In another part of my code, I was calling the AddProduct method twice. As it ran through the second time, it was finding the ProductCode that resulted from the first run through. I'm sorry it ended up being something so simple, but thank you guys for the help! — user3547070

Peter Peter · Accepted Answer · 2014-04-26T06:21:16

I geuss you forgot to do you productcode +1 somewhere. Why aren't you using auto increment on you pk? That will work any time.

Primary Key Violation (duplicate key value?)

2 Answers