Transaction not returning correct database ID

May 8, 2009 at 1:56 PM

Hi all,

I have a c# app which uses MySQL to save information to the database (using the Enterprise Library 3.1), however I'm having trouble returning the auto increment. Most times it works perfectly, but intermittently it can break, returning an incorrect ID (usually at high load times). The problem is that in some cases I will use this ID to add to other tables etc. I have inluded a snippet of how I am inserting into the database and how I retrieve the auto incremented ID. Previously I wasn't using transactions, however I updated the code assuming that transactions would fix the problem, however the concurrency issue still exists.

public static int Insert(Administrators c)
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase();

        c.ClientsID = Globals.ClientsID; // set to the current clientsid
        int AdministratorsID = 0;

        string ctext = "INSERT INTO administrators (Pass,Email,Name,ClientsID,Username,AType,AllowOverride,NonCreditCard) VALUES (?Pass,?Email,?Name,?ClientsID,?Username,?AType,?AllowOverride,?NonCreditCard)";
        DbCommand insertCommand = db.GetSqlStringCommand(ctext);

        db.AddInParameter(insertCommand, "?ClientsID", DbType.Int32, c.ClientsID);
        db.AddInParameter(insertCommand, "?AType", DbType.String, c.AType);
        db.AddInParameter(insertCommand, "?Name", DbType.String, c.Name);
        db.AddInParameter(insertCommand, "?Email", DbType.String, c.Email);
        db.AddInParameter(insertCommand, "?Username", DbType.String, c.Username);
        db.AddInParameter(insertCommand, "?Pass", DbType.String, c.Pass);
        db.AddInParameter(insertCommand, "?UniqueKey", DbType.String,;
        db.AddInParameter(insertCommand, "?NonCreditCard", DbType.String, (c.NonCreditCard ? "Yes" : "No"));
        db.AddInParameter(insertCommand, "?AllowOverride", DbType.String, (c.AllowOverride ? "Yes" : "No"));
            using (DbConnection connection = db.CreateConnection())

                DbTransaction trans = connection.BeginTransaction();
                insertCommand.Transaction = trans;


                ctext = "SELECT last_insert_id()";
                insertCommand = db.GetSqlStringCommand(ctext);
                insertCommand.Transaction = trans;

                AdministratorsID = Convert.ToInt32(db.ExecuteScalar(insertCommand));

        catch (DbException e)
            LogEntry log = new LogEntry();
            log.EventId = 105;
            log.Message = "Error Inserting Administrator To Database\r\n" + ctext + "\r\n" + e.ToString() + "\r\n" + e.StackTrace;
            log.Severity = TraceEventType.Information;
            log.Priority = 5;
        return AdministratorsID;


Any help provided would be much appreciated.

May 8, 2009 at 2:35 PM

I entered this into the wrong area. Please ignore.