Solved

Populating a form with data from different tables.

Posted on 2004-08-04
6
175 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

707 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