none
How do I avoid any duplicate insert in my DB RRS feed

  • Question

  • Hello,

    below is my code where I want to prevent or avoid any duplicate record insert in the DB... but Im looking for a quick solution how to prevent insert duplicate record....hope any can help. THX

    protected void Button1_Click(object sender, EventArgs e)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Insert into Workstations values ('"+TextBox1.Text+"','"+TextBox2.Text+ "','" + TextBox3.Text + "')";

                cmd.ExecuteNonQuery();


                con.Close();
                Response.Redirect("default.aspx");
            }


    Tuesday, June 12, 2018 4:52 PM

All replies

  • Create a primary key constraint for the table using the columns in the table that define a unique row. You should be able to do this using either SQL or SQL Server Management Studio.

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-2017


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, June 12, 2018 5:08 PM
  • Just the first result my favorite search engine provided after entering 't-sql insert avoid duplicates' was 'Avoid duplicates in INSERT INTO SELECT query in SQL Server'. I think that might be what you're looking for.

    wizend

    Tuesday, June 12, 2018 5:08 PM
  • The best way is through constraints which could be one or more fields.

    For more information see my TechNet article done for both VB.NET and C# with source code on GitHub.

    .NET Defensive data programming part 2.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, June 12, 2018 5:40 PM
    Moderator
  • All the answers so far address how to set up your database to reject duplicates. This is something you should do, but if it does reject a duplicate your code gets an error and does unhappy things.

    In addition to protecting the database you should probably check beforehand to see if you are entering a duplicate and then you can handle that.

    cmd.CommandText = "select * from Workstations where *Put in the currect where clause*"
    

    Then check to see if you get anything returned.

    Ethan


    Ethan Strauss

    Tuesday, June 12, 2018 9:40 PM
  • All the answers so far address how to set up your database to reject duplicates. This is something you should do, but if it does reject a duplicate your code gets an error and does unhappy things.

    In addition to protecting the database you should probably check beforehand to see if you are entering a duplicate and then you can handle that.

    cmd.CommandText = "select * from Workstations where *Put in the currect where clause*"

    Then check to see if you get anything returned.

    Ethan


    Ethan Strauss

    Actually what you suggested is in the link I provided as shown below in a very simply example for one field, of course with multiple fields in the constraint you would adapt to that using the same pattern as a single field constraint.

    My TechNet article starts with the wrong way (which is okay for some) then to the correct or more acceptable way.

    /// <summary>
    /// Insert new record the right way by first determing if the country name
    /// is present in the database.
    /// </summary>
    /// <param name="pCountryName">Country name to insert</param>
    /// <param name="pIdentifier">New primary key</param>
    /// <param name="pError">Error message on failure</param>
    /// <returns>Success</returns>
    public bool InsertCountry1(string pCountryName, ref int pIdentifier, ref string pError)
    {
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand() { Connection = cn })
            {
                var selectStatement = "SELECT 1 FROM dbo.Country WHERE Name = @Name";
     
                var insertStatement = "INSERT INTO dbo.Country (Name)  VALUES (@Name);" +
                                        "SELECT CAST(scope_identity() AS int);";
     
                try
                {
                    cmd.CommandText = selectStatement;
                    cmd.Parameters.AddWithValue("@Name", pCountryName);
                    cn.Open();
     
                    if (cmd.ExecuteScalar() != null)
                    {
                        pError = $"Country '{pCountryName}' already in table";
                        mHasException = false;
                        return false;
                    }
     
                    cmd.CommandText = insertStatement;
     
     
                    pIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                    return true;
                }
                catch (Exception ex)
                {
                    mHasException = true;
                    mLastException = ex;
     
                    return false;
                }
            }
        }
    }

    ''' <summary>
    ''' Insert new record the right way by first determing if the country name
    ''' is present in the database and will not increment the primary key sequence.
    ''' </summary>
    ''' <param name="pCountryName">Country name to insert</param>
    ''' <param name="pIdentifier">New primary key</param>
    ''' <param name="pError">Error message on failure</param>
    ''' <returns>Success</returns>
    Public Function InsertCountry1(ByVal pCountryName As String, ByRef pIdentifier As Integer, ByRef pError As String) As Boolean
        Using cn = New SqlConnection() With {.ConnectionString = ConnectionString}
            Using cmd = New SqlCommand() With {.Connection = cn}
                Dim selectStatement = "SELECT 1 FROM ForumExample.dbo.Country WHERE Name = @Name"
     
                Dim insertStatement = "INSERT INTO dbo.Country (Name)  VALUES (@Name);" & "SELECT CAST(scope_identity() AS int);"
     
                Try
                    cmd.CommandText = selectStatement
                    cmd.Parameters.AddWithValue("@Name", pCountryName)
                    cn.Open()
     
                    If cmd.ExecuteScalar() IsNot Nothing Then
                        pError = $"Country '{pCountryName}' already in table"
                        mHasException = False
                        Return False
                    End If
     
                    cmd.CommandText = insertStatement
     
     
                    pIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
     
                    Return True
     
                Catch ex As Exception
                    mHasException = True
                    mLastException = ex
     
                    Return False
                End Try
            End Using
        End Using
    End Function

      

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, June 12, 2018 11:15 PM
    Moderator
  • Ohmygod, guys, answer the poor guys question.

    You need to figure out if the value you currently have is present in the db or not before you do the insert. There are two ways of doing this, either as a discrete operation before you send the data up or as part of your SQL statement. Due to the environment in which I do most of my work I rarely do a single row insert and instead normally have many rows, sometimes hundreds of thousands of rows, to insert into a table. Accordingly, I normally populate a DataTable in the application, upload the DataTable to a temporary table and then merge the temp table into the perm table. None the less, the logic of the insert remains the same.

    The following is a slightly modified snippet from some production code.

    First, a temp table is created with the right sort of columns.

    The temp table then receives all of the data that was collected. A bulk copy is *very* fast and easy to code.

    Finally, SQL is told to merge into the perm table only those rows that exist in the temp table but do not exist in the perm table. I've found that it is *much* faster generally to let SQL do the heavy lifting than it is to explicitly code it locally.

    try
    {
        // The entire datatable has been populated.
        // Connect to the database.
        using (SqlConnection conn = new SqlConnection( ConnectionString ))
        {
            conn.Open();
    
            // Update to a temporary table.
            string query =
            "Create table #temp( " +
            "[DeviceType] [nchar](30) NOT NULL, " +
            "[SerialNumber] [nchar](30) NOT NULL, " +
            "[Timestamp] [datetime2](7) NOT NULL, " +
            "[Keyname] [nchar](50) NOT NULL, " +
            "[Value] [nchar](50) NOT NULL) ";
            SqlCommand cmd = new SqlCommand( query, conn );
            cmd.ExecuteNonQuery();
            using (SqlBulkCopy bulk = new SqlBulkCopy( conn ))
            {
                bulk.DestinationTableName = "#temp";
                bulk.WriteToServer( table );
            }
    
            // Merge temp table with permanent table.
            string mergeSql =
            "Merge into [PermDest] as d " +
            "using #temp as s " +
            "on s.DeviceType=d.DeviceType AND s.SerialNumber=d.SerialNumber AND s.Timestamp=d.Timestamp " +
            "when not matched then " +
            "insert ([DeviceType], [SerialNumber], [Timestamp], [Keyname], [Value]) " +
            "values (s.DeviceType, s.SerialNumber, s.Timestamp, s.Keyname, s.Value);";
            cmd.CommandText = mergeSql;
            cmd.ExecuteNonQuery();
        }
    
    }
    catch (Exception ex)
    {
        // Handle the exception in an appropriate fashion. 
    }
    

    OK, now let's talk about the insert of a single line but only if that line doesn't exist in the dest table already. I don't have a ready example script for you so let me talk you through how to figure it out on your own.

    You already know how to code up a SQL script and to execute it. Open a Sql Server Management Studio instance and code up a script that tells you if the target row already exists, something like

    SELECT COUNT(*) from users where user_name like 'Adam' AND password like '123456'

    You then put this select script into your C# code something like this:

    using (SqlConnection conn = new SqlConnection( ConnectionString ))
    {
        conn.Open();
        string sqlCmd = “SELECT COUNT(*) from users where user_name like 'Adam' AND password like '123456'”;
    
        int userCount = (int) sqlCommand.ExecuteScalar();
        if (userCount == 0)
        {
            // Do the insert.
        }
    }
    
    I say 'something like this' because this is all non-production code that I've just scribbled down off the top of my head. But it should give you the general idea.


    Richard Lewis Haggard

    Tuesday, June 12, 2018 11:53 PM
  • @Richard,

    The poster is only doing one insert, not more than one. I do agree that merge is an option when dealing with a lot of records as in my MSDN code sample using MERGE. Yet when dealing with corporate or federal information than neither is going to be right. I work with federal data every day and we use very different logic to deal with duplicates. The agency I just came from deals with the same data as the agency I'm at now and they both work on nightly SQL-Server jobs to deal with millions of taxpayer data which when duplicates are encountered a review process is done.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, June 13, 2018 12:30 AM
    Moderator
  • All the answers so far address how to set up your database to reject duplicates. This is something you should do, but if it does reject a duplicate your code gets an error and does unhappy things.

    In addition to protecting the database you should probably check beforehand to see if you are entering a duplicate and then you can handle that. 

    Well actually, if you have a primary key constraint violation you simply handle the SqlException (in a try...catch block) as you would any other database operation. This is rather common and necessary, so there is really no need to check if another row with the same (duplicate) data already exists.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, June 13, 2018 4:01 AM