Implementing a "Many to Many" relationship with 2 datagrids

Simplified Background:

First, I'm kind of new to datagrids, so I hope I can get my question across
right.

I have a database with the following three tables:

PersonTBL: A single record = a person
CourseTBL: A single record = a single college course
PersonHasCourseTBL: Pairs up a Person with a Course - A single record = a
single person/course combination. (Has foreign keys to the other two tables)

One person can have many courses
One course can have many people

What I want to have is two (editable) datagrids:

Datagrid 1: Displays the unique people.
Datagrid 2: Displays all of the courses for the current selected row
(person) in Datagrid 1

It is easy to display the data:

Code:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strSQL As String
strSQL = "Select * from PersonTBL"

objAdapterPerson = New SqlDataAdapter(strSQL, objConn)
objAdapterPerson.Fill(objDS1, "Person")
DataGrid1.DataSource = objDS1.Tables("Person")

Dim strPersonID As String
strPersonID = DataGrid1.Item(DataGrid1.CurrentRowIndex, 0)

objDS2 = New DataSet

strSQL = "Select * from CourseTBL"
objAdapterCourse = New SqlDataAdapter(strSQL, objConn)
objAdapterCourse.Fill(objDS2, "Course")

strSQL = "Select * from PersonHasCourseTBL"
objAdapterRelationCoursePerson = New SqlDataAdapter(strSQL, objConn)
objAdapterRelationCoursePerson.Fill(objDS2, "Relate")
objDS2.Relations.Add("relPersonCourse",
objDS2.Tables("Course").Columns("CourseID"),
objDS2.Tables("Relate").Columns("CourseID"))
objDS2.Tables("Relate").Columns.Add("Course", GetType(String),
"Parent(relPersonCourse).Description")

objView = New DataView(objDS2.Tables("Relate"))
objView.RowFilter = "PersonID='" & strPersonID & "'"

DataGrid2.DataSource = objView
End Sub

Private Sub DataGrid1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles DataGrid1.Click
Dim strSQL As String
Dim strPersonID As String

objDS2 = New DataSet
strPersonID = DataGrid1.Item(DataGrid1.CurrentRowIndex, 0)

objView.RowFilter = "PersonID='" & strPersonID & "'"
DataGrid2.DataSource = objView
End Sub

This displays the data how I would expect, but I already have a bunch of
questions:

1. I have a relational constraint that the course in the PersonHasCourseTBL
table must exist in order to exist in the CourseTBL table. The code I have
right now won't let me add new course information, only type in an ID for an
existing course. I want to be able to add a new course this way.

2. It doesn't seem to "save" these new rows to the dataset when I navigate
through different people. How do I get this to happen?

3. Some of the columns in my course grid I want to hide - Can I simply
manipulate the DataView for this without screwing up the DataSet?

4. Some of the columns in my course grid are lookups (for example - which
building is the course in) - obviously those currently show up as the IDs
into the lookup tables. Is there an easy way to make those columns into some
sort of bound combobox (and allow any changes to flow through correctly)

5. Finally, how do I get any jumbled heap of changes (whether it be adds,
updates, or deletes) back into the database smoothly?


I always want to do things the "right" way (i.e. the way its intended to be
done, with the fewest lines and readable code). I know I can figure out some
ways to do some of these things I mentioned, but slopping things together to
make it work isn't my idea of a good time . Any help, as always, greatly
appreciated.

Oh, and please let me know if you feel any of my current code is bad - I'm
halfway expecting it.
.

Relevant Pages

  • Zooming into a single record from Datagrid
    ... I am using a Datagrid with ADO code bindings. ... ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ... and then when the Single record FORM is loaded, I sue the following code to ... adoPrimaryRS.Move recposition - 1 ...
    (microsoft.public.vb.database)
  • DataGrid wont refresh
    ... I actually perform the deletion by issuing a sql string to connect to SQL ... in the datagrid that the user is viewing UNTIL, ... and user comes back to the form, OR until user presses the F5 key for a ... How can I get the single record deletion to appear immediatly in the ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • dynamic datagrid dropdown valuechanged repopulate 2nd list editmo
    ... I have a dynamic datagrid. ... string strSelectedID; ... I started with creating an ascx control which is a dropdown list. ... public string SelectedValue{ ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ParentWert in DataGridColumn anzeigen
    ... > StadtName String 50 ... > StadtID AutoWert ... > OrtsID AutoWert ... Ich fülle das DataGrid selber mit Spalten auf (und ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: problem with IListSource does not contain data source!!
    ... Imports System.Web.UI ... Private strSQLserver As String = "" ... Protected WithEvents grdNames As DataGrid ... Protected Overrides Sub CreateChildControls() ...
    (microsoft.public.sharepoint.portalserver.development)