1

When I attempt to add a new record and hence add a primary key, I get an error:

System.Data.SqlClient.SqlException occurred
HResult=0x80131904
Message=Violation of PRIMARY KEY constraint 'PK__Stock__BEAEB27A2652D17F'. Cannot insert duplicate key in object 'dbo.Stock'. The duplicate key value is (39).

I am sure that the total number of records currently is 38. The primary keys are added from an array which all have numbers from 39-44.

I have attached the code below:

int y = 0;

SqlCommand orderCommand3 = new SqlCommand();

string conString3 = Properties.Settings.Default.DatabaseEventsUnlimitedConnectionString;

using (SqlConnection connection3 = new SqlConnection(conString3))
{
    connection3.Open();

    while (ArrayCourseName[y] != "" && y <=5)
    {
        SqlCommand commandEvent = new SqlCommand("INSERT INTO STOCK (IngredientID, IngredientName, StorageType, QuantityInStock, MinimumRequired) VALUES (@p1, @p2, @p3, @p4, @p5)", connection3);

        commandEvent.Parameters.AddWithValue("@p1", ArrayIngredientID[y]);
        commandEvent.Parameters.AddWithValue("@p2", ArrayCourseName[y]);
        commandEvent.Parameters.AddWithValue("@p3", ArrayStorage[y]);
        commandEvent.Parameters.AddWithValue("@p4", ArrayQuantity[y]);
        commandEvent.Parameters.AddWithValue("@p5", ArrayMinimum[y]);

        int m = commandEvent.ExecuteNonQuery();

        if (m != 1)
        {
            throw new Exception("Too many records changed");
        }

        i++;
    }

    connection3.Close();
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bleari
  • 175
  • 1
  • 1
  • 8
  • 2
    Try making the primary key auto increment, you wont have to worry about adding them or duplicate keys anymore as the next number will be used automatically – Lucas Mar 14 '18 at 18:14
  • 2
    you are using y and doing i++, how that is going to work, or its typo....i think it should be y++ – Pranay Rana Mar 14 '18 at 18:16
  • @PranayRana has nailed it. You're not incrementing the right variable, so you are adding the same record in the array a second time, necessarily causing the index violation - and at precisely the location that would cause the error with the value you are observing. Great catch, pranay! – David W Mar 14 '18 at 18:18

1 Answers1

2

In your code you need to increment value of y not i, as you are using y as index value.

I suggest you make use of for loop will reduce complexity of your code 

for(int i=0;i<=5;i++) {
 if(string.IsNullOrWhiteSpace(ArrayCourseName[i] ))
   break;
 ///rest of the code
 using (SqlConnection connection3 = new SqlConnection(conString3))
 {
    connection3.Open();
    // code for sql non execute 
 }
}

i suggest you make use of using connection and create connection object every time , because connection pooling will take care of pooling connection object every time.

Ref : Is it better to execute many sql commands with one connection, or reconnect every time?

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263