OK... I changed the requirements on the app I am working on to (hopefully) make it easier. I know this shouldn't be difficult but I have been fighting this for over a week.
Platform: Visual Basic .NET 2005 Standard Edition, SQL Server 2005 Developer Edition
To simplify my actual issue, I have two tables: Company and Employee.
For each table, I have a form in VB using the binding control. The ID fields are identity fields incrementing by 1. All I want to do from the company form is press an employee button, see the employees currently linked to the selected company in the employee screen, and be able to add new employees to the employee screen, having them automatically link to the selected company. This should be easy. I am not sure what is the best way to do this as I run into the following issues:
1. I don't know what event in which to populate the CompanyLink field in. Addnew seems too early, others don't account for the disconnected nature of the dataset and only update one record, etc.
2. Since the dataset is disconnected, multiple users will create an identity that is the same (ie: if the database has two records and two are adding at the same time, each person's next record will be ID #3)
I am lost on #1, on #2, I am thinking I need a stored procedure to get the next identity value and pass it into my form... can anyone give me some advice or point me at some sample code?