locked
How to write the syntax with C# if record already exist in db? RRS feed

  • Question

  • User-290759768 posted
    This is my first assignment for C# and new to the syntax.  Here is my insert function, where and how the syntax to write for if record already exist in database and prompt a message box?

    protected void btnInsertion_Click(object sender, EventArgs e)
    {
    try /* Insertion After Validations*/
    {
    using (NpgsqlConnection connection = new NpgsqlConnection())
    {
                        connection.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ToString();
                        connection.Open();
                        NpgsqlCommand cmd = new NpgsqlCommand();
                        cmd.Connection = connection;                    
                        cmd.CommandText = "INSERT INTO student_folio (f_name,l_name)VALUES(@f_name, @l_name) " ;                                               
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add(new NpgsqlParameter("@f_name", txtFname.Text));
                        cmd.Parameters.Add(new NpgsqlParameter("@l_name", txtLname.Text));                   
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        connection.Close();
                        Bind();
                        clearForm();
                    }
    }
                catch (Exception ex)
                {
                    throw ex;
                }
            }//end of btnInsertion_Click
    Wednesday, September 11, 2019 9:13 AM

Answers

  • User288213138 posted

    Hi Happy Bee,

    According to your description, you can try below code:

    If the value returned >=1, it exists, otherwise it does not.

    protected void Button1_Click(object sender, EventArgs e)
            {
                string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (NpgsqlConnection con = new NpgsqlConnection(constr))
                {
                    string query =string.Format( "SELECT COUNT(*) FROM Customer WHERE Name='{0}' and Country='{1}'",txtFname.Text,txtLname.Text);
                    NpgsqlCommand cmd = new NpgsqlCommand(query, con);
                    con.Open();
                    int rows =Convert.ToInt32(cmd.ExecuteScalar());
                    if (rows >= 1)
                    {
                        Response.Write("the value had existed");
                    }
                    else
                    {
                        Response.Write("the value not exist");
                    }
                }
    
            }

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 11, 2019 11:07 AM