dcfyj dcfyj - 6 months ago 35
SQL Question

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

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.

Answer

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.

Comments