How to Get Last Created Entry's ID From Sql Database With Asp.Net

Question

I will explain problem with an example:

There is two table in my database, named entry, tags

There is a column named ID_ENTRY in both table. When I add a record to table, entry, I have to take the ID_ENTRY of last added record and add it to table, tags. How can I do it?

Answer 1

I would probably do this with an INSERT trigger on the named entry table, if you have all of the data you need to push to the tags table available. If not, then you might want to consider using a stored procedure that creates both inside a transaction.

If you want to do it in code, you'll need to be more specific about how you are managing your data. Are you using DataAdapter, DataTables, LINQ, NHibernate, ...? Essentially, you need to wrap both inserts inside a transaction of some sort so that either inserts get executed or neither do, but the means to doing that depend on what technology you are using to interact with the database.

Answer 2

Thanks for deal, but I have never used stored procedure or trigger. I use dynamic sql in my projects and if there is a way to do it with dynamic sql, it will be better.

Answer 3

The only way to do this is with multiple statements. Using dynamic sql you can do this by separating each statement in your query string with a semi-colon:

"DECLARE @ID int;INSERT INTO [Entry] (...) VALUES ...; SELECT @ID = scope_identity();INSERT INTO [TAGS] (ID_ENTRY) VALUES (@ID);"

Make sure you put this in a transaction to protect against concurrency problems and keep it all atomic. You could also break that up into two separate queries to return the new ID value in the middle if you want; just make sure both queries are in the same transaction.

Also: you are using parameterized queries with your dynamic sql, right? If you're not, I'll personally come over there and smack you 10,000 times with a wet noodle until you repent of your insecure ways.

Answer 4

Immediatly after executing the insert statement on first table, you should query @@IDENTITY doing "SELECT @@identity". That will retrieve the last autogenerated ID... and then just insert it on the second table.

If you are using triggers or something that inserts rows... this may be not work. Use Scope_Identity() instead of @@IDENTITY

Answer 5

If you use dynamic sql, why not use Linq to Entity Framework, now EF is the recommend data access technology from Microsoft (see this post Clarifying the message on L2S Futures from ADO.NET team blog), and if you do an insert with EF the last identity id will available for you automatically, I use it all the time it's easy.

Hope this helps!

Ray.

Answer 6

Thanks for your deal, but I'm living in Turkey and here there is no Server, which support framework 3.5 yet.

Answer 7

Hi Everyone, I have the same problem.

First, I to insert the data into First table and read the autogenereated ID value from that table and insert into second. I have more tables.

here is my sql code:

string sql = " DECLARE @ID int;DECLARE @ID1 int;DECLARE @ID3 int;DECLARE @ID4 int;DECLARE @ID5 int;DECLARE @ID6 int;"; sql = sql + "Insert into dbo.Records(Url,SourceTitleBar,SourcePageHead,RelatedRecords,Addi_Keywords,Description,IPHI_Title_Bar,IPHI_Page_Head) values "; sql = sql + "(@url,@sourceTitle,@sourcePage,@related,@additional,@description,@titleBar,@pageHead);"; sql = sql + " SET @ID = SCOPE_IDENTITY();"; sql = sql + " Insert into dbo.Categories(Cate_Name) values(@categoryName);"; sql = sql + " SET @ID1 = SCOPE_IDENTITY();"; sql = sql + " Insert into dbo.Subcategories(Sub_Cate_Name) values(@SubcategoryName);"; sql = sql + " SET @ID3 = SCOPE_IDENTITY();"; sql = sql + " Insert into dbo.Scope(Scope_Name) values(@scope);"; sql = sql + " SET @ID4 = SCOPE_IDENTITY();"; sql = sql + " Insert into dbo.Lead_Health_Indicator(Health_Indicator_Name) values(@indicator);"; sql = sql + " SET @ID5 = SCOPE_IDENTITY();"; sql = sql + " Insert into dbo.Category_Subcategory(Cate_ID,Sub_Cate_ID) values (@ID1,@ID3);"; sql = sql + " SET @ID6 = SCOPE_IDENTITY();"; sql = sql + " INSERT INTO dbo.Record_Category(RecordID,Cate_Subcate_ID) VALUES (@ID,@ID6);"; sql = sql + " Insert into dbo.Record_Scope(RecordID,Scope_ID) values (@ID,@ID4);"; sql = sql + " Insert into dbo.Record_HealthIndicator(RecordID,Health_Indicator_ID) values(@ID,@ID5);";

    SqlConnection conn = new SqlConnection(GetConnectionString());
    conn.Open();
    IDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = sql;



    IDbDataParameter userParam12 = cmd.CreateParameter();
    userParam12.ParameterName = "@url";
    userParam12.Value = iphi.URL;
    userParam12.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam12);

    IDbDataParameter userParam2 = cmd.CreateParameter();
    userParam2.ParameterName = "@sourceTitle";
    userParam2.Value = iphi.SourceTitleBar;
    userParam2.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam2);

    IDbDataParameter userParam20 = cmd.CreateParameter();
    userParam20.ParameterName = "@sourcePage";
    userParam20.Value = iphi.SourcePageHead;
    userParam20.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam20);

    IDbDataParameter userParam3 = cmd.CreateParameter();
    userParam3.ParameterName = "@related";
    userParam3.Value = iphi.RelatedRecords;
    userParam3.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam3);

    IDbDataParameter userParam4 = cmd.CreateParameter();
    userParam4.ParameterName = "@additional";
    userParam4.Value = iphi.AdditionalKeywords;
    userParam4.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam4);

    IDbDataParameter userParam5 = cmd.CreateParameter();
    userParam5.ParameterName = "@description";
    userParam5.Value = iphi.Description;
    userParam5.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam5);

    IDbDataParameter userParam6 = cmd.CreateParameter();
    userParam6.ParameterName = "@titleBar";
    userParam6.Value = iphi.IPHITitleBar;
    userParam6.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam6);

    IDbDataParameter userParam7 = cmd.CreateParameter();
    userParam7.ParameterName = "@pageHead";
    userParam7.Value = iphi.IPHIPageHead;
    userParam7.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam7);


    IDbDataParameter userParam121 = cmd.CreateParameter();
    userParam121.ParameterName = "@categoryName";
    userParam121.Value = iphi.Categories;
    userParam121.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam121);

    IDbDataParameter userParam122 = cmd.CreateParameter();
    userParam12.ParameterName = "@SubcategoryName";
    userParam122.Value = iphi.SubCategories;
    userParam122.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam122);

    IDbDataParameter userParam123 = cmd.CreateParameter();
    userParam123.ParameterName = "@scope";
    userParam123.Value = iphi.Scope;
    userParam123.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam123);


    IDbDataParameter userParam124 = cmd.CreateParameter();
    userParam124.ParameterName = "@indicator";
    userParam124.Value = iphi.Indicator;
    userParam124.DbType = System.Data.DbType.String;
    cmd.Parameters.Add(userParam124);




    cmd.ExecuteNonQuery();

It tells me the the following error:

Must define the scalar value at "@url"

Please help me, any help will be appreciated.

Thanks -Raj