更新字段时违反UNIQUE KEY约束条件
问题描述:
我正在使用gridview我添加文本框的行并添加成功完成的数据..但是,当我更新字段的电子邮件它显示错误。违反同时更新场更新字段时违反UNIQUE KEY约束条件
function: Update_U_Anonymoususerdetails
int InsId=0, UserId=0,UserInstLink=0,UserCreditRecord=0;
SqlConnection Cn = new SqlConnection(Conn);
Cn.Open();
for (int i = 0; i < GridView1.Rows.Count; i++)
{
Label Id = GridView1.Rows[i].FindControl("lblId") as Label;
TextBox Name = GridView1.Rows[i].FindControl("txtName") as TextBox;
TextBox MName = GridView1.Rows[i].FindControl("txtMName") as TextBox;
TextBox LName = GridView1.Rows[i].FindControl("txtLName") as TextBox;
TextBox Degree = GridView1.Rows[i].FindControl("txtDegree") as TextBox;
TextBox Title = GridView1.Rows[i].FindControl("txtTitle") as TextBox;
TextBox Email = GridView1.Rows[i].FindControl("txtEmail") as TextBox;
TextBox Institution = GridView1.Rows[i].FindControl("txtInstitution") as TextBox;
if (Name.Text.Trim() != null && Name.Text.Trim() !="")
{
int AId = 0;
if (Id != null)
AId = Convert.ToInt32(Id.Text);
else
AId = 0;
SqlCommand Cmd = new SqlCommand("UpdateOtherAuthors", Cn);
Cmd.CommandType = CommandType.StoredProcedure;
SqlParameter InID = new SqlParameter("@uiID", SqlDbType.Int);
InID.Direction = ParameterDirection.Output;
Cmd.Parameters.Add(InID);
Cmd.Parameters.AddWithValue("@Name", Name.Text);
Cmd.Parameters.AddWithValue("@MName", MName.Text);
Cmd.Parameters.AddWithValue("@LName", LName.Text);
Cmd.Parameters.AddWithValue("@Degree", Degree.Text);
Cmd.Parameters.AddWithValue("@Title", Title.Text);
Cmd.Parameters.AddWithValue("@Email", Email.Text);
Cmd.Parameters.AddWithValue("@Institution", Institution.Text);
Cmd.Parameters.AddWithValue("@Id", AId);
Cmd.Parameters.AddWithValue("@CaseId", CaseId);
Cmd.ExecuteNonQuery();
UserId = int.Parse(Cmd.Parameters["@uiID"].Value.ToString());//getting error in this line first
}
if (Institution.Text.Trim() != null && Institution.Text.Trim() != "")
{
SqlCommand cmd = new SqlCommand("AddInstitution", Cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter oParam = new SqlParameter("@Institution", Institution.Text.ToString());
SqlParameter InID = new SqlParameter("@InID",SqlDbType.Int);
oParam.Direction = ParameterDirection.Input;
InID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oParam);
cmd.Parameters.Add(InID);
cmd.ExecuteNonQuery();
InsId = int.Parse(cmd.Parameters["@InID"].Value.ToString());
}
UserInstLink= UserInstutionLink(UserId, InsId);// I am getting the error in this line
UserCreditRecord = UserCreditRecordID(UserInstLink, Name.Text, MName.Text, LName.Text, Degree.Text, Title.Text);
AssignContentManagementPrivileges(UserId,CaseId);
CreateCaseCredits(InsId, CaseId, UserCreditRecord);
}
Cn.Close();
store procedure i am using
CREATE PROCEDURE [dbo].[UpdateOtherAuthors]
(
@Name nvarchar(50),
@MName nvarchar(50),
@LName nvarchar(50),
@Degree nvarchar(50),
@Title nvarchar(50),
@Email nvarchar(50),
@Institution nvarchar(50),
@Id int,
@CaseId int,
@uiID int output
)
AS BEGIN
Declare @SameId nvarchar(50)
if(@Id='0')
begin
INSERT INTO tbl_AuthorDetails VALUES(@Name,@Degree,@Title,@Email,@Institution,@CaseId,@LName,@MName)
Select @Id=COUNT(Email) from [User] where [email protected]
if(@Id=0)
begin
exec CreateOtherAuthorsAccount @Name,@LName,@MName,@Degree,@Email
end
end
else
begin
set @SameId=(Select (Email) from tbl_AuthorDetails where [email protected])
if(@SameId = @Email)
Update tbl_AuthorDetails set [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected] where [email protected]
else
begin
INSERT INTO tbl_AuthorDetails VALUES(@Name,@Degree,@Title,@Email,@Institution,@CaseId,@LName,@MName)
Update tbl_AuthorDetails set CaseId=NULL where [email protected]
exec CreateOtherAuthorsAccount @Name,@LName,@MName,@Degree,@Email
end
end
END
set @uiID= (Select ID from [User] where [email protected])
RETURN @uiID
调用该函数唯一键约束的:
objGetBaseCase.Update_U_Anonymoususerdetails(GridView1, Convert.ToInt32(Request.QueryString[0]));
答
这是因为同一email
记录在数据库中已存在。并且数据库对电子邮件字段有一个限制,该字段中的每个条目应该是独特的,您将要插入数据的表中。
编辑:如果这个数据是由用户提供的,那么你需要抓住这个例外并显示一条消息给用户“具有相同的电子邮件地址在系统中已经存在的记录。”并允许他/她重新输入电子邮件字段的数据。
答
为了解决这个问题,你可以检查邮件是否已经存在:
IF NOT EXISTS (SELECT * FROM tbl_AuthorDetails where Email = @Email)
BEGIN
Update tbl_AuthorDetails set [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected] where [email protected]
END
比你请告诉我怎么解决这个问题 – Rocky 2011-06-01 12:55:26
使用不同的电子邮件地址 – Connell 2011-06-01 12:58:24
@connell沃特金斯:如果用户不要”知道的是,邮件编号已经在日期基地然后? – Rocky 2011-06-01 13:07:10