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.