How to validate duplicate records?

Question

0.00/5 (No votes)

See more:

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();
    }

Posted 16-Sep-14 19:30pm

DarkDreamer08

Updated 16-Sep-14 19:32pm

v2

Add a Solution

Comments

/\jmot 17-Sep-14 2:22am

which one is the primary key in your table??

DarkDreamer08 17-Sep-14 4:25am

checklist code is the primary key

ChintanShukla 17-Sep-14 2:30am

this statement if (c == lblchecklist_popup_code.Text || d == lblchecklist_popup_orderno.Text)
is returning false.
Debug and Check the value of c and d

DarkDreamer08 17-Sep-14 4:26am

what do you mean returning to false? i think it is right.

/\jmot 17-Sep-14 6:57am

check my solution. i think you'll get the solution.

2 solutions

Solution 1

Hi,

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

Rewrite your update query

C#

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();
        }

Posted 16-Sep-14 22:43pm

khem thapa

Updated 17-Sep-14 1:07am

v2

Comments

DarkDreamer08 17-Sep-14 21:02pm

but Checklist code is editable also. It should be updated whenever the user wants to. What the system will do is that to check if that code is already existing. but take note that the checklist code is a primary key in the table, so database will promptly tell that it should not be duplicated. so in terms of displaying such kind of error, an error message will appear.
- I know how to explain such things like this but I cannot sometimes put it in code :) That's the problem about me -_- Hope you guys understand :)

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

/\jmot · Accepted Answer · 2014-09-17T00:48:00

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.

C#

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;

}

How to validate duplicate records?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How to validate duplicate records?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us