Re: Replacing key values with their referenced data records

From: Sijin Joseph (sijinNOSPAMdotnet_at_hotmail.com)
Date: 09/14/04 

Date: Tue, 14 Sep 2004 14:02:26 +0530

Hi Wade,

You need to search for Lookup values or Lookup tables. In .Net they are
set using the DisplayMember and ValueMember property of controls like
ComboBox, ListBox etc.

For the datagrid, you will need to define a DataRelation between your
parent tables and child tables and then use the DataColumn.Expression
property to set the value to the ParentRelations relevant field.

This is the relevant documentation from the MSDN documentation of
DataColumn.Expression property

PARENT/CHILD RELATION REFERENCING

A parent table may be referenced in an expression by prepending the
column name with Parent. For example, the Parent.Price references the
parent table's column named Price.

A column in a child table may be referenced in an expression by
prepending the column name with Child. However, because child
relationships may return multiple rows, you must include the reference
to the child column in an aggregate function. For example,
Sum(Child.Price) would return the sum of the column named Price in the
child table.

If a table has more than one child, the syntax is: Child(RelationName).
For example, if a table has two child tables named Customers and Orders,
and the DataRelation object is named Customers2Orders, the reference
would be:

Avg(Child(Customers2Orders).Quantity)

Also check out these articles

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatinglookuptableforlistboxorcomboboxcontrol.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vbwlkCreatingLookupTableOnData-BoundWindowsForm.asp

Sijin Joseph
http://www.indiangeek.net
http://weblogs.asp.net/sjoseph

Wade wrote:
> I've been trying to solve this problem for several days, but I don't know
> what this operation is called, so I can't effectively search for a solution.
>
> I have a datagrid bound to a table I've imported from Access. The table is
> part of a relational database, so it has several columns containing foreign
> keys to related tables. In Access, the keys are replaced with the record
> that they reference in their respective parent tables. Each related record
> is a single string, so it simply replaces the key value in the column. This
> way, a user can change the string value without having to know the key (ID)
> of the data they are changing. I want to replicate this functionality in my
> VB .NET program, but I can't figure out how. Importing the tables and
> displaying the data isn't a problem, but I don't know how to relate the
> tables in the dataset and properly bind the datagrid to display the table the
> way I want it. Additionally, once I can get it to display properly, I need
> to be able to change a string value, and have the program locate the proper
> key value for the string so that key can be returned to the database. I've
> been told a solution is in the Resource Kit, but I haven't been able to find
> it. Any direction you can give me will be greatly appreciated.

Relevant Pages

  • Re: DataRelations and DataGrids
    ... Create a DataRelation LookupToMain. ... > a DataRelation between your parent tables and child tables and then use ... > relationships may return multiple rows, you must include the reference ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: Searching for parent in child-parent table
    ... Should the UNION ALL not be placed between the SELECT statements? ... SELECT UNION ALL DOC, Ch2 AS Child ... When a document is entered you indicate it's parent if it has one. ... such that I can then use it as a reference to get other information on ...
    (microsoft.public.access.queries)
  • RE: How to show fields from related tables in the same datagrid
    ... the parent table and child table normally have master-details ... why I recommanded using combobox to display the related child table records ... Also, if you still does not want to display combobox, we have another ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: DataRelations and DataGrids
    ... one table, there is no build-in support for the updating synchroniztion, we ... I think your concern is displaying the all the child table's rows. ... just select the parent and child tables in 2 datatables in one dataset, ... then use the datarelation to display the selected child row's ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Query vs form with child & parent: Deleting rows
    ... When I delete a row from a query involving both parent and child rows, ... when I display this query in a form and delete a row from there, ...
    (microsoft.public.access.forms)
Quantcast