Problem with bindingNavigator of two tables. Please help....

yeeboon

New member
Joined
Oct 1, 2006
Messages
1
Programming Experience
Beginner
Hi,


My application bind to a database which have 2 table, man(manId, name, age) and employment(employId, manId, salary). manId in employment table is foreign key of man table.
I set the relationship of these two tables in MS Access. So after I add these two tables into my dataset, it automatically creates a “one-to-many” relation.

After I create a form, I drag the man table in dataset as ‘details’ onto my form, it create dataSet, manBindingSource, manTableAdapter, ManBindingNavigator. When I debug the application now, it works fine, can navigate, add, delete and update.
After that, I try to drag the employment table under manTable as “DataGridView” onto my form, it all work fine. All the fields from man and employment table can be ‘navigate’,’add’, ‘delete’ and ‘update’
Then I drag the employment table under the manTable as ‘details’ onto my form, it create EmploymentBindingSource and EmploymentTableAdapter.
Then I try to debug my application, I can navigate, add, delete and update those fields from my man table, but when I want to add data into employment table, it failed, for example, after fiiling all the details of man table, I fill in the ‘salary’ field of employment table, then I click ‘save’ button(I didn’t fill the employId and manId cause employId is auto-number and manId is foreign key of man table), all the three fields of employment table become blank. It didn’t save into my database, yet all the data in the man fields successfully save into my database.
I had spent more than a week to solve this problem, but still cannot.
smile_sniff.gif
:(:(

I need to add those fields from table employment onto my form as ‘details’.
Can some one please help me?
Million thanks in advance.


Best regards
Boon.



p/s: I had already add code in the ‘save’ button of bindingNavigator so that it could save data to a database from multiple Tables. ( http://msdn2.microsoft.com/en-us/library/4esb49b4.aspx )
 
wouldnt mind betting youre running into a similar issue as i had for a LONG time and still with no nice resolution.

see this thread:
http://www.vbdotnetforums.com/showthread.php?t=13058


What I do in my forms that have related data, in my form's "add new record" button(s) i ALWAYS call EndEdit() after adding a new row (and ensuring that constraints are satisfied)

Read the thread, and if youre still at a loss as to what to do, return here and ask me.
 
Hi

Im using the binding navigator to show me details of one table 'Notes'. The notes table contains a companyId and a ActivityId.

Each note must be associated with a company, and can be assocaited with an activity of that company. Otherwise the note is just for the comapny rather than for this activity of this comapny.

So my table adapter only read from one table.

Where do I add the companyId and ActivityId into the dataset? Ive just read the add method creates a datarow but doesnt add it back into the dataset until endedit is called. How do I get hold of the datarow to add my foreigns keys?

Thanks a lot.

Darren
 
BindingNavigators dont show details, they alter the current position that is maintained by a BindingSource. A BindingSource shows you some representation of data from the underlying DataTable.

Are you creating a self-referential constraint?
 
the database has the referencial link between activity.activityId and notes.activityId. However Notes.ActivityId can be NULL.

Sorry about the terminology but did you understand what Im trying to say?

Thanks

Darren
 
One activity may have one or more notes:


VB.NET:
[FONT=Courier New]Activity                Notes[/FONT]
[FONT=Courier New]--------                -----[/FONT]
[FONT=Courier New]activityID  1--------M  activityID[/FONT]
[FONT=Courier New]
[/FONT]

?
 
Yeah correct.

A note also has CompanyId which relates to Company.CompanyId. A note must be part of the company but can be just for the company or also for the activity.
 
When the 'New' button on the binding navigator is pressed i thought a new record is created in the dataset. Which is then populated by the values typed into the fields.

But when I press the 'New' button the datatable doesnt get a new row added. So when Save is pressed and the code gets to the line me.NotesBindingSource.EndEdit() it errors because CompanyId (foreign key) cannot be null.

How do I fill the companyId value and how do I create a new datarow?
 
When the 'New' button on the binding navigator is pressed i thought a new record is created in the dataset. Which is then populated by the values typed into the fields.

Not quite, though it is seldom documented.

DataSets dont hold records - datatables hold records, and datasets hold datatables. In a bound situation hierarchy you have:

Control
|
BindingSource -- BindingNavigator
|
DataTable
|
DataSet


But when I press the 'New' button the datatable doesnt get a new row added.
Correct. When you press New, BindingSource.AddNew() is called
This adds a new DataRowView to the BindingSource and the .Current property of the BindingSource is pointing to the new DataRowView. This DataRowView has as its underlying base, a DataRow object with a state of Detached.
At this point the Detached row is not added to the datatable.

When BindingSource.EndEdit() is called the detached row is put into the datatable. EndEdit can be called implicitly, through a move or another addnew or explicitly. At this point of addition ot the datatable, contrsint validation in the DataSet occurs

So when Save is pressed and the code gets to the line me.NotesBindingSource.EndEdit() it errors because CompanyId (foreign key) cannot be null.

How do I fill the companyId value and how do I create a new datarow?
It depends. In a relation, whether it is a cascading one or not, calling AddNew on the child causes the new child row to gain a related value equal to the parent's value.. So if the ParentBindingSource.Current property is pointing to a row with an ID of 123, then calling AddNew on the child will generate a detached row within the bindingsource of ID 123.

One problem you may encounter with this setup is that if the parent ID is null when you call AddNew on the child, then the child ID will be null.

There are other factors that make datarelations fail. Have a search for a thread I wrote here called "Calling EndEdit causes data relations to fail"
It seems that some people routinely get this problem. I now do not use BindingNavigator's default Add button, but code my own in relational scenarios because it gets the operation wrong routinely.

I cannot help more without seeing the code itself, but I reckon I'd have a pretty good idea of what was going on. The golden rules I follow now are:

Set defaulting values for a row first
Call ParentBindingSource.AddNew myself
Do any post-new setup of the row that cannot be done ahead of time
Call ParentBindingSource.EndEdit()
THEN, call ChildBindingSource.AddNew()


It is crucial that hte last steps are done in order, otherwise youll end up a very confused puppy (like i was) because as soon as you call EndEdit on one bindingsource, it destroys any detached rows in any related bindingsources.. Very confusing!
 
Back
Top