Hi, this is my problem (I can do this with Coldfusion but not in asp.net)… I have two tables “memberAccount” and “memberProfile”.
New member information is INSERTED into memberAccount whenever a new member is created…
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.
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.
I'm afraid I no longer use this forum due to the new point allocation system.
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
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.
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 vardb=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?)varselectQueryString="SELECT * FROM AccountInfo ORDER BY memID Desc maxrows=1";// 3. Insert new member ID into Profile tabledb.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).
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.
Member
29 Points
88 Posts
How do i INSERT the new record ID from table#1 into table#2 in the same process?
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.
All-Star
37441 Points
9076 Posts
Re: How do i INSERT the new record ID from table#1 into table#2 in the same process?
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.
Member
29 Points
88 Posts
Re: How do i INSERT the new record ID from table#1 into table#2 in the same process?
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">
SELECT AnonymousInfoID
FROM AnonymousInfo
ORDER BY AnonymousInfoID Desc
*************************
thanks.
All-Star
37441 Points
9076 Posts
Re: How do i INSERT the new record ID from table#1 into table#2 in the same process?
Aug 24, 2013 12:49 PM|AidyF|LINK
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.
Member
29 Points
88 Posts
Re: How do i INSERT the new record ID from table#1 into table#2 in the same process?
Aug 24, 2013 12:55 PM|robuc33|LINK
Thanks aidyF, i will take a look at these suggestions.
All-Star
194847 Points
28099 Posts
Moderator
Re: How do i INSERT the new record ID from table#1 into table#2 in the same process?
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
You should maybe consider using the WebSecurity helper for your authentication and membership. It does all this for you.
Member
29 Points
88 Posts
Re: How do i INSERT the new record ID from table#1 into table#2 in the same process?
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.
*****************************************
*****************************************
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).
Member
29 Points
88 Posts
Re: How do i INSERT the new record ID from table#1 into table#2 in the same process?
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.