Dataview & Relationships

[bizjosh]

Hi,

How do i input 2 related tables' fields into a dataview which
is displayed on a datagrid, choosing the fields (columns)
i want to display, anyone knows or have any examples?


Table1:
ID, Name, Age, Sex, Job

Table2:
ID, JobName, Salary

For example, I created a relationship "Table1Table2" I would like
my datagrid to display the fields: ID,Name,Age,Sex,Job,JobName.
JobName is from Table2, the others from Table1.
Not sure how to do it, these are my codes so far

daTable1.Fill(ds)
daTable2.Fill(ds)

dataview = ds.DefaultView

datagrid.Datasource = dataview

-
help appreciated! thanx!

 

[levyuk]

It should just be a case of changing the sql statement

 

[angitapalli]

check this Function:


Friend Sub GetMediaINABatch()
Call Connection()

Try

Cursor.Current = Cursors.WaitCursor

' To clear the tables and relation each time this function is called so that after deleting a batch, the grid is updated

sqlDataSet.Relations.Clear()

sqlDataSet.Tables.Clear()

Dim BatchAdapter As New SqlDataAdapter("SELECT Type + '-' + ID AS [Batch ID], ID,CASE Status WHEN 0 THEN 'none' WHEN 1 THEN 'Submitted' WHEN 2 THEN 'Ready' WHEN 3 THEN 'Authorized' WHEN 4 THEN 'UC-5A Authorized' END AS Status FROM tblBatch ORDER BY [Batch ID]; Select tblMedia.BatchID,tblMedia.MediaID from tblBatch,tblMedia where tblBatch.ID=tblMedia.BatchID", sqlConn)

BatchAdapter.Fill(sqlDataSet)

'Give Meaning full name to the tables

sqlDataSet.Tables(0).TableName = "Batch"

sqlDataSet.Tables(1).TableName = "Media"

'Establish a Parent child relationship between the two tables based on the common field BatchID.

Dim Parent As DataColumn = sqlDataSet.Tables("Batch").Columns("ID")

Dim Child As DataColumn = sqlDataSet.Tables("Media").Columns("BatchID")

Dim MediaInThisBatch As DataRelation = New DataRelation("MediaInThisBatch", Parent, Child, False)

'Add the relationship to the Dataset

sqlDataSet.Relations.Add(MediaInThisBatch)

'Bind the data grid to the default view of the Batch Table

Cursor.Current = Cursors.Default

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub


then in the code while filling datagrid:

dgBatch.TableStyles.Clear()

dgBatch.DataSource = Nothing

oleGetRec.GetMediaINABatch()

dgBatch.DataSource = oleGetRec.sqlDataSet.Tables("Batch").DefaultView

Hope this helps u...

 

[bizjosh]

Question:
Do i have to create it for all my related tables?
How do i know which is parent, which is child?

What do i do after i create the relationships?

In summary, if i have 8 tables, i create 8 data-adapters
for 8 tables and put all of them into a dataset?
Then type 8 times of da1-8.fill(dataset) and
how do i pass it into the dataview?