DataGridView with multiple table info

[cday119]

Here is my basic dilemma

I have a database with two tables. Computers table and Location Table. The computers table basically looks like this

ID
Location
...

Example Rows

ID                       Location
Computer1          39
Computer2          21

The Location table looks like this

LocationID
Location

Example Rows

LocationID    Location
39                Computer Room
21                Conference Room

I created a dataset with the two tables, then created the relations, but now how do I display the data in a datagridview so it just shows the ID and the Location. I want the DataGridView to look like this

ID                 Location
Computer1     Computer Room
Computer2     Conference Room

This seems like a common thing to me but I just can't figure it out.

Thanks

[Curtis Rutland]

I would do this in SQL. Either set up a view in your DB, or use JOIN statements in your select command. I think that using two DataTables and DataRelations is doing the work on the wrong side of things. SQL is made for this.

[cday119]

But if I update the location that will not reflect on the computers.locat ion cell.

[Curtis Rutland]

But if I update the location that will not reflect on the computers.locat ion cell.

???

I'm sorry, I don't understand. Please be more clear.

[cday119]

Yeah sorry I was focused on the database layout I forgot to mention that I need to edit and update the data. And I would like to use the Adapter.Update command.

I can add a new row the the datagridview but it doesn't let me set a datasource. I think I can do something like calling the Computers table children, which was created when I added the relation. But I do not know the syntax of this or if I will be able to update the database after editing the location.

[Curtis Rutland]

As long as you are selecting the PK field, the CommandBuilder should be able to generate a good update/insert/delete statement for you, even if you are using joined tables.

Otherwise, what you might have to do is make a third DataTable, and fill it from your two original datatables. Then, when it is updated, you might have to go back through it and update your original tables.

[cday119]

Isn't there a way to add a column to the datagridview that holds the data of another table?

[cday119]

Here is what I did to show rows from multiple tables on one data grid view. Basically:
Load the dataset with all the data in all the tables
Add the Relations
Create a JoinView
Set the datagridview datasource to the JoinView.

Here is the code:

    'We declare data adapters for all the tables so we can update the dataset using dsHardware.Update.  If we do a Join statement, this would not be possible
    Public daComputers As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Select * from Computers", My.Settings.ConnectionString)
    Public daStyle As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Select * from Style", My.Settings.ConnectionString)
    Public daLocation As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Select * from Location", My.Settings.ConnectionString)




'Load the DataSet
        Try
            FillDataSet()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        'Add Relations
        dsHardware.Relations.Add(New DataRelation("relCompStyle", _
          dsHardware.Tables("Style").Columns("StyleNum"), _
          dsHardware.Tables("Computers").Columns("Style"), True))

        dsHardware.Relations.Add(New DataRelation("relCompLoc", _
          dsHardware.Tables("Location").Columns("LocNum"), _
          dsHardware.Tables("Computers").Columns("Location"), True))

        'Load the JoinView - This is what is displayed in the datagrid view.  We do this to show data from multiple tables on one table
        Dim jvMaster As JoinView = New JoinView(dsHardware.Tables("Computers"), "RecordNum, Assignment, Sys_ID, Role, Rep_Cycle, Processor, RAM, Hard_Drive, Windows_OS, Dell_Tag, Decommissioned, Netware, relCompStyle.Style, relCompLoc.Location")

        'Load the DataGridView - Set datasoure the the JoinView we just created
        dgvComputers.DataSource = jvMaster






 Public Function FillDataSet() As Boolean
        'Computers Table
        daComputers.MissingSchemaAction = MissingSchemaAction.AddWithKey
        daComputers.Fill(dsHardware, "Computers")

        'Location Table
        daLocation.MissingSchemaAction = MissingSchemaAction.AddWithKey
        daLocation.Fill(dsHardware, "Location")

        'Style Table
        daStyle.MissingSchemaAction = MissingSchemaAction.AddWithKey
        daStyle.Fill(dsHardware, "Style")