Lookup tables and data binding

[Joe S.]

Hello,
I have a form that is used only to insert data into a database. It is
composed of a datagrid which is used only for displaying data and selecting
rows and a few text and combo boxes for the actual data editing.

What I want to do is bind these to a data source, but I am not exactly sure
how to handle the comboboxes which show data from a standard lookup table
(int id, nvarchar something). I displayed data from lookup tables by binding
their ValueMember and DataMember properties.

When the user clicks a row in the datagrid, the data from that row should
appear in the other controls for editing. The comboboxes should show the
data from the selected row, but how can they do this if they are already
bound to lookup tables?

A possible workaround is to bind the comboboxes to the lookup tables when a
new record is being created, and to the datagrid's datasource later. This
has its own share of problems, of course.

Maybe I should describe these relations in a dataset, and rely on the data
binding mechanism to figure out what is supposed to be in the combo boxes?
The problem with this is that I do not need a SELECT statement, because this
form is write-only. What do I do with the DataAdapter.Fill() and
DataAdapter.SelectCommand then?

I hope I made myself clear. If any clarifications are needed, do not
hesitate to ask.

Regards,
Joe

 

[Andy Becker]

When the user clicks a row in the datagrid, the data from that row should
appear in the other controls for editing. The comboboxes should show the
data from the selected row, but how can they do this if they are already
bound to lookup tables?

I think you're clear enough... Let's find out.

The binding of the ComboBox to a lookup table is actually separate from the
selection, as it should be. I think you probably want to keep your
DisplayMember and ValueMember settings as they are, but to add a binding for
SelectedValue to the underlying table. This would be to the column which
holds the lookup value, i.e. the FK reference.

So you have two "bindings" - one to the lookup table (DataSource,
DisplayMember and ValueMember), and one to the actual table (SelectedValue).
The trick is that DataSource in this case is unrelated to data bindings -
it's only indicating where the list comes from.

When you mention values from the datagrid row appearing in the other
controls for editing, this appears to be at odds with the notion that the
form is only used for adding data. Are the datagrid rows serving as default
values or something?

Best Regards,

Andy

 

[Joe S.]

So you have two "bindings" - one to the lookup table (DataSource,

DisplayMember and ValueMember), and one to the actual table (SelectedValue).
The trick is that DataSource in this case is unrelated to data bindings -
it's only indicating where the list comes from.

Thanks, that's it!

When you mention values from the datagrid row appearing in the other
controls for editing, this appears to be at odds with the notion that the
form is only used for adding data. Are the datagrid rows serving as default
values or something?

The user can insert, delete and update rows locally, in the memory, but once
finished, the final data is inserted into the database. So, there really are
only INSERT operations going on.

Is there a way to easily create a DataTable that has the same structure as a
physical table in the database? Since there is no SELECT on this form which
would get me the DataTable to use as a data source, I have to manually
create one which has the same schema as the one in the database. I know
about DataReader.GetSchemaTable(), but is there a way to automatically
"apply" that information to a DataTable? Am I missing something obvious
(again)?

Thanks for the help.

 

[Andy Becker]

Is there a way to easily create a DataTable that has the same structure as a
physical table in the database? Since there is no SELECT on this form which
would get me the DataTable to use as a data source, I have to manually
create one which has the same schema as the one in the database. I know
about DataReader.GetSchemaTable(), but is there a way to automatically
"apply" that information to a DataTable? Am I missing something obvious
(again)?

I would use a typed dataset, if you don't mind creating it at compile time.
In Solution Explorer, right click the project and then Add New Item. Choose
Dataset, name this puppy whatever you like, and then you will get a design
surface. From Server Explorer (same tool window as Toolbox, by default),
create a data connection to the DB. Then if you drag the table onto the
design surface, it will create a typed dataset for you, PK definition and
all, matching the table.

If you are talking about creating the table at runtime, I'm not sure how it
would be done. Clearly it can though, since the IDE does it in the steps I
just described.

Best Regards,

Andy

 

[Joe S.]

If you are talking about creating the table at runtime, I'm not sure how
it

would be done. Clearly it can though, since the IDE does it in the steps I
just described.

Creating it at runtime is what I meant, but I managed to work around this
problem.

Thank you very much for your extensive replies.

Joe