How to prevent entering duplicate values into sql database table in asp.net while adding new record?

I have created a table in sql database named CATEGORIES. While adding new records, it is accepting duplicates which i dont want.

DAL:

 public void saveCAT(int id, string catname, bool act)
    {
        SqlCommand cmd = new SqlCommand("SAVECAT", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@CAT_ID", id);
        cmd.Parameters.AddWithValue("@CAT_NAME", catname);            
        cmd.Parameters.AddWithValue("@CAT_ACTIVE", (act) ? "Y" : "N");            
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

BAL:
 public void savcat(int id, string catname, bool act )
    {
        dll.saveCAT(id, catname, act);
    }

CS:

protected void btn_savcat_Click(object sender, EventArgs e)
{
    if (HiddenField1.Value == "")
    {
        bll.savcat(0, tb_catname.Text, Convert.ToBoolean(chk_actcat.Checked));
        Response.Write("<script>alert('Category Saved Successfully')</script>");
    }
    else
    {
        bll.savcat(Convert.ToInt16(HiddenField1.Value),tb_catname.Text, Convert.ToBoolean(chk_actcat.Checked));
        Response.Write("<script>alert('Category Updated Successfully')</script>");
    }
    bindgrid();
}

Stored Procedure:

ALTER PROC [dbo].[SAVECAT]
@CAT_ID int,
@CAT_NAME varchar(20),
@CAT_ACTIVE char(1)
AS
BEGIN
if(@CAT_ID=0)
BEGIN
INSERT INTO CATEGORIES(CAT_NAME) VALUES (@CAT_NAME)
END
ELSE
BEGIN
UPDATE CATEGORIES SET [email protected]_NAME,[email protected]_ACTIVE WHERE [email protected]_ID
END
END

Can any one help me with this, how to avoid entering duplicate values into database table. If duplicate value entered, error message should be displayed.

1 Answers

0
Suchir Patel On

Adding this condition while inserting may help you.

IF NOT EXIST (SELECT CAT_NAME FROM CATEGORIES WHERE LOWER(CAT_NAME)= LOWER(@CAT_NAME)) BEGIN INSERT INTO CATEGORIES(CAT_NAME) VALUES (@CAT_NAME) END