I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
If you're passing all of the information into your Stored Procedure, then can't you simply retrieve the last ID inserted via SCOPE_IDENTITY? This assumes your using an identity column within tblPerson.
|||Thanks for your reply. I'm not familiar with this command because I'm from a MySQL background. So I essentially I use the following
INSERT INTO tblPerson (name, username, password) VALUES (@.name, @.username, @.password);
INSERT INTO tblAddress (FK_tblPerson, address1, address2) VALUES (scope_identity(),@.address1, @.address2);
|||Yes, except that I'd declare a variable and place the results of SCOPE_IDENTITY into it. Then I'd use that variable for my next INSERT.
