Performing Lookups on Dataset

  • Thread starter Thread starter Hemang Shah
  • Start date Start date
H

Hemang Shah

In the customer table, the primary key of the lookup table is stored.

e.g:

Table Customer

CustTypeID1
CustTypeID2
....


Table Lookup

TypeID
TypeName



Now in report or on the form, is there a quickway to do a lookup on table
Lookup by the id ?

I want a function like this:

TypeName = Lookup(typeid);

So that I can use this in crystal reports and also on the forms..

Any suggestions would be welcome.

Thanks
 
Hi,

If these two tables are in a database then when you select data you could
join these two tables using next kind of SQL statement and it will bring all
the related descriptions for the types

SELECT Customer.*, Lookup.TypeName FROM Customer LEFT JOIN Lookup ON
Customer.CustTypeID1=Lookup.TypeID
 
Thanks Val

That would be easy.

However the first table has multiple fields of ID thas has the corresponding
name in the lookup table.

I can't have multiple Joins on all these fields between the same two table
right ?

That was the reason for my lookup function.

Any ideas ?
 
Hi,

You can use Find method of Rows Collection in the DataTable like this:

DataRow dr = Lookup.Rows.Find(typeid);
String TypeName = dr["TypeName"];


Regards,
Saravanan K V
 
Does the Lookup table have to be indexed on typeid ?

or sorted ? or any restriction ?

Saravanan K V said:
Hi,

You can use Find method of Rows Collection in the DataTable like this:

DataRow dr = Lookup.Rows.Find(typeid);
String TypeName = dr["TypeName"];


Regards,
Saravanan K V

news.microsoft.com said:
Thanks Val

That would be easy.

However the first table has multiple fields of ID thas has the
corresponding
name in the lookup table.

I can't have multiple Joins on all these fields between the same two
table
right ?

That was the reason for my lookup function.

Any ideas ?
Click to expand...
Click to expand...
 
Hi,

What about creating stored procedure and a convert data inside into some
sort of normalized temp table and join after that? Does it work for you?

--
Val Mazur
Microsoft MVP

http://xport.mvps.org
 
Hemang,

Okay now the question is clear (unlike your email which got me confused
LOL).

Anyway, you can skin this cat in 3 ways.

a) DataTable.Select
b) DataView - First sort on TypeName, and then do a DataView.Find or
DataView.FindRows
c) If TypeName's are unique, set a primary key constraint on that column,
and do a DataTable.Find

HTH :)

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
Hi,

You need to create a Primary key for the Lookup table with typeid column.
The table need not be sorted and you do not need any DataViews.

You can create PK like this:

DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = Lookup.Columns["typeid"];
Lookup.PrimaryKey = PrimaryKeyColumns;

Then use can use the Find method. Actually, the Find method takes the values
or array of values for the Primary keys of the DataTable.

Regards,
Saravanan K V
Hemang Shah said:
Does the Lookup table have to be indexed on typeid ?

or sorted ? or any restriction ?

Saravanan K V said:
Hi,

You can use Find method of Rows Collection in the DataTable like this:

DataRow dr = Lookup.Rows.Find(typeid);
String TypeName = dr["TypeName"];


Regards,
Saravanan K V

news.microsoft.com said:
Thanks Val

That would be easy.

However the first table has multiple fields of ID thas has the
corresponding
name in the lookup table.

I can't have multiple Joins on all these fields between the same two
table
right ?

That was the reason for my lookup function.

Any ideas ?

Hi,

If these two tables are in a database then when you select data you
could
join these two tables using next kind of SQL statement and it will
bring
all the related descriptions for the types

SELECT Customer.*, Lookup.TypeName FROM Customer LEFT JOIN Lookup ON
Customer.CustTypeID1=Lookup.TypeID

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



In the customer table, the primary key of the lookup table is stored.

e.g:

Table Customer

CustTypeID1
CustTypeID2
...


Table Lookup

TypeID
TypeName



Now in report or on the form, is there a quickway to do a lookup on
table
Lookup by the id ?

I want a function like this:

TypeName = Lookup(typeid);

So that I can use this in crystal reports and also on the forms..

Any suggestions would be welcome.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top