Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
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
Updated 16-Sep-14 19:32pm
v2
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.

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;

}
 
Share this answer
 
v5
Comments
DarkDreamer08 17-Sep-14 20:30pm    
What do you mean by the GetNumOfRows_From_checklists? I did not understand that, I am sorry :)
/\jmot 18-Sep-14 1:04am    
you have to write a method name " GetNumOfRows_From_checklists()"
which'll return the number of record present in the database corresponding to a query.
wait,i'm updating my answer.
/\jmot 18-Sep-14 1:11am    
Now, see the result.if you have any question.then ask.
DarkDreamer08 18-Sep-14 1:32am    
It works so great!!! Thanks for that! I have a question. when I tried to update only the order no, it does not updated. An error message displayed saying, 'Error: Updated Checklist Code have a duplicate in table.Try different Checklist Code !' the problem here it should be updated even if the checklist code does not change. How to do that? I hope you extend your help to me :) Please :)
/\jmot 18-Sep-14 2:03am    
Use This..

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 if(no==1)
{
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_code='" + lblchecklist_popup_code.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);
}
}
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();
        }
 
Share this answer
 
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 :)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900