CODEWITHSUNDEEP
c#mysql
Manoj Patil
Manoj Patil

Reputation: 19

Get Autoincrement value after INSERT query in MySQL

using (OdbcConnection con = new OdbcConnection(ConnStr))
using (OdbcCommand cmd = new OdbcCommand("INSERT INTO tblUsers(FirstName, LastName, UserName, Password, EmailId, Created_Date, typeid) VALUES ('" + ObjUserProp.FirstName + "','" + ObjUserProp.LastName + "','" + ObjUserProp.UserName + "','" + ObjUserProp.Password + "','" + ObjUserProp.EmailId + "','" + ObjUserProp.Created_Date + "'," + ObjUserProp.TypeId + ")", con))
{

    con.Open();

    using (OdbcCommand cmd1 = new OdbcCommand("INSERT INTO tblUsersRelation(UserId,usertypeid) VALUES ( LAST_INSERT_ID() ," + ObjUserProp.TypeId + ")", con))
    {
        IsDone = cmd.ExecuteNonQuery();
        return IsDone;
    }
}

Record is inserted only in 1 table. Please tell me how to take latest autoincrement ID value.

Upvotes: 1

Views: 6218

Answers (5)

Tim Meers
Tim Meers

Reputation: 928

You need to use the LAST_INSERT_ID() along with ExecuteScalar which will return the record. Using ExecuteNonQuery will not return anything.

Aside from your question you also should be using parameterized queries.

Upvotes: 0

Lloyd
Lloyd

Reputation: 2942

You are far better off using stored procedures, "Routines" as they are referred to in MySql, and passing in input parameters.

Upvotes: 2

Chris
Chris

Reputation: 27609

This looks like what you want - SELECT LAST_INSERT_ID() (from http://dev.mysql.com/doc/refman/5.5/en/getting-unique-id.html).

In essence I'd change the first sql statement to call that after the insert. Then you can pick up the returned value and use it in subsequent sql queries.

That having been said it should work the way you have done it. Though of course I am assuming that you are in fact missing the cmd1.ExecuteNonQuery() line. If you really don't have that line then that will be why the second table isn't bein updated.

Upvotes: 1

MC Emperor
MC Emperor

Reputation: 22987

You can use the MySQL function LAST_INSERT_ID() to retrieve the last inserted id generated by AUTO_INCREMENT.

Upvotes: 0

Zohaib
Zohaib

Reputation: 7116

select MAX(id)
from tblUsers

I dont have idea of C#, but I guess this is what u r looking for...

Upvotes: 0

Related Questions