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?
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
3 Points
6 Posts
Get latest added id using Dapper - Insert query
Jan 27, 2021 04:17 PM|Adir987|LINK
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:
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!
All-Star
194496 Points
28081 Posts
Moderator
Re: Get latest added id using Dapper - Insert query
Jan 27, 2021 07:18 PM|Mikesdotnetting|LINK
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
Contributor
3730 Points
1427 Posts
Re: Get latest added id using Dapper - Insert query
Jan 28, 2021 03:07 AM|yij sun|LINK
Hi Adir987,
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
All-Star
194496 Points
28081 Posts
Moderator
Re: Get latest added id using Dapper - Insert query
Jan 28, 2021 06:28 AM|Mikesdotnetting|LINK
Member
3 Points
6 Posts
Re: Get latest added id using Dapper - Insert query
Jan 29, 2021 03:28 PM|Adir987|LINK
Understood, thank you!