Joining Tables in a Dataset..

Hi Experts,

New to dotnet and also to VB.net.. Please give some ideas..

I have two tables loaded into a Dataset and I need to Join them using a sql condition.

I am not sure how can I Join tables in a dataset..

Can someone please post some articles are code snippets for the above requirement..

Kind of urgent..

Thanks,

K.
yarlapatiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yarlapatiAuthor Commented:
Experts,

Rephrasing the question....

I have two tables in A Dataset and these tables are loaded not using any of the Adapters like SQL, OleBD etc....

These tables are loaded using Custom adapter. Now I need to Join two tables Table1 and Table2 based on a join condition...

The join condition is something like this..


"SELECT T1.*, T2.* from Table1  as T1, Table2  as T2 WHERE (T1.[Column1] = T2.[Column3] AND T1.[Column2] = T2.[Column4]  AND ((T1.[Column4] = T2.[Column6] AND T2.[Column6] <> '0') OR (T2.[Column6] = '0')) AND T1.[Column5] = '0')" + _
            " OR " + " (T1.[Column5] = '1' AND T1.[Column5] = T2.[Column9]) ORDER BY T1.[Column5]"
 
I am not sure how to add relations on multiple columns.. Both the tables are different and there is no primary foriegn key relationship between them..

Now that I have two tables loaded in a Dataset I need to create a third Table joining the two tables using the above relation.

Please post some ideas how to acheive this... Sample code.. if any would be of great help.......

Thanks,
K

Dirk HaestProject managerCommented:
"You cannot use a DataView to join data between two DataTable objects, nor can you use a DataView to view only certain columns in a DataTable." ( David Sceppa, Microsoft ADO.NET book.)

Got caught with the fine print on this one, and I was sooo excited when I first heard about datasets too! :(
Seriously though, yes you will probably need to get ugly with manual coding but to point you in the right direction Microsoft already have written some kb articles to allow you to join tables in memory and pull out a subset of both tables into a new datatable (sorted  and filtered). This could probably be adapted to work across 3 or it might be easier for you to join 2 and then join
result with 3rd to produce what you want. The kb article numbers are:
326080 - C#
325688 - VB.Net

If you need the links are below:
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b326080
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b325688

HOW TO: Implement a DataSet JOIN Helper Class in Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b325688


This article might also help:
http://www.codeproject.com/csharp/PracticalGuideDataGrids3.asp?df=100&forumid=33941&exp=0&select=812739

assuming you have both tables in a dataset, start at section 4.4 of the article.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.