Unanswered: Get the last inserted row id from a table and update it in another table
I am not sure weather if i am in the right track. First of all i am using DreamweaverMX and the Application server is ASP with MS Sql2000 as the DB.
I am not a developer but i can fiddle around and done some lite programming in Php as well as in Asp with the aid of Dreamweaver.
Right now i am stuck with a situation dont know how to proceed, wondering weather if it is the right approach.
I developing an application that has about 20 tables, some of them are so called lookup tables that you enter the information first in order to relate that to another table and so on.
The trouble is i've done very well in inserting and updating deleting etc. Now i am stuck wondering how to go about.
I have User_table, and a transaction_table; what i wish to do is when a new user is created which is done by inserting a new row in the User_table, i need at the same time pick that new row's user_id which is auto_increment that was just created and insert it in the field situated in the transaction_table (user_id as FK) and so on. I just realized that i will be needing this technique for other situation in the other tables.
I also realized that the id should be the one just inserted and not any body else's id that was inserted just after me.
Looking through different forums i found solutions like @@IDENTITY, select max (user_id) through procedure etc but unfortunately i am stuck so as to how i'll implement these solution.
I'd appreciate if some one could help me guide through the procedure or some solution or example where i could take a look or even any tutorial etc. i am opened to all proposition.