Link to home
Start Free TrialLog in
Avatar of gwagnr

asked on

Visual Basic .NET 2005/SQL Server 2005: simple relational linking btw two tables

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?

Avatar of StephenCairns

first I'd say use you disconneted dataset to view the data only
insert any new records directly into the tables

1. you have an existing company? then populate your companylink field as you add the employee
if you are adding a new company and employee at thesame time - dont - add the company returning the id with scope_identity then use this value for your employees.

2 if you insert the data directly into the table rather than a disconnected dataset then there will be no problems with collisions.  

any help?

Avatar of gwagnr


I am new to VB.NET and was under the impression that a disconnected dataset was my only option... How do I update the table directly?
Let's start at the beginning...

I assume you've got a control of some sort (like a DataGridView) on the Company form that's showing the list of companies.   An easy way to select a company would be to select the row in the DataGridView.   Then the user would push a button.   The button will look for the currently selected row in the DataGridView (if there is any) and pick out the CompanyID

Next you'd create a second form? that lists all of the Employees with that CompanyID.   You'd do this by creating an SQL query inside your program to query the database.  You'd display the results of this query onto another control (perhaps another DataGridView).

The user could then add/delete/modify the items in the DataGridView using just the DataGridView control.   You'd probably have a "Save" button to commit the changes made by the user back to the database.

The only tricky part about this would be to assure that the changes to the entries in Employee table didn't "violate referential integrity" (meaning that you didn't create a bogus CompanyLink field that didn't match the CompanyID filed in the Company table.

There are ways to do with using a concept called DataRelations, but I can tell that's probably not where you'd want to go ;)

If all of this is making sense, then let us know, and we can help you with examples on each part of this process.   Otherwise, keep asking questions!
Avatar of gwagnr


You are definitely making sense...  the way I have implemented this so far is with a company form with all the fields (not a datagrid).  You can traverse all the companies by stepping thru the table.  When you want to view a given company's employees, you would click an employees button.  That button would open a form showing all the employee information for the first employee associated with that company.  You can traverse each employee the same way you traverse companies.  If you add a new employee, it should look at the ID value of the associated company, and add it to the companylink field in the employee record.

This is simply how I have implemented it to make it easy to test.  I am not against using a grid.  Just trying to take baby steps.  The majority of my experience is in the Access world.

As far as setting relationships, if I define it in SQL and/or in the dataset definition, do I still need to set it in the code?

Thanks for your help.  I am looking forward to your response.
I'd recommend we stay away from DataRelations until later...   Yes, it could actually help out a bit in this situation, but it adds a level of complexity that probably isn't justified until you're good and ready.

I actually find the DataGridView easier/faster to prototype than "descrete controls" (such as bound TextBoxes).  Are you using a BindingNavigator control (the VCR-style control) for moving between records?   If so you're already "in pretty deep"...   You can control the appearance of the "AddNewItem" and "DeleteIteM' thru the control's properties to allow for add/deleting the records in the Employee table

As for referential integrity problem, I'd recommend a simple approach of just catching the error message and telling the user to fix the problem and try again.   So, there's no need to replicate a relationship in code (er, well... I assume there *is* a relationship at the database).   That means the database itself will catch the error and we'll just deal with the error condition.

I've got a few articles that might be of interest....
Avatar of gwagnr


Yes, I am using the BindingNavigator control so far... not at the point of no return, however.  There are a lot of fields in the actual tables, so I felt this form of implementation made more sense.  I have been thinking about datagrid summaries with a click into the detailed form with all the fields.

I know my head is in the Access world, but it seems to me like this should be easy.  All I should have to do (before saving a new employee record) is to set the companylink field to the companyID value in the company form.  What am I missing?
That's it... you could trap the events coming from the DataTable itself to manually populate the correct item for that field

...but the simpiler approach is: At the "Save" button, just loop thru the rows in the DataTable and fill in the correct item for that field.   Since they all will be the same value, you can just blindly copy the CompanyID to that field.

Hey, let us know when you're ready to take on the more complex methods.  (I sill would recommend you take the time to read thru those articles)
Avatar of gwagnr


Definitely on the right track... I tried a few of the events on the DataTable... it worked if I added a record and saved each time, but did not if I added more than one before saving.  I like your save idea and will test that.  I will also need to write code to handle closing the form without saving (or force a save on close, etc.)

Do I need to write a stored procedure to get my identity value?  My CompanyID and EmployeeID fields are identity fields, but I am afraid in a multiuser environment there will be identity collisions when users enter data at the same time.  Does VB/SQL handle this and change the identities on replication, or is there something I need to do?
I've not used data adapters  (after looking at this Im felingvery happy about that)
spoted this
which might help with your avoiding collision in your identity fields
Avatar of graye
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gwagnr


I am sure I will have more to ask, but man-- I needed to get you some points.  I'll give it a shot and will be in touch.  Thanks!