how do i add a new registry in database without being duplicated,if duplicated name message will show (name existed)

Question

private void btn_Submit_Click(object sender, EventArgs e)
{
con.Open();

SqlCommand cmd = con.CreateCommand();  

if (txt_Name.Text == "Employee")  
{  
    cmd.ExecuteNonQuery();  
    con.Close();  

    display_data();  
    MessageBox.Show("Name existed");  
}  
else  
{  
    cmd.CommandType = CommandType.Text;  
    cmd.CommandText = "insert into Employee values('" + txt_Name.Text + "','" + txt_Contact.Text + "','" + txt_Address.Text + "','" + txt_Email.Text + "','" + txt_Password.Text + "')";  

    cmd.ExecuteNonQuery();  

    con.Close();  

    display_data();  
    MessageBox.Show("Inserted successfully");  
}  

}

Answer 1

Hello @Yee Keong and thanks for reaching out. In order to insert duplicate values in the same column you will need to disable any unique constraint. If the values fell under a column that is primary key or part of one that won't be possible since values need to be unique.

Let us know if you need additional assistance. If the answer was helpful, please accept it and complete the quality survey so that others can find a solution.

Answer 2

There are plentiful of ways to skin this cat. For instance you can do:

   cmd.CommandText = @"  
      IF EXISTS (SELECT * FROM Employee WHERE name = @name)  
          PRINT 'The name  ' + @name + ' has already been inserted.'  
      ELSE   
         INSERT INTO Employee (Name, Contact, Address, Email, Password)  
           VALUES(@name, @contact, @address, @email, @password)"  
   cmd.Parameters.Add("@name", SqlDbType.NVarChar, 40).Value = txt_Name.Text;  
   // and similar calls for remaining parameters  

The most important thing to observe here is that I have replaced your inlining of the input values with parameters. The type of inlining you have above is a cardinal sin with lots of issues: opens for SQL injection, causes cache litter in SQL Server, is a nightmare with date values and the list goes on. Using parameters like I do above is easier and more efficient. And safe for SQL injection.

In my example above, SQL Server produces a PRINT message. You can set up an InfoMessage event handler to get this message. As I am an SQL person and .NET person, I don't have an example on top of my head.

Another alternative is to convey the situation in an output parameter.

And yet a way is simply to insert and set up an exception handler that traps error 2627, primary key violation.