locked
Primary Violation method error RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below code and i want if Primary Key Violation error appears then message should be displayed "Record already exists" . Currently it is displaying Catch statement error

    try
    {
    string sql = "INSERT INTO test (Id,InvoiceNo) VALUES (@Id,@InvoiceNo)";
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    SqlCommand cmd0 = new SqlCommand(sql, con);
                    cmd.Parameters.AddWithValue("@Id", !string.IsNullOrWhiteSpace(txtCustomerId.Text) ? txtCustomerId.Text.ToUpper() : "");
                    
                    cmd.Parameters.AddWithValue("@InvoiceNo", !string.IsNullOrWhiteSpace(txtInvoiceNo.Text) ? txtInvoiceNo.Text.ToUpper().Trim() : "");
    
    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
    
    }
                catch (Exception ex)
                {
                    StringBuilder builder = new StringBuilder();
                    builder.Append("<script language=JavaScript>");
                    builder.Append("alert(\"" + ex.Message.Replace("\r\n", "") + "\")");
                    builder.Append("</script>");
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "FailAlert", builder.ToString());
                }



    Thanks

    Friday, July 19, 2019 4:51 AM

Answers

  • User665608656 posted

    Hi jsshivalik,

    should be displayed "Record already exists"

    According to your description, to achieve this ,you can just change the content in alert like this :

      builder.Append("alert(\"Record already exists!\")");

     SqlCommand cmd0 = new SqlCommand(sql, con);
                    cmd.Parameters.AddWithValue("@Id", !string.IsNullOrWhiteSpace(txtCustomerId.Text) ? txtCustomerId.Text.ToUpper() : "");
                    
                    cmd.Parameters.AddWithValue("@InvoiceNo", !string.IsNullOrWhiteSpace(txtInvoiceNo.Text) ? txtInvoiceNo.Text.ToUpper().Trim() : "");
    
    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
    

    I aslo found above code that your SqlCommand variable named cmd0, but when you use this variable later, you use cmd, you could better change cmd0 to cmd.

    I have made a simple example to insert data , you can refer to it:

    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                Id:<asp:TextBox ID="txtId" runat="server" ></asp:TextBox>
                Name:<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="Add" OnClick="Button1_Click" />
                <br />
                <br />
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id">
                    <Columns>
                        <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
                        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    </Columns>
                </asp:GridView>
            </div>
        </form>
    </body>
    </html>
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    GridView1.DataSource = BindData();
                    GridView1.DataBind(); 
                }
            }
            public DataTable BindData()
            {
                DataTable dt = new DataTable();
                con.Open();
                SqlCommand cmd = new SqlCommand("select * from Test", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                con.Close();
                return dt;
            }
            protected void Button1_Click(object sender, EventArgs e)
            {
                try
                {
                    string sql = "INSERT INTO Test (Id,Name) VALUES (@Id,@Name)";
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.Parameters.AddWithValue("@Id", !string.IsNullOrWhiteSpace(txtId.Text) ? txtId.Text.ToUpper() : "");
                    cmd.Parameters.AddWithValue("@Name", !string.IsNullOrWhiteSpace(txtName.Text) ? txtName.Text.ToUpper().Trim() : "");
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
    
                }
                catch (Exception ex)
                {
                    StringBuilder builder = new StringBuilder();
                    builder.Append("<script language=JavaScript>");
                    builder.Append("alert(\"Record already exists!\")");
                    builder.Append("</script>");
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "FailAlert", builder.ToString());
                }
                finally
                {
                    con.Close();
                    GridView1.DataSource = BindData();
                    GridView1.DataBind();
                }
            }

    The result of this work demo:

    If you want to avoid this judgment, @AddWeb recommends a good way .

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 19, 2019 6:48 AM

All replies

  • User-1038772411 posted

    Hello, jsshivalik

    If you set Autoincrement true in your db for primary key(ID) please make it falsa if you want to add manually id.

    before ExecuteNonQuery() please check ID Field manually in database and if record  exists  in database than please return [Your error message like : Record already exists].

    Thanks

    Friday, July 19, 2019 5:53 AM
  • User665608656 posted

    Hi jsshivalik,

    should be displayed "Record already exists"

    According to your description, to achieve this ,you can just change the content in alert like this :

      builder.Append("alert(\"Record already exists!\")");

     SqlCommand cmd0 = new SqlCommand(sql, con);
                    cmd.Parameters.AddWithValue("@Id", !string.IsNullOrWhiteSpace(txtCustomerId.Text) ? txtCustomerId.Text.ToUpper() : "");
                    
                    cmd.Parameters.AddWithValue("@InvoiceNo", !string.IsNullOrWhiteSpace(txtInvoiceNo.Text) ? txtInvoiceNo.Text.ToUpper().Trim() : "");
    
    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
    

    I aslo found above code that your SqlCommand variable named cmd0, but when you use this variable later, you use cmd, you could better change cmd0 to cmd.

    I have made a simple example to insert data , you can refer to it:

    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                Id:<asp:TextBox ID="txtId" runat="server" ></asp:TextBox>
                Name:<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="Add" OnClick="Button1_Click" />
                <br />
                <br />
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id">
                    <Columns>
                        <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
                        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    </Columns>
                </asp:GridView>
            </div>
        </form>
    </body>
    </html>
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    GridView1.DataSource = BindData();
                    GridView1.DataBind(); 
                }
            }
            public DataTable BindData()
            {
                DataTable dt = new DataTable();
                con.Open();
                SqlCommand cmd = new SqlCommand("select * from Test", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                con.Close();
                return dt;
            }
            protected void Button1_Click(object sender, EventArgs e)
            {
                try
                {
                    string sql = "INSERT INTO Test (Id,Name) VALUES (@Id,@Name)";
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.Parameters.AddWithValue("@Id", !string.IsNullOrWhiteSpace(txtId.Text) ? txtId.Text.ToUpper() : "");
                    cmd.Parameters.AddWithValue("@Name", !string.IsNullOrWhiteSpace(txtName.Text) ? txtName.Text.ToUpper().Trim() : "");
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
    
                }
                catch (Exception ex)
                {
                    StringBuilder builder = new StringBuilder();
                    builder.Append("<script language=JavaScript>");
                    builder.Append("alert(\"Record already exists!\")");
                    builder.Append("</script>");
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "FailAlert", builder.ToString());
                }
                finally
                {
                    con.Close();
                    GridView1.DataSource = BindData();
                    GridView1.DataBind();
                }
            }

    The result of this work demo:

    If you want to avoid this judgment, @AddWeb recommends a good way .

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 19, 2019 6:48 AM