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.

Company
-----------
CompanyID
Name

Employee
-----------
EmployeeID
CompanyLink
EmployeeName

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?

Thanks!
gwagnrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenCairnsCommented:
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?

 
0
gwagnrAuthor Commented:
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?
0
grayeCommented:
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!
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gwagnrAuthor Commented:
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.
0
grayeCommented:
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....

http://home.hot.rr.com/graye/Articles/BeginningADO.htm
http://home.hot.rr.com/graye/Articles/SavingDataADO.htm
http://home.hot.rr.com/graye/Articles/DataRelations.htm
0
gwagnrAuthor Commented:
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?
0
grayeCommented:
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)
0
gwagnrAuthor Commented:
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?
0
StephenCairnsCommented:
I've not used data adapters  (after looking at this Im felingvery happy about that)
spoted this
http://support.microsoft.com/kb/320301
which might help with your avoiding collision in your identity fields
cheers
0
grayeCommented:
Actually ADO.Net does a good job of handling the identity values for you... without having to write any code.  For example, the DataAdapter's Update method will submit the SQL Insert command with the identity field missing (knowing that the underlying database will fill that in).

The problem comes later, if after you've inserted some new rows, you need to use the identity field elsewhere in your program.   In those cases, you could write a stored procedure (or just execute a query) that returns the "last identity" value provided by the database.   On the other hand, it might be easier to just execute another DataAdapter Fill method to "refresh" the data.   That accomplishes the same thing.

Recall that the Update method will scan the rows/columns of your locally cached DataTable for any changes.  Then it submits the appropriate INSERT, DELETE, MODIFY commands for all rows where there are changes.   This technique is exactly what you're after.... you make changes, deletions, additions to the rows (by means of the controls on your form) and then the Update method will submit all of the changes back to the database.   So, this should work for multiple changes.

You might have detected a common thread here.... start simple, get it working, then later explore the more exotic methods of accomplishing the same thing.   Moving from DAO/ADO to the newer ADO.Net takes a bit of getting used to.   Nobody made the transitition in just a few days.

In fact, I fully expect to see you here again in a few weeks asking about some of those exotic methods.  :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gwagnrAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.