如何验证重复记录？ [英] How to validate duplicate records?

问题描述

大家好！



我在这部分遇到错误。我有一个带有编辑命令的网格视图。流程如下：如果用户输入了清单代码或订单号已存在于数据库中，则会显示错误消息。但错误是违反PRIMARY KEY约束'PK_checklists'。无法在对象'dbo.checklists'中插入重复键。

该语句已被终止。我的主键是清单代码



这是我的代码。

Hi Guys!

I am experiencing an error in this part. I have a grid view with edit command inside. the flow is like this: If the user input the checklist code or order No which is already existing in the database, an error message will display. But the error is, "Violation of PRIMARY KEY constraint 'PK_checklists'. Cannot insert duplicate key in object 'dbo.checklists'.
The statement has been terminated." My Primary Key is the checklist code

Here is my code.

protected void checklist_modalpopup_save_Click(object sender, EventArgs e)
    {
        string c="", d="";
        connection();
        amicassaCon.Open();
        SqlCommand check = new SqlCommand("SELECT checklist_code, order_no FROM checklists Where checklist_name ='" + lblchecklist_popup_name.Text + "' AND checklist_code='" + lblchecklist_popup_code .Text+ "'", amicassaCon);
        SqlDataReader rd = check.ExecuteReader();
        while (rd.Read())
        {
            c = rd[0].ToString();
            d = rd[1].ToString();

        }
        rd.Close();
        amicassaCon.Close();
        if (c == lblchecklist_popup_code.Text || d == lblchecklist_popup_orderno.Text)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Checklist Code/ Order Number have a duplicate in database!');", true);
            checklist_popup.Show();
        }
        else
        {
            connection();
            amicassaCon.Open();
            SqlCommand cmd = new SqlCommand("UPDATE checklists SET checklist_code='" + lblchecklist_popup_code.Text + "',order_no='" + lblchecklist_popup_orderno.Text + "',checklist_status='" + rrd_checklist_status.SelectedValue + "',  checklist_type='" + rrdchecklist_popup_type.SelectedValue + "' WHERE checklist_name = '" + lblchecklist_popup_name.Text + "'", amicassaCon);
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Close();
            amicassaCon.Close();
        }
        connection();
        checklist();
    }

推荐答案

您收到此错误的原因是：



，让你的桌子有两排：

row1-> checklist_code = code1，order_no = order1，checklist_name = name1

row2-> checklist_code = code2，order_no = order2，checklist_name = name2





现在，你试图用输入（code2，name1）搜索当前表，并获得空结果（否则你的部分条件）



之后，当代码尝试更新表时，它显示违反主键的错误，因为您正在使用现有主键的值更新主键。[在您的其他部分条件中]



i认为你明白你犯了错误。

You getting this Error because:

let, your table has two rows:
row1-> checklist_code=code1,order_no=order1,checklist_name=name1
row2->checklist_code=code2,order_no=order2,checklist_name=name2


now, you are trying to search the current table with input(code2,name1),and getting empty result(else part of your condition)

after that, when the code trying to update the table, it's showing the error of violation of Primary key because you are updating the primary key with the value of an existing primary key.[in your else part condition]

i think you got the point where you made a mistake.

protected void checklist_modalpopup_save_Click(object sender, EventArgs e)
    {
        string c="", d="";
        connection();
        amicassaCon.Open();
        SqlCommand check = new SqlCommand("SELECT checklist_code, order_no FROM checklists Where checklist_name ='" + lblchecklist_popup_name.Text + "' AND checklist_code='" + lblchecklist_popup_code .Text+ "'", amicassaCon);
        SqlDataReader rd = check.ExecuteReader();
        while (rd.Read())
        {
            c = rd[0].ToString();
            d = rd[1].ToString();
 
        }
        rd.Close();
        amicassaCon.Close();
        if (c == lblchecklist_popup_code.Text || d == lblchecklist_popup_orderno.Text)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Checklist Code/ Order Number have a duplicate in database!');", true);
            checklist_popup.Show();
        }
        else
        {

//Check here..where the table has any rows with updated checklist_code(lblchecklist_popup_code.Text)???
int no=GetNumOfRows_From_checklists("select * from checklists where checklist_code='" + lblchecklist_popup_code .Text+ "'");

if(no==0)
{

connection();
amicassaCon.Open();
SqlCommand cmd = new SqlCommand("UPDATE checklists SET checklist_code='" + lblchecklist_popup_code.Text + "',order_no='" + lblchecklist_popup_orderno.Text + "',checklist_status='" + rrd_checklist_status.SelectedValue + "',  checklist_type='" + rrdchecklist_popup_type.SelectedValue + "' WHERE checklist_name = '" + lblchecklist_popup_name.Text + "'", amicassaCon);
 SqlDataReader dr = cmd.ExecuteReader();

 dr.Close();
 amicassaCon.Close();
}
else
{
 ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Updated Checklist Code have a duplicate in table.Try different Checklis Code !');", true);
}
}
connection();
checklist();

    }

public int GetNumOfRows_From_checklists(string query)
{

int NumRows=0;
try
{
DataTable dt=new DataTable();
connection();
amicassaCon.Open();
SqlCommand  cmd=new SqlCommand(query,amicassaCon); 
SqlDataAdapter da=new SqlDataAdapter(cmd);
da.Fill(dt);
if(dt.Rows.Count>0)
{
NumRows=dt.Rows.Count;
}
else
{
NumRows=0;
}

}
catch(exception e1)
{
NumRows=-1;
}
return NumRows;

}

原因可能是你的< u> checklist_name 得到不同的行结果。最好有主键在哪里条件（建议）。



重写你的更新查询

Hi,

Reason may be your checklist_name getting different row result. Better to have primary key in where condition(suggestion).

Rewrite your update query

else
        {
            connection();
            amicassaCon.Open();
            SqlCommand cmd = new SqlCommand("UPDATE checklists SET order_no='" + lblchecklist_popup_orderno.Text + "',checklist_status='" + rrd_checklist_status.SelectedValue + "',  checklist_type='" + rrdchecklist_popup_type.SelectedValue + "' WHERE checklist_name = '" + lblchecklist_popup_name.Text + "' AND checklist_code='" + lblchecklist_popup_code .Text+ "'", amicassaCon);
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Close();
            amicassaCon.Close();
        }

这篇关于如何验证重复记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！