Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2007-03-19
Medium Priority
Last Modified: 2013-11-27
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?

Question by:gwagnr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
LVL 10

Expert Comment

ID: 18754056
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?


Author Comment

ID: 18756148
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?
LVL 41

Expert Comment

ID: 18758561
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!

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 18758737
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.
LVL 41

Expert Comment

ID: 18758874
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....

Author Comment

ID: 18759068
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?
LVL 41

Expert Comment

ID: 18759252
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)

Author Comment

ID: 18761530
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?
LVL 10

Expert Comment

ID: 18762018
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
LVL 41

Accepted Solution

graye earned 2000 total points
ID: 18763260
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.  :)

Author Comment

ID: 18767747
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!

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question