Foreign Key Combobox

[HeavenCore]

Hello

Hopefully a quick question, i have a complex relational database, with many tables. tblJobs in this instance has two Foreign Key Attributes linking to the id's of tblQuotes and tblContracts.

I create a form and data source for this table adding in the three tables, i change the Foreign Key fields to drop down boxes and drag it onto my form thus creating all the text boxes and data bindings etc. I then bind the combo boxes to display the values from the Quote and Contract tables so when adding a job the user can select the Quote from a list. Sounds simple enough but i cannot get it working. the form displays and the drop down boxes are populated but when i hit save nothing happens, no error and no data is saved :S Does anybody have an example or Tutorial on how to do this properly?

I Hope somebody can help

Best Regards

Jordon.

 

[cjard]

a job has a quote and a contract?

Does this mean that quote is many/many related to contract:

quote M--M contract

and hence we must break this up:

quote M--1 job 1--M contract

so a job is purely for relating a quote to a contract. Both quote and contract must exist before job, and each job links just one contract to one quote, uniquely?


and you want to make a form that does what... shows quote and contract, and creates a job entry? or is able to create all of quote, job and contract entry?

 

[HeavenCore]

Hi thanks for your response.

we are only dealing with 1 to 1 relationships here (for now)

1 job can have 1 contract

1 job can have 1 Quote

the form i wish to create is essentially an 'Add/edit Job' form displaying the Job Attributes with drop down lists the foreign keys, based on the principle that the quote and contracts have already been created. Basically giving the user a quick list of potential quotes so they can just select one from a drop down box when adding a new job.

(find attached a relationship diagram and a quick form idea)

Hope this sheds some more light on this, I look forward to your response

Regards

Jordon

 

[cjard]

OK, so youre going to want 2 lists, of quotes and contracts that are job-orphans, essentially.

In your dataset, create 2 new tableadapters (the associated datatables will create automatically) from the following queries:

SELECT 
  QuoteID as ValMemb,
  QuoteName || QuotePrice as DispMemb
FROM
  tblQuote
  LEFT OUTER JOIN
  tblJobs
  ON
    QuoteID = fk_JobJobQuote
WHERE
  fk_JobJobQuote IS NULL

SELECT 
  ContractID as ValMemb,
  ContractName || ContractPrice as DispMemb
FROM
  tblContract
  LEFT OUTER JOIN
  tblJobs
  ON
    ContractID = fk_JobContract
WHERE
  fk_JobContract IS NULL

These queries find orphaned contracts and quotes by joining the tables in such a way that the FK in jobs will be null if there is no matching record and looking for those nulls. In a relational system this is the safest way, and it should be fast too, because the FK fields will need to be indexed to maintain the relationship. If you have some other way of determining this, then reconsider it, in terms of data sync problems.

Also consider that if quotes: jobs and jobs: contracts is only ever 1:1 then it should all be in the same table. We only split tables and normalise when there will be a 1:M relationship.. if we are not excessively normalising (which usually generates more work in the form of SQL joining than it saves in data organisation)

Now you have 2 tables that have 2 columns, disp and val

REMOVE ALL EXISTING BINDINGS FROM THE COMBO BOXES

Click the combo and press the little PLAY button |> in the top right of the combo.
Choose TICK use data bound items
DataSource = The relevant new table you made
Display member = dispmemb
Value member = Valu memb
Selected Value = YOUR_OLD_BINDING_TO_THE_JOBS_TABLE.relevant column


-

So we end up with 2 combos that:

Show a list of orphaned quotes/contracts
Display a nice friendly name of the quote/contract
But store the quote/contract id of the displayed item, into the jobs table


i.e. the list to show comes from the orphans list, but the data to change is the jobs list.

Relations are not needed for this to work

If you want to show other data from the quotes or contracts table, on the form, then just include it in the query as well as DispMemb and ValueMemb

Confused? Tell me and I'll make an example

 

[cjard]

Note that you never said what DB you use. I have provided ORACLE syntax for string concatenation ||

Change to your local db choice

 

[HeavenCore]

Thanks again for your reply ; i'll give this a try now...

For the record i am just using sqlExpress.

I'll keep you updated

Regards

J.

 

[HeavenCore]

Hello again.

i created the table adapters and i do understand the method your trying to use, but no record is saved when i click save. No errors are presented, and the combo boxes are blank. i have stripped my project down to a small zip of the project (database and project files: 1.3mb) if you dont mind having a look?

i would really appreciate it, big learning curve this

the zip can be downloaded here www.heavencore.co.uk/BindingProblem.zip


its an mdi app so ignore login and just click jobs menu at top,,

thanks again *crosses fingers"

 

[cjard]

Congrats on making a good project that I can jsut run striaght off, no probs

Erm.. your combos are blank because there literally are no orphaned jobs or quotes!

Create some quotes and contracts that are not already assigned to jobs! (I did, and it works perfectly..

Some other points of note:

Your login screen can be ignored

The combos will necessarily be blank when displaying existing data, because whatever existing quote/contract is for that job will not be orphaned (because it is assigned to a job; that job) so the ID will not appear in the orphan list from which the combo derives.
In essence, you have a situation where the Review is different to the Add - when in "add mode" the form wants to show a combo of orphaned quotes, but when in Review mode, it wants to show a textbox of the currently assigned quote.. Hope this makes sense

You can attach an event handler to the PositionChanged of the jobs bindingsource, upon a change, if the record we have changed to is a New record (i.e. RowState = Added) then show the orphans combo, otherwise show a textbox of the existing selection. If you inted to show a name or other related data you msut re-establish the relations you show in JPEG 1. For more info see the DW2 link in my sig, section displaying related data.

Also, take a read of the DNU link in my signature. This issue too, looks set to bite you from what I see in your project..

 

[HeavenCore]

Your login screen can be ignored

yup i know, stripped all that out to keep it a small zip

the DNU thing is a problem i think.... i'll keep looking

Thanks for all the help m8 your a legend!

You can attach an event handler to the PositionChanged of the jobs bindingsource, upon a change, if the record we have changed to is a New record (i.e. RowState = Added) then show the orphans combo, otherwise show a textbox of the existing selection.

Lost me on that one, i am very new to this (i am a PHP MYSQL developer, databinding is a whole new way of doing things)

 

[cjard]

Lost me on that one, i am very new to this (i am a PHP MYSQL developer, databinding is a whole new way of doing things)

Your controls are bound to a BindingSource (BS)
BS is attached to the underlying data
BS can sort, filter and maintains position
When you change the position of a BS, the textboxes change what they show (Textbox can only show one thing at once, on a 100 row result set, some positional nav is required)

PositionChanged is an event of BS, indicating the textboxes etc are looking at a different row. You can query the row to find out if it is New (not in db yet) Modified (original values are different to current) or deleted. VB uses this to determine whether to fire an Insert, Update or Delete query when sending the row back to the db..

You can use it to work out what to show: combo (for add new) or textbox (view existing)

For more info read up on DataRow.RowState
Also look at DataRowView.RowVersion

Remember that a BS maintains a DataRowView - a sorted/filtered set of DataRow. New rows are put into the BS, and upon calling EndEdit of the row, sent into the underlying source. Upon calling Update() they are synced to the DB.. Disconnected data!