locked
update data without getting data exists error in c# windows forms RRS feed

  • Question

  • hi,

    i am trying to update data without getting data exists error. i am working on Sqlite Database in database i have a table name "UserTable" and this table have 4 Columns (Id, Name, Phone, Address). before i insert data into database table i check that "Name" and "Phone" is exists or not. If exists then show message "Already Exists", if not then Insert data into database i used below code and working fine for me.

    try
    {
       if (this.txtName.Text.Trim() == string.Empty)
                    {
                        MessageBox.Show("Please Enter Name");
                        this.txtName.Focus();
                        return;
                    }
       if (this.txtPhone.Text.Trim() == string.Empty)
                    {
                        MessageBox.Show("Please Enter Phone No");
                        this.txtPhone.Focus();
                        return;
                    }
       if (this.txtAddress.Text.Trim() == string.Empty)
                    {
                        MessageBox.Show("Please Enter Address");
                        this.txtAddress.Focus();
                        return;
                    }
       using (SQLiteConnection conn = new SQLiteConnection("Data Source=Database.db;Version=3;"))
                    {
                        string commandtext = "SELECT count(*) FROM UserTable WHERE [Name]=@name AND [Phone]=@phone";
                        using (SQLiteCommand cmd = new SQLiteCommand(commandtext, conn))
                        {
                            conn.Open();
                            cmd.Parameters.AddWithValue("@name", this.txtName.Text);
                            cmd.Parameters.AddWithValue("@phone", this.txtPhone.Text);
    
                            int result = Convert.ToInt32(cmd.ExecuteScalar());
                            if (result > 0)
                            {
                                MessageBox.Show("This User is already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                            else
                            {
                                string insertquery = "INSERT INTO UserTable ([Name],[Phone],[Address]) VALUES (@name,@phone,@address)";
                                using (SQLiteCommand scmd = new SQLiteCommand(insertquery, conn))
                                {
                                    //conn.Open();
                                    scmd.Parameters.AddWithValue("@name", this.txtName.Text);
                                    scmd.Parameters.AddWithValue("@phone", this.txtPhone.Text);
                                    scmd.Parameters.AddWithValue("@address", this.txtAddress.Text);
    
                                    int save = Convert.ToInt32(cmd.ExecuteNonQuery());
                                    if (save > 0)
                                    {
                                        MessageBox.Show("Data Saved!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                }
                            }
                        }
                    }
    }
    catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
    

    This code working fine for me. Now i want to Update the data using "Id", when i update the data also check again that data is exists or not and then update here is code:

    using (SQLiteConnection conn = new SQLiteConnection("Data Source=Database.db;Version=3;"))
                    {
                        string commandtext = "SELECT count(*) FROM UserTable WHERE [Name]=@name AND [Phone]=@phone";
                        using (SQLiteCommand cmd = new SQLiteCommand(commandtext, conn))
                        {
                            conn.Open();
                            cmd.Parameters.AddWithValue("@name", this.txtName.Text);
                            cmd.Parameters.AddWithValue("@phone", this.txtPhone.Text);
    
                            int result = Convert.ToInt32(cmd.ExecuteScalar());
                            if (result > 0)
                            {
                                MessageBox.Show("This User is already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                            else
                            {
                                string updatequery = "UPDATE UserTable SET [Name]=@name, [Phone]=@phone, [Address]=@address WHERE [Id]=@id";
                                using (SQLiteCommand scmd = new SQLiteCommand(updatequery, conn))
                                {
                                    //conn.Open();
                                    scmd.Parameters.AddWithValue("@Id", this.txtId.Text);
                                    scmd.Parameters.AddWithValue("@name", this.txtName.Text);
                                    scmd.Parameters.AddWithValue("@phone", this.txtPhone.Text);
                                    scmd.Parameters.AddWithValue("@address", this.txtAddress.Text);
    
                                    int save = Convert.ToInt32(cmd.ExecuteNonQuery());
                                    if (save > 0)
                                    {
                                        MessageBox.Show("Data Updated!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                }
                            }
                        }
                    }

    This code also working fine and update data when i change the value of 3 textboxes(name,phone,address).

    My Problem is that:

    1: i don't want to change the value of name textbox i want to change the values of phone and address textbox only. the name textbox value as same as in database i don't want to change it. When i run the code i get "This user is already exists" Error.

    2: And if i want to change only name and address textboxes value and phone textbox value as same as it, when i run the code and get Error again "This user is already exists".

    Please help me how can i handle my problem..

    Any suggestion will help me a lot.

    thanks.

    Tuesday, October 20, 2020 6:39 PM

Answers

  • Try adding a condition:

    string commandtext = "SELECT count(*) FROM UserTable WHERE [Name]=@name AND [Phone]=@phone AND Id <> @id";

    . . .

    scmd.Parameters.AddWithValue("@id", this.txtId.Text);


    • Edited by Viorel_MVP Tuesday, October 20, 2020 7:50 PM
    • Marked as answer by John6272 Wednesday, October 21, 2020 5:56 AM
    Tuesday, October 20, 2020 7:49 PM
  • Thanks for reply,

    i just add a condition in select query like this:

    string commandtext = "SELECT count(*) FROM UserTable WHERE [Name]=@name AND [Phone]=@phone AND Id != @id";

    and it working perfectly just like i want.

    Thanks for solving my problem.

    • Marked as answer by John6272 Wednesday, October 21, 2020 5:57 AM
    Wednesday, October 21, 2020 5:56 AM

All replies

  • Try adding a condition:

    string commandtext = "SELECT count(*) FROM UserTable WHERE [Name]=@name AND [Phone]=@phone AND Id <> @id";

    . . .

    scmd.Parameters.AddWithValue("@id", this.txtId.Text);


    • Edited by Viorel_MVP Tuesday, October 20, 2020 7:50 PM
    • Marked as answer by John6272 Wednesday, October 21, 2020 5:56 AM
    Tuesday, October 20, 2020 7:49 PM
  • Hi John,

    Thank you for posting here.

    I cannot reproduce your error, the code will always return me the correct result.

    Is it possible that your modified name and phone pair does exist in the database?

    Sometimes we may reuse some similar simple data when testing, so I doubt this possibility a bit.

    In addition, according to the code, you seem to hope that name and phone are not unique, but their combination is unique, right?

    If so, I think you can consider using composite key.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 21, 2020 3:01 AM
  • Thanks for reply,

    i just add a condition in select query like this:

    string commandtext = "SELECT count(*) FROM UserTable WHERE [Name]=@name AND [Phone]=@phone AND Id != @id";

    and it working perfectly just like i want.

    Thanks for solving my problem.

    • Marked as answer by John6272 Wednesday, October 21, 2020 5:57 AM
    Wednesday, October 21, 2020 5:56 AM