Duplicate Key when inserting into a SQL database from C# code

Question

I'm adding features to a program that was originally written by another programmer and now I've run into this dilemma. When I send the database this query,

INSERT INTO AQS_STATION_Road_Influences (STA_SerialCode,
TangentStreetNumber, TangentStreetName, RoadTypeCode,
TrafficCountValue, TrafficCountYear, DirectionToStreetCode,
TrafficCountSourceCode) Values (4, '45', '56', '3', '365', '2016',
'NE', '54'),  (141, '45', '56', '3', '365', '2016', 'NE', '54')

I get

Violation of PRIMARY KEY constraint 'PK_AQS_STATION_Road_Influences'. Cannot insert duplicate key in object 'dbo.AQS_STATION_Road_Influences'. The duplicate key value is (4, 45).

The statement has been terminated.

I've checked the table in the query it has no such primary key. When I do a select * from the table I get the 3 entries that I manually put in there. Which are as follows:

These are the current rows in the road influences table

The primary key for this table is a composite one which consists of STA_SerialCode and TangentStreetNumber. The confusing factor about this is that if I paste my insert query directly into SQL manager it runs just fine. I've tried running the insert one row at a time but it still threw the same error. I've also stepped through my code to ensure that the query I think is being passed is actually the one I want. For reference's sake this is the C# code I think is relevant:

public int DbUpdateTable(string strQuery, DataTable dtData)
        {
            int nRows;

            try
            {
                DbProviderFactory factoryProvider = DbProviderFactories.GetFactory(_strDbProvider);
                using (DbConnection connDb = factoryProvider.CreateConnection())
                {
                    try
                    {
                        connDb.ConnectionString = _strDbConnection;
                        connDb.Open();

                        using (DbTransaction dbTrans = connDb.BeginTransaction())
                        {
                            using (DbDataAdapter adapterDb = factoryProvider.CreateDataAdapter())
                            {
                                using (DbCommand cmdDb = connDb.CreateCommand())
                                {
                                    cmdDb.Transaction = dbTrans;
                                    cmdDb.CommandText = strQuery;

                                    if (strQuery.Contains("INSERT INTO"))
                                    {
                                        adapterDb.InsertCommand = cmdDb;
                                    }
                                    else if (strQuery.Contains("DELETE FROM"))
                                    {
                                        adapterDb.DeleteCommand = cmdDb;
                                    }
                                    else
                                    {
                                        adapterDb.SelectCommand = cmdDb;
                                    }

                                    using (DbCommandBuilder bldDb = factoryProvider.CreateCommandBuilder())
                                    {
                                        try
                                        {
                                            bldDb.DataAdapter = adapterDb;
                                            nRows = adapterDb.Update(dtData);
                                            dbTrans.Commit();
                                            return (nRows);
                                        }
                                        catch (Exception dbex)//DbException dbex)
                                        {
                                            Debug.Assert(false);
                                            dbTrans.Rollback();
                                            throw new Exception(dbex.Message);
                                        }
                                    }
                                }
                            }
                        }
                    }
                    catch (DbException ex)
                    {
                        Debug.Assert(false);
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        if (connDb.State != ConnectionState.Closed)
                        {
                            connDb.Close();
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Debug.Assert(false);
                Trace.WriteLine("Excpt: " + e.Message, this.ToString());
                throw new CybernetException("QUERY ERROR: " + strQuery);
            }
            finally
            {
            }

        }

Any help would be greatly appreciated.

EDIT: For an easier read, I'm adding the primary key SQL code from my comment in here.

ALTER TABLE [dbo].[AQS_STATION_Road_Influences] ADD CONSTRAINT
[PK_AQS_STATION_Road_Influences] PRIMARY KEY CLUSTERED (
[STA_SerialCode] ASC, [TangentStreetNumber] ASC )WITH (PAD_INDEX =
OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

EDIT 2:

SQL Profiler displaying what appears to be two sent queries.

Does that constraint 'PK_AQS_STATION_Road_Influences' exist under the constraints folder in this table when you view it in Management Studio? You may need to check to see how it is configured. — user1408767
– user1408767, Commented May 9, 2016 at 15:21
I wouldn't recommend passing in SQL in the way you do, instead use stored procedures and split out your functionality to determine the type of query. Also, do not throw a new exception since you lose the stacktrace. Instead just use throw. Finally, make sure you are looking at the right database. — user1666620
– user1666620, Commented May 9, 2016 at 15:22
are you sure your application is pointing to the same database that you're connected to in SSMS? — Kritner
– Kritner, Commented May 9, 2016 at 15:23
No, the constraint was added at the table's design time. When I right-click on the key and send it to a query window this is what I get: ALTER TABLE [dbo].[AQS_STATION_Road_Influences] ADD CONSTRAINT [PK_AQS_STATION_Road_Influences] PRIMARY KEY CLUSTERED ( [STA_SerialCode] ASC, [TangentStreetNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO — dcfyj
– dcfyj, Commented May 9, 2016 at 15:23
Is it possible that your method gets called more than once, attempting the same INSERT a second time? Or maybe the data table parameter holds a duplicate row? — Biscuits
– Biscuits, Commented May 9, 2016 at 18:28

dcfyj · Accepted Answer · 2016-05-17 15:42:02Z

So after looking for a few days and still being unable to figure out why the insert query was being sent twice, I ended up just rewriting the function and now it works just fine.

public int SimpleDBUpdateTable(string strQuery)
    {
        int nRows;

        try
        {
            DbProviderFactory factoryProvider = DbProviderFactories.GetFactory(_strDbProvider);
            using (DbConnection connDb = factoryProvider.CreateConnection())
            {
                try
                {
                    connDb.ConnectionString = _strDbConnection;
                    connDb.Open();
                    using (DbCommand cmdDb = connDb.CreateCommand())
                    {
                        cmdDb.CommandText = strQuery;
                        nRows = cmdDb.ExecuteNonQuery();

                        return nRows;
                    }
                }
                catch (DbException ex)
                {
                    throw;
                }
                finally
                {
                    if (connDb.State != ConnectionState.Closed)
                    {
                        connDb.Close();
                    }
                }
            }
        }
        catch (Exception e)
        {
            throw;
        }
    }

I'm guessing the duplication had something to do with the adapter and the command object, but as I said, I couldn't find the reason.

Collectives™ on Stack Overflow

Duplicate Key when inserting into a SQL database from C# code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related