Populating a form with data from different tables.

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
amw22Asked:
Who is Participating?
 
ptakjaCommented:
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
 
amw22Author Commented:
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
 
amw22Author Commented:
I was able to get the relatioship correct, but using the DataGrid does not get the appropriate records to display.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
amw22Author Commented:
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
 
xorcrackCommented:
use:

DataGrid1.DataSource = ds
DataGrid1.DataMemeber = "table2"
0
 
amw22Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.