MySQL not returning correct ID 100% of the time

Hi everyone,

I have a c# app which uses MySQL to save information to the database, however I'm having trouble returning the auto increment. Most times it works perfectly, but intermittently it can break, returning an incorrect ID. 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:

 

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, c.id);
        db.AddInParameter(insertCommand, "?NonCreditCard", DbType.String, (c.NonCreditCard ? "Yes" : "No"));
        db.AddInParameter(insertCommand, "?AllowOverride", DbType.String, (c.AllowOverride ? "Yes" : "No"));
        try{
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                db.ExecuteNonQuery(insertCommand);

                ctext = "SELECT last_insert_id()";
                insertCommand = db.GetSqlStringCommand(ctext);
                AdministratorsID = Convert.ToInt32(db.ExecuteScalar(insertCommand));

                connection.Close();
            }
        }
        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.Categories.Add("Database");
            log.Severity = TraceEventType.Information;
            log.Priority = 5;
            Logger.Write(log);
        }
        return AdministratorsID;
    }

 

Any feedback provided will be much appreciated.

0
Chelseaflo
4/29/2009 6:36:35 AM
asp.net.mysql 1929 articles. 0 followers. Follow

1 Replies
616 Views

Similar Articles

[PageSpeed] 49

last_insert_id will return the last ID any user has inserted into any table that has an auto-increment column. 

What you are seeing is a concurrency issue between multiple users of your page. You will need to run your statements within a transaction:

using (DbConnection connection = db.CreateConnection())
{
  connection.Open();

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

  db.ExecuteNonQuery(insertCommand);
  ctext = "SELECT last_insert_id()";
  insertCommand = db.GetSqlStringCommand(ctext);
  insertCommand.Transaction = trans;
  AdministratorsID = Convert.ToInt32(db.ExecuteScalar(insertCommand));

  trans.Commit();
 
  connection.Close();
}
0
uyaem
4/29/2009 8:58:25 AM
Reply:

Similar Artilces:

MySQL
Currently have a site coded in PHP but the customer wants to go to .NET. Can I use the existing MySQL DB or am I required to use MSSQL? Thanks, Ken Check out this forum which is specialized for this kind of questions: ASP.NET MySQL forumBart De Smet [MVP] Visit www.msdn.be, www.bartdesmet.net...

mysql php5-mysql
Hi, i have installed mysqk from binaries itworks!!! but now how can i install php5-mysql and dependencies? Many thanks Thierry -- trazomtg ------------------------------------------------------------------------ When you say from the binaries, do you mean you installed via Yast and the Suse repositories?? It is always best to install via Yast-Software-Software Management if you can. This way all dependencies are done for you. -- gogalthorp ------------------------------------------------------------------------ gogalthorp's Profile: http://forums.opensuse....

Help: MySQL Time Type Null (ODBC.NET) Urgent ! ! !
Hi, All In my web application, I'm using MySQL 4.0.21 (mysqld-max-nt)+ASP.NET 1.1 (SP1)+ODBC.NET + MyODBC 3.15.09 @window 2000 SP4 (tried 2000 standard server & professional with same error) MDAC 2.80 I have following error: I have a table named 'SHIFT': CREATE TABLE `shift` ( `SHIFT_ID` tinyint(3) unsigned NOT NULL default '0', `SHIFT_CODE` varchar(10) NOT NULL default '' `SHIFT_STARTTIME` time NOT NULL default '00:00:00', `SHIFT_ENDTIME` time NOT NULL default '00:00:00', `SHIFT_MIDDLETIME` time NOT NULL default '00:00:00', `SHIFT_HALF_END` time NOT N...

Net::MySQL
------=_NextPart_000_0026_01C30A6E.486AE070 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Anyone run into this problem=20 $mysql->query(q{ INSERT INTO dbname (table_name) VALUES ( $var ) }); The problem is, when I assign a value to a $var that value is ignored in = the SQL statement. Ive tried every operator I know to get it to look at = it and accept the value as input into the DB. Ive tried=20 ( '$var' ) ("$var") ("\$var") ([$var]) ({$var}) Any ideas ? Kind regards...

mysql and .net
Hi there, i am new to .net and i am trying to setup a simple form. I am an open source person and what i am asking can be done easily using php and mysql; but i need to know how to do this in .net project asap. i have three forms; form1 submit to the database and redirect to form2 and form2 submit to the database and redirect to form3. i want to grab mysql last inserted id from form1 to form2 while redirecting and pass it via the url. i am able to select from the database, but i don't know how to assign it to a variable; here is what i have so far: str2 = "select last_ins...

