Last post Aug 24, 2013 02:43 PM by robuc33
Aug 24, 2013 12:23 PM|robuc33|LINK
Hi, this is my problem (I can do this with Coldfusion but not in asp.net)… I have two tables “memberAccount” and “memberProfile”.
Basically, How do i INSERT the new record ID from table#1 into table#2 in the same process (INSERT, SELECT & UPDATE)? Thanks.
Aug 24, 2013 12:27 PM|AidyF|LINK
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.
Aug 24, 2013 12:40 PM|robuc33|LINK
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">
ORDER BY AnonymousInfoID Desc
Aug 24, 2013 12:49 PM|AidyF|LINK
You can execute your sql directly using ado.net and the SqlCommand object
You could put your logic into a single stored procedure and execute that via ado.net
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.
It all depends on how you decide to do your data access. There are other options like Entity Framework too.
Aug 24, 2013 12:55 PM|robuc33|LINK
Thanks aidyF, i will take a look at these suggestions.
Aug 24, 2013 01:19 PM|Mikesdotnetting|LINK
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.
Aug 24, 2013 02:13 PM|robuc33|LINK
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).
Aug 24, 2013 02:43 PM|robuc33|LINK
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.