Solved

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

Posted on 2007-03-19
11
285 Views
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.

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!
0
Comment
Question by:gwagnr
  • 5
  • 4
  • 2
11 Comments
 
LVL 10

Expert Comment

by:StephenCairns
Comment Utility
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
 

Author Comment

by:gwagnr
Comment Utility
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
 
LVL 41

Expert Comment

by:graye
Comment Utility
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
 

Author Comment

by:gwagnr
Comment Utility
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
 
LVL 41

Expert Comment

by:graye
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:gwagnr
Comment Utility
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
 
LVL 41

Expert Comment

by:graye
Comment Utility
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
 

Author Comment

by:gwagnr
Comment Utility
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
 
LVL 10

Expert Comment

by:StephenCairns
Comment Utility
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
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
Comment Utility
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
 

Author Comment

by:gwagnr
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now