locked
Get latest added id using Dapper - Insert query RRS feed

  • Question

  • User-914804179 posted

    Hey,

    was wondering if it is possible to get my last added ID when I am using an Insert Query in OleDB (Microsoft Access DB).
    This is my code:

            public void AddCar(CarInfo car)
            {
                using (OleDbConnection connection = new OleDbConnection(Helper.CnnVal("CarDB")))
                {
                    DynamicParameters pars = new DynamicParameters();
                    pars.Add("@Manufacturer", car.Manufacturer, DbType.String);
                    pars.Add("@CarName", car.CarName, DbType.String);
                    pars.Add("@CarYear", car.CarYear, DbType.Int32);
                    pars.Add("@CarNumber", car.CarNumber, DbType.Int32);
                    pars.Add("@CarModule", car.CarModule, DbType.String);
                    pars.Add("@CarLicenseExpirationDate", car.CarLicenseExpirationDate, DbType.DateTime);
                    pars.Add("@Weight", car.Weight, DbType.Int32);
                    pars.Add("@FrontTire", car.FrontTire, DbType.String);
                    pars.Add("@RearTire", car.RearTire, DbType.String);
                    pars.Add("@FrontTirePressure", car.FrontTirePressure, DbType.Double);
                    pars.Add("@RearTirePressure", car.RearTirePressure, DbType.Double);
                    connection.Execute("AddCar", pars, commandType: CommandType.StoredProcedure);
                }
            }

    and this is the Query named AddCar:

    INSERT INTO CarInfo ( Manufacturer, CarName, CarYear, CarNumber, CarModule, CarLicenseExpirationDate, Weight, FrontTire, RearTire, FrontTirePressure, RearTirePressure )
    SELECT [@Manufacturer] AS Expr1, [@CarName] AS Expr2, [@CarYear] AS Expr3, [@CarNumber] AS Expr4, [@CarModule] AS Expr5, [@CarLicenseExpirationDate] AS Expr6, [@Weight] AS Expr7, [@FrontTire] AS Expr8, [@RearTire] AS Expr9, [@FrontTirePressure] AS Expr10, [@RearTirePressure] AS Expr11;
    

    The CarInfo table has one more additional coloumn which is CarID (auto generated number), I want to get the new inserted row CarID - is it possible without using another query?

    Thanks for any help!

    Wednesday, January 27, 2021 4:17 PM

Answers

All replies

  • User-821857111 posted

    Access doesn't support batch queries. You need to execute two separate commands on the same connection and use @@identity to get the new identity value: https://www.mikesdotnetting.com/article/54/getting-the-identity-of-the-most-recently-added-record

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 27, 2021 7:18 PM
  • User1535942433 posted

    Hi Adir987,

    The CarInfo table has one more additional coloumn which is CarID (auto generated number), I want to get the new inserted row CarID - is it possible without using another query?

    As far as I think,it's impossible to get all columns of the lasted id. You could use LAST_INSERT_ID(); 

    Just like this:

    declare @returnId int;
    Insert into Test(num,Total) values('fsdfsds',12)
    SELECT LAST_INSERT_ID();
    SELECT [@Manufacturer] AS Expr1, [@CarName] AS Expr2, [@CarYear] AS Expr3, [@CarNumber] AS Expr4, [@CarModule] AS Expr5, [@CarLicenseExpirationDate] AS Expr6, [@Weight] AS Expr7, [@FrontTire] AS Expr8, [@RearTire] AS Expr9, [@FrontTirePressure] AS Expr10, [@RearTirePressure] AS Expr11 where @returnId=(Select LAST_INSERT_ID());

    Best regards,

    Yijing Sun

    Thursday, January 28, 2021 3:07 AM
  • User-821857111 posted

    You could use LAST_INSERT_ID(); 
    That's not available in Access. It's from MySQL.

    Thursday, January 28, 2021 6:28 AM
  • User-914804179 posted

    Understood, thank you!

    Friday, January 29, 2021 3:28 PM