Passing auto incremented id through multiple forms

Need advice on what is the best approach to get the auto incremented id to be the same on multiple forms.

Basically I am using VB .Net with S开发者_C百科QL Server as the backend. I wrote a few stored procedures to insert data and then I am calling the stored procedures on the front end. I have 3 forms and once the first form is submitted it generates an autoID and then takes the user to 2nd form.

In the second form I have a separate insert stored procedure is there a way to grab the id that the first form generated and for it to be consistent. Or should I store it in a variable after they submit the form and then call it in the second form.

I read about the @@Identity but that only grabs the id within that session since I am submitting the first forms data then going to the second wouldn't that be a different session.

Thanks

---

Your stored proc could return the identity value (use the output clause or scope_identity() to get it) as an output variable that you would store as a variable in your application, then it would be available to use. You would want to make sure to null out the varaible when the person moves to a differnt record and after allthe associated formas have inserted records, so that you don;t inadvertently use it at some other time.

You do not want to ever use @@identity for this as it is unreliable and can create massive data integrity issues as it will give you the wrong value if someone adds a trigger that inserts to another table with an identity.

---

The fact that this is causing you problems might be an indicator that this isn't the best way to do it. Perhaps instead, use a function on the DB to return the ID, and then set it in your .net code?

If you have an auto incremented ID, then it should only increment when a record is created. Are your three forms creating three records? Is that what you want? If so, then an auto ID is definitely not the right way to go.

---

I think maybe your over thinking this?

I read about the @@Identity but that only grabs the id within that session since I am submitting the first forms data then going to the second wouldn't that be a different session.

Don't you want the key that was generated by the entries made on the first form?

In the second form I have a separate insert stored procedure is there a way to grab the id that the first form generated and for it to be consistent. Or should I store it in a variable after they submit the form and then call it in the second form.