Using a data relationship 1

[woodyinoz]

Hi all,

I have a dataset called dsStage. This dataset contains two tables - Stage and StageSub.
I have created a relationship between the two... This is called relstage.

I used the following code to create the relationship:

        Dim relStage As New DataRelation("StageRel", _
            DsStage.Tables("Stage").Columns("StageNo"), _
            DsStage.Tables("StageSub").Columns("StageNo"))
        DsStage.Relations.Add(relStage)

I have the following code underneath a button to navigate between the records in the stage table:

Me.BindingContext(DsStage, "Stage").Position = (Me.BindingContext(DsStage, "Stage").Position - 1)

I have the stage table info being displayed in text boxes at the top of the form.... My question is, how do I use my relationship to show the correct stageSub information in a datagrid on the form, changing it in relation to the stage being shown?

Thanks in advance,

Woody

 

[jebenson]

The easiest way I know how to do this does nor involve a DataRelation or using the BindingContext. Just use a DataView of the StageSub table as the DataSource of the DataGrid, and set the DataView's RowFilter property as the Stage records are displayed. Here's some code:

'Dim a DataView in the Declaration section of the form's code, so it is global to the form

Dim dvStage As DataView

'...code to set up DataSet DsStage

'Set DataView

dvStage = DsStage.Tables("StageSub").DefaultView

'Bind StageNo column of Stage table to a ComboBox

ComboBox1.DataSource = DsStage.Tables("Stage")
ComboBox1.DisplayMember = "StageNo" 'you may want something else here, like a name, description, etc.
ComboBox1.ValueMember = "StageNo"

'Bind DataView to DataGrid
DataGrid1.DataSource = dvStage


Now, in the ComboBox's SelectedIndexChanged event handler:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
dvStage.RowFilter = "StageNo=" & sender.text.trim
End Sub

I hope this helps. Post again if you have any questions.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[woodyinoz]

Thanks for that!

Can the user add records using a dataview and then update the database?

 

[jebenson]

Absolutely! You just need to set the DataView's AllowNew property to True, and use a DataRowView to do the actual adding of new data. Here's some example code:

dvStage.AllowNew = True

Dim drv As DataRowView

drv = dvStage.AddNew

drv("Column1") = "Some Value"
drv("Column2") = "Some Other Value"

Note: replace the "Column1" and "Column2" with your actual field names.

The data you input to the fields of the DataRowView object are reflected in the underlying Dataview, and from there to the DataTable in the original DataSet. Once this is done, you can do an Update with the DataAdapter.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson