locked
Checking whether Duplicate Entry is available in database RRS feed

  • Question

  • User76138855 posted

    I am working on asp.net project. I want to insert entry in database bu before entrying it i want to check whether same data is present or not if present it will give msg that data exist and if not then insert command will execute. Ihave write folloing code

      public bool checkgrains()
        {
            con = new SqlConnection(ConfigurationManager.AppSettings["AR"]);
            con.Open();
            cmd = new SqlCommand();
            cmd.CommandText = "select grains from Uaddgrains where username=@u";
            cmd.Parameters.AddWithValue("@u", Session["username"].ToString());
            cmd.Connection = con;
            object obj= cmd.ExecuteScalar();
            con.Close();
            if (obj == null)
            {
                return true;
            }
            else
            {
                return false;
            }

     protected void btnUpload_Click(object sender, EventArgs e)
        {


            bool checkgrains;
            checkgrains = this.checkgrains();
            if (checkgrains == false)
            {
                lblmsg.Text = "Grains already exist";

            }
            else
            {

                SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["AR"]);
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Insert into Uaddgrains (grains,rate,username) values(@grain,@rate,@u)";

                cmd.Parameters.AddWithValue("@grain", ddlGrain.SelectedItem.ToString());
                cmd.Parameters.AddWithValue("@rate", txtRate.Text);
                cmd.Parameters.AddWithValue("@u", Session["username"].ToString());
                cmd.Connection = con;
                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                {
                    lblmsg.Text = "Upload sucessfull";
                    ddlGrain.Text = "--Select Grains--";
                    txtRate.Text = "";
                }
                else
                {
                    lblmsg.Text = "Record Not Updated";
                }
                con.Close();
                //ddlGrain.Items.Clear();
            }
        }

    Thursday, November 23, 2017 2:02 PM

Answers

  • User2103319870 posted

    Lordakku

    if (obj == null)

    Try with below code

    public bool checkgrains()
            {
                con = new SqlConnection(ConfigurationManager.AppSettings["AR"]);
                con.Open();
                cmd = new SqlCommand();
                cmd.CommandText = "select COUNT(*) from Uaddgrains where username=@u";
                cmd.Parameters.AddWithValue("@u", Session["username"].ToString());
                cmd.Connection = con;
                //object obj = cmd.ExecuteScalar();
                con.Close();
    
                int recordExist = (int)cmd.ExecuteScalar();
    
                if (recordExist > 0)
                {
                    //Record exist.
                    return false;
                }
                else
                {
                    //Record doesn't exist.
                    return true;
                }
    
            }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 24, 2017 2:30 AM

All replies

  • User2103319870 posted

    Lordakku

    if (obj == null)

    Try with below code

    public bool checkgrains()
            {
                con = new SqlConnection(ConfigurationManager.AppSettings["AR"]);
                con.Open();
                cmd = new SqlCommand();
                cmd.CommandText = "select COUNT(*) from Uaddgrains where username=@u";
                cmd.Parameters.AddWithValue("@u", Session["username"].ToString());
                cmd.Connection = con;
                //object obj = cmd.ExecuteScalar();
                con.Close();
    
                int recordExist = (int)cmd.ExecuteScalar();
    
                if (recordExist > 0)
                {
                    //Record exist.
                    return false;
                }
                else
                {
                    //Record doesn't exist.
                    return true;
                }
    
            }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 24, 2017 2:30 AM
  • User-335504541 posted

    Hi Lordakku,

    I have tested your code, it seems works fine. What problem do you meet?

    Best Regards,

    Billy

    Friday, November 24, 2017 3:10 AM
  • User3690988 posted

    Assuming that your username is a unique key in the table, I would just attempt the INSERT and check for an error.  This way there is only one call to SQL.
    SQL Error codes 2601 and 2627 are possible duplicate key errors.

    try {
    	cmd.ExecuteNonQuery();
    	Msg = "Update Successful";
    } catch (SqlException ex) {
    	if (ex.Number == 2601 | ex.Number == 2627) {
    		Msg = "Error: " + Session["username"] + " is already in database.";
    	} else {
    		Msg = "Error: " + Session["username"] + " not updated.";
    	}
    }

    Friday, November 24, 2017 12:01 PM
  • User753101303 posted

    Hi,

    Please ask an explicit question.  Your intent is unclear and for now it seems you check rather user uniqueness rather than really something else.

    You must have a single grains value for each user ???

    It's largely a matter of preference but capturing SQL Server error sides is the last thing I would do (for example you could take the occasion to give details about the existing data etc...). You could also do this check using Ajax etc...

    Edit : for now each user can insert a single row and I believe this is just not what you actually want ,???

    Friday, November 24, 2017 12:10 PM
  • User76138855 posted

    Hi Billy,

    Actually it was taking duplicate entry of grains. Even there was already entry in database.

    Saturday, November 25, 2017 9:49 AM
  • User76138855 posted

    Thank You,

    For suggestion..

    Saturday, November 25, 2017 9:50 AM
  • User76138855 posted

    It is working

    Thankx

    Saturday, November 25, 2017 9:50 AM