update data without getting data exists error in c# windows forms

[John6272]

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.

Continue reading...