CODEWITHSUNDEEP
c#sqlwinforms
karthik reddy
karthik reddy

Reputation: 454

How to avoid entering duplicate values into table through winform?

in my project I have set the client name as primary key and if I enter the same value, I will get exception, now I want to write the validation, i.e if I re enter the primary key value then I should get a message like "Data already exists", Please help me to do that, The code I am using to insert value is: 

      private void btnInsert_Click(object sender, EventArgs e)
       {
        if (txtName.Text == string.Empty)
        {
            MessageBox.Show("Please enter a value to Project Name!");
            txtName.Focus();
            return;
        }
        if (txtContactPerson.Text == string.Empty)
        {
            MessageBox.Show("Please enter a value to Description!");
            txtContactPerson.Focus();
            return;
        }
        SqlConnection con = Helper.getconnection();
        con.Open();
        string commandText = "InsertClient";
        SqlCommand cmd = new SqlCommand(commandText, con);
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@ContactPerson", txtContactPerson.Text);
        cmd.CommandType = CommandType.StoredProcedure;
        MessageBox.Show("Client details are inserted successfully");
        txtName.Clear();
        txtContactPerson.Clear();
        object Name = cmd.ExecuteNonQuery();
        con.Close();
        BindData();            
    }

Upvotes: 3

Views: 3755

Answers (4)

user2833558
user2833558

Reputation: 86

I understand your requirement, I see that you are asking about using of your own code instead of the exception. You can get it by using the try catch block. Try the following code: 

try
{
    object Name = cmd.ExecuteNonQuery();
    MessageBox.Show("Client details are inserted successfully");
    txtName.Clear();
    txtContactPerson.Clear();
    BindData();
}
catch(Exception ex)
{
    //Handle exception, Inform User
}
finally
{
    con.Close();
}

Upvotes: 2

user1450877
user1450877

Reputation: 421

I would tend to allow the user to try to enter any superficially valid primary key, If it is a duplicate then there will be an exception that you can catch and display to the user.

The reason for this is you would have to check the database for an existing key so you might as well do this by trying to insert it and handling any errors.

You could probably improve the validation and error handling a lot more, popping up a message box on every individual problem is annoying, better to have a summary with all the problems. Also holding open a database connection while displaying a message box probably isn't advisable either.

private void btnInsert_Click(object sender, EventArgs e)
   {
    if (txtName.Text == string.Empty)
    {
        MessageBox.Show("Please enter a value to Project Name!");
        txtName.Focus();
        return;
    }
    if (txtContactPerson.Text == string.Empty)
    {
        MessageBox.Show("Please enter a value to Description!");
        txtContactPerson.Focus();
        return;
    }
    SqlConnection con = Helper.getconnection();
    con.Open();
    string commandText = "InsertClient";
    SqlCommand cmd = new SqlCommand(commandText, con);
    cmd.Parameters.AddWithValue("@Name", txtName.Text);
    cmd.Parameters.AddWithValue("@ContactPerson", txtContactPerson.Text);
    cmd.CommandType = CommandType.StoredProcedure;

try
{
    object Name = cmd.ExecuteNonQuery();
    MessageBox.Show("Client details are inserted successfully");
    txtName.Clear();
    txtContactPerson.Clear();
    BindData();
}
catch(Exception ex)
{
        //Handle exception, Inform User
}
finally
{
        con.Close();
}      
}

Upvotes: 3

Thilina H
Thilina H

Reputation: 5802

First, you can prevent a duplicate from ever occurring in the table by using a unique index or constraint. An index/constraint can work in concert with the suggestions below. If you only use a unique index and not one of the below solutions, inserting a duplicate record will throw an error and you will need to handle that on the other end.

you could check for the records existence and insert or update manually:

create procedure MyProcedure
(
    @Name nvarchar(100),
    ...
)
as

    if not exists (select * from MyTable where Name = @Name)
    begin
        insert into MyTable (Name,...) values (@Name,...)
    end
    else
    begin
            update MyTable
            set ...
            where Name = @Name
    end

Upvotes: 3

Steven Wood
Steven Wood

Reputation: 2785

I tend to use Entity Framework as it will throw an exception in this case, however I suppose you could run an sql query first to check whether it exists or not, or though there may be a significant performance overhead with that

Upvotes: 1

Related Questions