Hi everyone. I have a very simple problem, that I seem to not being
able to solve!

Assume I have two simple tables Car, and Store, looking like this:

Car
-------
car_id
make


Store
-------
car_id
numberofcars


and I want to show, and update the data in the Store table. I can
easily use a grid and a datasource that binds to the Store table
alone, but then I have to stick with the not-meaningful car_id showing
a bunch of numbers instead of meaningful names that are stored in the
Car table.

In the database, I have added foreing key constaints, and when I add
the two tables to a datasource using VS 2005's wizard it nicely
observes the FK constraint and creates the corresponding DataRelation.
However, I have still not found a way to show a grid with the columns:
make | numberofcars. I am aware that I can show this easy if I create
a database View and bind that to the grid. However, that makes it
impossible for me to do (automatically at least) Updates to the table.

Any ideas? It should be very easy to accomplish this.

I appreciate ANY kind of help a lot, thanks!!

Claus

A couple of things. Easiest first, you can hide the ID columns in your
datasource using MappingType.Hidden. And yup, you cannot "easily" update a
grid with data relations. All the Microsoft-generated logic is based on a
single table binding scheme. If you wish to retain that scheme without
rolling your own update logic, then you will have to go with one table per
grid. If your needs are truly simple as you have shown, I would recommend
you load the Car table up in a listbox or combo, then bind the grid to the
Store table, or some variation thereof (click on a Car, populate a grid with
the Store data). For more complicated scenarios with a lot of columns, the
most common technique is to use two datagrids, where selecting a row from
the parent grid populates the child grid.


Show quoteHide quote

Hi,

Show quoteHide quote* The table layouts alone aren't always enough, i don't know what your exact
reasoning is...

1) there may be a one-to-one relation between car.car_id and store.car_id,
which isn't directly supported by ado.net(disconnected) or databinding, so
you might want to put them into a single table. Or you need to pretend it's
one-to-many.

2) there may be a one-to-many relation between car.car_id and store.car_id,
then
   cars would be the one side (car_id=pk)
   store would be on the many side (car_id=fk)
but then store would have its own pk and duplicate car_id's in store would
be possible.

Anyways, if you have a one-to-many (pretend or not) relation you can
'import' the car fields into the store table.

- Open DataSet schema designer (double-click DataSet inside solution
explorer).
- Right-click on the Store table and choose "Add Column", rename it to
'make'.
- Keep the new column selected and open properties window.
- For the expression property type : "parent(fk_relation_name).make".

The relation between Store and Car must be correct (Car is parent and Store
is child) and the relation must be a relation not just a foreign key
constraint, see relation dialog.

Recompile then update the DataGridView by disconnecting and reconnecting the
BindingSource (DataSource property).

* A view can be updateable if you create the update/delete/insert commands
yourself, this can be a daunting task, but often wizard-generated code is
cut, paste and changed for this.


HTH,
Greetings




Show quoteHide quote

How to cancel deleting a row in an event
Computed Columns
Help calling an Oracle procedure from vb.net
What is the purpose of AcceptChanges?
Concurrency notification question