locked
How do i INSERT the new record ID from table#1 into table#2 in the same process? RRS feed

  • Question

  • User-250910258 posted

    Hi, this is my problem (I can do this with Coldfusion but not in asp.net)… I have two tables “memberAccount” and “memberProfile”.

    1. New member information is INSERTED into memberAccount whenever a new member is created…
    2. I would like to GREB the new member’s MemID and add it to the memberProfile table at the same time the new memberAccount is created.  How do I do that in asp.net Razor C# syntax?  Thanks.

    Basically, How do i INSERT the new record ID from table#1 into table#2 in the same process (INSERT, SELECT & UPDATE)? Thanks.

    Saturday, August 24, 2013 12:23 PM

Answers

  • User-821857111 posted

    If you are using the Database helper for your data access, you can use the GetLastInsertId method

    var sql = "INSERT INTO MyTable (col1, col2, etc) VALUES (@0, @1)";
    db.Execute(sql, Request["FirstName"], Request["LastName"]);
    var id = (int)db.GetLastInsertId();

    You should maybe consider using the WebSecurity helper for your authentication and membership. It does all this for you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 24, 2013 1:19 PM

All replies

  • User-760709272 posted

    How are you inserting your data?  If you're doing stored procedures, or even direct SQL, then you get the last ID using scope_identity()

    set nocount on; insert into table values (...); select SCOPE_IDENTITY() as NewID

    You can execute that and treat it like something that returns a single result (execuescalar if you are using ado.net) and it returns the id of the last-inserted table.  You can use that in your next insert.

    Saturday, August 24, 2013 12:27 PM
  • User-250910258 posted

    AidyF, thanks for the reply.  I am very new to asp.net, did Coldfusion many years "10" ago.

    So basically, i have a FORM that potential members can submit... As soon as the account is created i would like to get that memID and add it to the memberProfile table.

    Therefore, how do i select the last entry into a table?  This is what i did back in the day, to grab the last entry, when i used coldfusion.

    *************************

    <cfquery name="GetID" datasource="BCGinfo" dbtype="ODBC" maxrows="1">
    SELECT AnonymousInfoID
    FROM AnonymousInfo
    ORDER BY AnonymousInfoID Desc

    *************************

    thanks.

    Saturday, August 24, 2013 12:40 PM
  • User-760709272 posted

    You can execute your sql directly using ado.net and the SqlCommand object

    http://msdn.microsoft.com/en-us/library/tyy0sz6b.aspx

    You could put your logic into a single stored procedure and execute that via ado.net

    http://csharp-station.com/Tutorial/AdoDotNet/Lesson07

    You could not bother with writing your own sql at all and use linq to sql which lets you treat your tables like basic c# objects.

    http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

    It all depends on how you decide to do your data access.  There are other options like Entity Framework too.

    Saturday, August 24, 2013 12:49 PM
  • User-250910258 posted

    Thanks aidyF, i will take a look at these suggestions.

    Saturday, August 24, 2013 12:55 PM
  • User-821857111 posted

    If you are using the Database helper for your data access, you can use the GetLastInsertId method

    var sql = "INSERT INTO MyTable (col1, col2, etc) VALUES (@0, @1)";
    db.Execute(sql, Request["FirstName"], Request["LastName"]);
    var id = (int)db.GetLastInsertId();

    You should maybe consider using the WebSecurity helper for your authentication and membership. It does all this for you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 24, 2013 1:19 PM
  • User-250910258 posted

    Mikesdotnett... thanks for the info.  Please look at this, THIS is the what i have so far.  I think i need help in area #2 below.  Thanks again.

    *****************************************

     // 1. Insert new member data into Account table 
         var db = Database.Open("AutosDB");
                db.Execute("INSERT INTO AccountInfo (Created, FirstName, lastName, Phone, Email) VALUES (@0, @1, @2, @3, @4)", Account_DateTime, Account_FirstName, Account_LastName, Account_Phone, Account_Email);
     
        // 2. Get the new member ID that was JUST created from Account table (i know this is wrong, what must i do???... so, How do i get the last ID that was just creatd?)
        var selectQueryString = "SELECT * FROM AccountInfo ORDER BY memID Desc maxrows=1";
     
        // 3. Insert new member ID into Profile table
        db.Execute("INSERT INTO ProfileInfo (Created, memID) VALUES (@0, @1)", Profile_DateTime, memID);

    *****************************************

    Thanks Mikesdotnett...  "WebSecurity helper" that was my next step... i thought i would do this first before getting into more stuff i had not done before (i know i will have to incorporate the asp.net WebSecurity helper with my member sign up).

    Saturday, August 24, 2013 2:13 PM
  • User-250910258 posted

    Hello All, i would like to thank AidyF and Mikesdotnett... for their replies....  Mikesdotnett your suggestion worked, thanks again.  i just tried it.  You guys have been helpful.

    Saturday, August 24, 2013 2:43 PM