Suse MySQL and MySQL workbench
I am looking at the downloads here 'MySQL :: Download MySQL Workbench' (http://dev.mysql.com/downloads/workbench/1.0.html#downloads) and 'MySQL :: Download MySQL Community Server' (http://dev.mysql.com/downloads/mysql/#downloads). As one is source code and the other is suse enterprise linux, are these the best downloads for MySQL on suse 11.3? Is there a best source for the install? Suse specific rpm's? -- flebber ------------------------------------------------------------------------ Try zypper or YAST first to look at the OpenSuSE repositories. ...

MySQL time
Hi, Please tell me how can I find the time needed to pass for a perl program execute an SQL query using MySQL, DBI and DBD::mysql. (That time shown by the mysql standard client). Thank you. Teddy On Mon, 14 Mar 2005, Octavian Rasnita wrote: > Please tell me how can I find the time needed to pass for a perl > program execute an SQL query using MySQL, DBI and DBD::mysql. You find the time required by benchmarking the procedure. If this isn't what you mean, maybe you could clarify what you mean by "time" (if you have some MySQL-specific concept of...

linked server MySql to Mysql
Can i make linked server from MySql to another Mysql server?...

Perl Mysql
Hi all, I am installing Bugzilla3 on a windowsXP box together with Mysql5.0.45 and ActivePerl-5.8.8.820. It passed the checksetup and created the database. However, when I point my Firefox to http://localhost/ I got the following error: install_driver(mysql) failed: Can't load 'C:/Perl/site/lib/auto/DBD/mysql/mysql.dll' for module DBD::mysql: load_file:The specified module could not be found at C:/Perl/lib/DynaLoader.pm line 230. at (eval 34) line 3 Compilation failed in require at (eval 34) line 3. Perhaps a required shared library or dll isn't installed...

DBD::mysql bad auto/DBD/mysql/mysql.bundle file on OS X
Howdy group :) Having a bit of trouble getting DBD::mysql installed on OS X I get this error: root# perl -e 'use DBD::mysql;' Can't load '/Library/Perl/5.8.1/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: /Library/Perl/5.8.1/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle(2): Not a recognisable object file at -e line 1 Compilation failed in require at -e line 1. BEGIN failed--compilation aborted at -e line 1. root# Here is what is in the directory: root# ls /Library/Perl/5.8.1/darwin-thread-multi-2level/au...

Mysql
how to write a connection string for Mysql  with asp.net2.0 using different connections. Thanks in advance.  Using MyODBC its "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;"Using OleDbConnection its "Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;"  I just thought of asking if you probably are having trouble switching databases when connecting to your mysql db.Now, the good thing is MySQL provides a connector to asp.net, and you can download free of charge...

MySQL
Can I use MySQL as database or am I limited to Access and MSSQL? Hi, You can use database other than Access / MSSQL in ASP.NET, FYI: view post 249017 and view forum 56 Colt...

mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib service mysql start Starting service MySQL warning: /var/lib/mysql/mysql.sock didn't appear within 3 need help thanks -- ansonelliott ------------------------------------------------------------------------ Have you ensured both mysqld and apache are running? YaST > System > System services (if I remember correctly) allows you to set both to start automatically on boot. -- john_hudson -----------------------------------------------------------------------...

MySQL
Anyone using PB with MySQL as their database? Just about everything is going fine except the current ODBC driver doesn't support cursors which we're having some difficulty getting around for one function of our program. Terrill ...

Web resources about - MySQL not returning correct ID 100% of the time - asp.net.mysql

Returning - Wikipedia, the free encyclopedia
In retail , returning is the process of a customer taking previously purchased merchandise back to the retailer, and in turn, receiving a cash ...

Mark Zuckerberg Returning To TechCrunch Disrupt
Facebook Co-Founder and CEO Mark Zuckerberg took the stage at last year’s TechCrunch Disrupt conference in San Francisco, where he discussed ...

How-to: Improve the Experience for Returning Users
... – your inbox, your favorite news sources, or your friends’ activity. If you’ve integrated Facebook Login on your website, you can give returning ...

Search Twitter - emilia-clarke-not-returning
Sign in Sign up Search Refresh K. @ kikeurbina 1h Muy malas noticias: 'GAME OF THRONES' STAR EMILIA CLARKE (DAENERYS) NOT RETURNING FOR SEASON ...

Is the Returning CEO a Trend?
... well be wondering if it is a new trend for boards to reach back into their alumni ranks to bring back a CEO when a company is in Is the Returning ...

Returning fire - Flickr - Photo Sharing!
A U.S. Army soldier with the 101st Airborne Division returns fire with a M249 light machine gun during combat operations in the valley of Barawala ...

Marines Celebrate Returning Home With a Huge Pillow Fight Aboard the Airplane - YouTube
Visit http://WelcomeHomeBlog.com for more surprise military homecomings! In this clip, a group of marines celebrate their safe return home from ...

WWE’s Daniel Bryan on WWE 2K16, facing Brock Lesnar at WrestleMania, returning to the ring and life on ...
The four-time WWE world champion discusses whether it is right for Stone Cold Steve Austin to be the cover star of WWE 2K16, as well as who he ...

Damn Fine News of the Day: Kyle MacLachlan is Returning to ‘Twin Peaks’
In case you missed it, David Lynch announced on Twitter Monday that Kyle MacLachlan will reprise his role as Special Agent Dale Cooper in the ...

Injury not enough to keep official caught in crossfire from returning to action
The NRL match official caught in the crossfire of angry Bulldogs fans says he has no intention of stepping down from his role on the sidelines. ...

Resources last updated: 12/10/2015 7:48:24 AM