Solved

Populating a form with data from different tables.

Posted on 2004-08-04
6
174 Views
Last Modified: 2010-05-18
Hi,
I have a form which needs to contain data from two different tables.  The tables have a one to many relationship.  Currently I have filled the dataset with both tables and created the appropriate relationship between the two tables, but I need to know how to make the linked records display on the form at the same time.  Right now they do not.  Here is the code I have now:

 Dim da_table1 As New OleDb.OleDbDataAdapter("SELECT * FROM table1", Conn)
        Dim da_table2 As New OleDb.OleDbDataAdapter("Select * From table2", Conn)

        da_table1.Fill(ds, "table1")
        da_table2.Fill(ds, "table2")
       
        Dim childCol As DataColumn = ds.Tables("table1").Columns("column1")
        Dim parentCol As DataColumn = ds.Tables("table2").Columns("column2")

        Dim relMPSMS As DataRelation
        relMPSMS = New DataRelation("rel", parentCol, childCol)
        ' Add the relation to the DataSet.
        ds.Relations.Add(rel)
   
        TextBox1.DataBindings.Add("Text", ds, "table1.column1")
        TextBox9.DataBindings.Add("Text", ds, "table2.column2")
        TextBox7.DataBindings.Add("Text", ds, "table2.column3")

I am new to VB.Net and ADO.Net so explanations with code would be helpful, Thanks
0
Comment
Question by:amw22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 14

Accepted Solution

by:
ptakja earned 250 total points
ID: 11719595
First off, put your data tables into ONE dataset.  That will make things much easier:

        Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM table1;SELECT * FROM table2", Conn)
        da.Fill(ds)
        ' At this point your dataset will have 2 tables in it with default names of Table1 and Table2.  You can rename them with the TableName property if you like.
       
        Dim childCol As DataColumn = ds.Tables("table1").Columns("column1")
        Dim parentCol As DataColumn = ds.Tables("table2").Columns("column2")

        Dim relMPSMS As DataRelation
        relMPSMS = New DataRelation("rel", parentCol, childCol)
        ' Add the relation to the DataSet.
        ds.Relations.Add(rel)

Now, to get the linked tables to show up, use the DataGrid control:

DataGrid1.DataSource = ds
DataGrid1.DataMemeber = "table1"

That's all there is to it.
0
 

Author Comment

by:amw22
ID: 11720389
What if I don't want to use a DataGrid?   I would like to bind everything to textboxes and listboxes right now.  Also I think I have my parent and child columns reversed, but the column which I believe should be the parent column in not a unique identifier in that table and I get errors that the relationship cannot be made that way.
0
 

Author Comment

by:amw22
ID: 11720578
I was able to get the relatioship correct, but using the DataGrid does not get the appropriate records to display.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:amw22
ID: 11720603
All of the records in table1 display in the datagrid which is what I want, but I need it to focus on the record that corresponds to the parent record.  How do I do that?
0
 
LVL 1

Expert Comment

by:xorcrack
ID: 11722871
use:

DataGrid1.DataSource = ds
DataGrid1.DataMemeber = "table2"
0
 

Author Comment

by:amw22
ID: 11722944
I have tried using both "table1" and "table2" for the DataMember property and it does not work.  What I have is a basic windows form using mostly textboxs populated with data from table2.  I would like to populate the datgrid with data from table1, but I need the record in table1 corresponding to table2 to be focused on in the datagrid.  By focused I mean being highlighted or at the top of the datagrid so that it is clear that particular record goes with the rest of the data being displayed on the form.  
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question