In mssql you have @@Identiy which can return the id of the row you added, anybody know if there is an equivalant in mysql?

 

 public int InsertContactList(objContactList list)
        {
            MySqlCommand cmd;
            conn.Open();
            cmd = new MySqlCommand("insert into contact_lists (ListName,UserID) values (?ListName,?UserID);Select LAST_INSERT_ID();", conn);
            
            cmd.Parameters.AddWithValue("?ListName", list.ListName);
            cmd.Parameters.AddWithValue("?UserID", list.UserID);
            int i = (int)(long)cmd.ExecuteScalar();
            conn.Close();
            return i;
        }

Two other differences that stumped me for a while were that mysql doesn't have GetDate() as an sql function so i had to swap that to Now(), and the one I got stuck on the longest was when you use parameterized queries, mssql variables are named like @Variable but in mysql it's ?Variable. It gave me no error messages when i used @Variable it just didn't work, took me f**king forever to work that out .

 

scope_identity() is the last inserted id for your scope (query, stored proc, trigger)

@@identity is last inserted id for the database for your connection

Or in other words - on busy systems with heavy inserting going on, @@identity can return the wrong id

hmm from what I read it is for the current connection which i assume means between con.open and con.close.

Sound right to you?

 

That being said though, any sort of trigger will definitely live inside your connection's scope, so if one of those does an insert somewhere on an identity field because of something you did, you're in trouble. Moral of the story - use scope_identity(), there's no reason not to