Sql Update Command With Unique Constraint Asp.net

If you please help me out I am trying to update a table with a unique constraint which is on all 4 keys (Role_ID, Track_ID, Person_ID, Conference_ID). Why can I not update it? erro

Solution 1:

Your code should look like this - by all means, avoid just stringing together your SQL statements! (that's the door opener to SQL injection!). Also, with ADO.NET, you should put your connection and command into using-blocks to make sure they get properly disposed when no longer needed:

// define/read connection string and SQL statementstring connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string sqlStatement = "UPDATE ConferenceRole SET Role_ID = @RoleID, " +
                      "Person_ID = @PersonID, " + 
                      "WHERE Conference_ID = @ConferenceID AND Track_ID = @TrackID";

// put your SqlConnection and SqlCommand into using blocks!
using(SqlConnection _con = new SqlConnection(connectionString))
using(SqlCommand _cmd = new SqlCommand(sqlStatement, _con))
{
   // define and set parameter values
   _cmd.Parameters.Add("@RoleID", SqlDbType.INT).Value = 3;
   _cmd.Parameters.Add("@PersonID", SqlDbType.INT).Value = idp;
   _cmd.Parameters.Add("@ConferenceID", SqlDbType.INT).Value = conference;
   _cmd.Parameters.Add("@TrackID", SqlDbType.INT).Value = trackId;

   // execute query 
   _con.Open();
   _cmd.ExecuteNonQuery();
   _con.Close();
}

And this construct here:

catch (Exception ee) 
{ 
    throw ee; 
}

is absolutely pointless - all it does is destroy the call stack, e.g. you won't be able to see where the exception really came from anymore - just leave it out all together, or if you must - use

catch (Exception ee) 
{ 
    throw;   // **DO NOT** specify the 'ee' exception object here! 
}

What the error says it that you're trying to create a duplicate entry into the table - one with a combination (Role_ID, Track_ID, Person_ID, Conference_ID) that already exists. That's the whole point of the unique constraint: to prevent this from happening. So check your table - you must already have an entry in your table that has the same four values as the one you're trying to update this row to....

Solution 2:

You defined Role_ID and/or Person_ID as your primary key in your table and there's already a record with the same values and that record is not the one with the conference and Track_ID you are passing in as parameters

Also your SQL statement is prone to SQL Injection attacks. Is best to parametrize your queries.

Solution 3:

you'll have to look on your database for the definition of that constraint, it will tell you which columns to look for, if you have Sql Management Studio or something similar, open your database, expand that table and look under constraints, right click and take a look at the definition of the UK_conferenceRole constraint, if you update your question and post that here, it will be easier to help you