Solved

Populating a form with data from different tables.

Posted on 2004-08-04
6
172 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
  • 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

827 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