具有独特的约束asp.net SQL更新命令 [英] SQL update command with unique constraint asp.net

问题描述

如果你请帮我，我试图用一种独特的约束是对所有四个键，更新表（ ROLE_ID，Track_ID，PERSON_ID，Conference_ID ）

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).

为什么不能更新呢？

错误：

说明：在执行过程中发生未处理的异常
  当前Web请求。有关详情，请堆栈跟踪
  有关错误的信息以及它起源于code。

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

异常详细信息：System.Data.SqlClient.SqlException：违反
  唯一键约束'UK_conferenceRole。无法插入重复键
  在对象dbo.ConferenceRole。该语句已终止。

Exception Details: System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK_conferenceRole'. Cannot insert duplicate key in object 'dbo.ConferenceRole'. The statement has been terminated.

code：

var sqlCon7 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
string query7 = @"update ConferenceRole set Role_ID =" + 3 + ",Person_ID='" + idp + "'where Conference_ID ='" + conference + "'and Track_ID='" + TrackId + "'";

SqlCommand cmd7 = new SqlCommand(query7, sqlCon7);
cmd7.CommandType = CommandType.Text;

try
{
    sqlCon7.Open();
    cmd7.ExecuteNonQuery();
    sqlCon7.Close();
}
catch (Exception ee) 
{ 
    throw ee; 
}

在此先感谢

推荐答案

您code应该是这样的 - 通过各种手段，避免只是你的SQL语句串在一起（这是门首战SQL注入！）。此外，使用ADO.NET，你应该把你的连接和命令到使用块，以确保不再需要时，他们得到妥善处置：

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 statement
string 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! 
}

什么错误说，你想创建一个重复的条目到表 - 一个具有组合（ROLE_ID，Track_ID，PERSON_ID，Conference_ID）已经存在。这是唯一约束的整点：以prevent这种情况的发生。因此，检查你的表 - 您必须已经在表中具有相同的四个值你要更新该行的任何一个的进入到....

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....

这篇关于具有独特的约束asp.net SQL更新命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！