Duplicate Values in Primary Key (Access 12)

[Jeff Gaines]

I have a 'boiler plate' function for adding records to an Access 12
database as follows:

internal static int AddRecord(JAdLinks adLinks)
{
// Add Only - Use UpdateRecord for existing records

// Create the SelectCommand.
string selectString = "SELECT * FROM " + m_TableName;
selectString += " WHERE RecordNumber = ";
selectString += adLinks.RecordNumber.ToString();

// Create Connection
using (OleDbConnection dbConnection = GetOleDbConnection())
{
dbConnection.Open();

// CreateSelectAdapter
OleDbDataAdapter dbDataAdapter = CreateSelectAdapter(dbConnection,
selectString);

// Create Command To Add New Record
CreateInsertCommand(ref dbDataAdapter, dbConnection, adLinks);

// Fill the DataSet
DataSet dataSet = new DataSet();
dbDataAdapter.Fill(dataSet, m_TableName);

// Trap the RowUpdatedEvent - we want to return the new RecordNumber
dbDataAdapter.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);

// Create Command to get @@IDENTITY Value
m_oleCmdGetIdentity = new OleDbCommand();
m_oleCmdGetIdentity.CommandText = "SELECT @@IDENTITY";
m_oleCmdGetIdentity.Connection = dbConnection;

// Create New Row And Add Data
DataRow newRow = dataSet.Tables[m_TableName].NewRow();
SetupDataRow(ref newRow, adLinks);
dataSet.Tables[m_TableName].Rows.Add(newRow);

// Update the Row and trigger event - TODO this is a problem
// dbDataAdapter.Update(dataSet, m_TableName);

// This works but returns null & doesn't trigger OnRowUpdated
object obTemp = dbDataAdapter.InsertCommand.ExecuteScalar();
dbDataAdapter.InsertCommand.Connection.Close();

// Remove Event
dbDataAdapter.RowUpdated -= new OleDbRowUpdatedEventHandler(OnRowUpdated);
}
return m_NewRecordNumber;
}


This has been working fine for the couple of years I have been using it
and returns the record number of the new record.

I have now run into a problem when adding several records in quick
succession - the exception says there is a duplicate value in the primary
key (which is an Access AutoNumber field). I can overcome the problem by
using InsertCommand.ExecuteScalar(); followed by
InsertCommand.Connection.Close(); but this doesn't trigger the RowUpdated
event so I don't get the new record number returned.

I had a similar problem I a while back when 'deleted' records were
returned becuase I hadn't executed the DeleteCommand.Connection.Close();
function and I wonder if I am facing the same issue here.

Is there a way of flushing/closing the connection before the function
returns so I can continue to use Update? The 'using' statement should
close the connection but even a specific dbConnection.Close() makes no
difference. Alternatively is there a way to return the record number if I
use InsertCommand.ExecuteScalar()?

Many thanks.

 

[Jeff Gaines]

I have now run into a problem when adding several records in quick
succession - the exception says there is a duplicate value in the primary
key (which is an Access AutoNumber field). I can overcome the problem by
using InsertCommand.ExecuteScalar(); followed by
InsertCommand.Connection.Close(); but this doesn't trigger the RowUpdated
event so I don't get the new record number returned.

Sorry to follow up my own post but I have discovered what the problem is.

I have discovered that Access has its knickers in a twist in respect of
where it is for the Autonumber field. I had added a couple of records
manually as a test then I noticed it was allocating record numbers from
103 on whereas the highest record number is 827. As soon as I tried to add
a fifth record I got the same error from Access as I got from my code - so
2 weeks thinking my code was wrong and it was an Access problem :-(

I currently use the autonumber field in several tables in this database to
relate records to each other so re-numbering or re-setting the autonumber
field is going to be a major problem.

So a question for the gurus - if Access can get confused like this is it
better to set up my own fields/indexes to relate records to each other,
would that be more robust?

Many thanks.

 

[Jim Rand]

Just ran into this. Create a brand new Access database and import all the
objects. That should fix the autonumbering problem.

 

[Jeff Gaines]

Just ran into this. Create a brand new Access database and import all the
objects. That should fix the autonumbering problem.

Thanks Jim, I was about to do that when I was pointed to this by the author:

http://allenbrowne.com/ser-40.html

I am delighted to say that running the script fixed the issue