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

Yee Keong 1 Reputation point
2022-10-11T08:24:24.157+00:00

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");  
}  

}

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,903 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. 2022-10-11T18:34:41.53+00:00

    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.

    0 comments No comments

  2. Erland Sommarskog 102.3K Reputation points
    2022-10-11T21:40:57.813+00:00

    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.

    0 comments No comments