Question Many Question about Data Accessing

[rekhtar]

Hello Dears

I wanna know some important things


Case:

1- I have 3 tables that have relations (Contacts, Telephones, Category)

One Contact have many Telephones, I want to make query or something else that let my Data is Displayed like this:



(Group by Name)

How Can I do This?

AND HOW can I Display The data in The DataGridView as the same way in the picture?

------------------

Case

2- I have Combobox that filled with Contacts Names from Contacts Table, I want to populate Contact details into Textboxes, and remember that each contact has many telephones, therefore I have DomainUpDown control for telephones becaues I want list all contact's telephone numbers in DomainUpDown control

How Can I populate Contact Detail into Textboxes?

How Can I list Telephones in DomainUpDown?

( I work on VB.NET 2005)

Thank you in Advance

 

[jmcilhinney]

First up, please attach your images directly to your posts in future so we don;t have to click off to an external site to see them.

As for question 1, it can't be done. The DataGridView doesn't support a hierarchical view like that. Some third-party grids would but if you want to show related data in a DataGridView then you need more than one of them.

For question 2, try this example:

1. Create a new WinForms project.
2. Add a ComboBox, a TextBox and a DomainUpDown.
3. Set the DropDownStyle of the ComboBox to DropDownList.
4. Add a BindingSource.
5. Add the following code:

Private data As New DataSet

Private Sub Form1_Load(ByVal sender As Object, _
                       ByVal e As EventArgs) Handles MyBase.Load
    Dim people As New DataTable("Person")

    With people.Columns
        people.PrimaryKey = New DataColumn() {.Add("ID", GetType(Integer))}

        .Add("Name", GetType(String))
        .Add("Colour", GetType(String))
    End With

    With people.Rows
        .Add(1, "Peter", "Red")
        .Add(2, "Paul", "Green")
        .Add(3, "Mary", "Blue")
    End With


    Dim phoneNumbers As New DataTable("PhoneNumber")

    With phoneNumbers.Columns
        phoneNumbers.PrimaryKey = New DataColumn() {.Add("ID", GetType(Integer))}

        .Add("PersonID", GetType(Integer))
        .Add("Value", GetType(String))
    End With

    With phoneNumbers.Rows
        .Add(1, 1, "98765432")
        .Add(2, 1, "0412345678")
        .Add(3, 2, "97654321")
        .Add(4, 2, "0423456789")
        .Add(5, 3, "96543210")
        .Add(6, 3, "0434567890")
    End With


    data.Tables.AddRange(New DataTable() {people, phoneNumbers})
    data.Relations.Add("PersonPhoneNumber", _
                       people.Columns("ID"), _
                       phoneNumbers.Columns("PersonID"))


    Me.BindingSource1.DataSource = people

    Me.ComboBox1.DisplayMember = "Name"
    Me.ComboBox1.ValueMember = "ID"
    Me.ComboBox1.DataSource = Me.BindingSource1

    Me.TextBox1.DataBindings.Add("Text", Me.BindingSource1, "Colour")
End Sub

Private Sub BindingSource1_CurrentChanged(ByVal sender As Object, _
                                          ByVal e As EventArgs) Handles BindingSource1.CurrentChanged
    Dim personRow As DataRow = DirectCast(Me.BindingSource1.Current, DataRowView).Row
    Dim phoneNumberRows As DataRow() = personRow.GetChildRows("PersonPhoneNumber")
    Dim upperBound As Integer = phoneNumberRows.GetUpperBound(0)
    Dim phoneNumbers(upperBound) As String

    For index As Integer = 0 To upperBound
        phoneNumbers(index) = CStr(phoneNumberRows(index)("Value"))
    Next

    Me.DomainUpDown1.ResetText()
    Me.DomainUpDown1.Items.Clear()
    Me.DomainUpDown1.Items.AddRange(phoneNumbers)
    Me.DomainUpDown1.SelectedIndex = 0
End Sub

6. Run the project and experiment.

I'd be inclined to think that a ComboBox for the phone numbers would be a better option than a DomainUpDown.

 

[rekhtar]

your Code works in own form, but It doesn't work with my code


This is my code, and please tell me what's wrong

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

 ExecuteDS("Select * from Cats order by CatName", "Cats") 'Sub Retrieves data from Ctegory Table and make DataTable inside Dataset
        Me._CmbCat.DataSource = ds.Tables("Cats")
        Me._CmbCat.DisplayMember = "CatName"
        Me._CmbCat.ValueMember = "CatID"
        Me._CmbCat.SelectedIndex = -1
        Me.Width = 680
        Me.Height = 370

        ExecuteDS("Select * from Contacts order by CName", "CNameTbl") 'Sub that retrieve Data and make DataTable inside Dataset
        daTelephones = New OleDb.OleDbDataAdapter("select * from Telephones", cn)
        daTelephones.Fill(ds, "Tel")
        Dim drel As DataRelation = New DataRelation("CT", ds.Tables("CNameTbl").Columns("CID"), ds.Tables("Tel").Columns("CID"))
        ds.Relations.Add(drel)

        Dim drel2 As New DataRelation("CatContact", ds.Tables("Cats").Columns("CatID"), ds.Tables("CNameTbl").Columns("CatID"))
        ds.Relations.Add(drel2)
        bs.DataSource = ds.Tables("CNameTbl")
        Me._CmbQuickSearch.DataSource = bs
        Me._CmbQuickSearch.DisplayMember = "CName"
        Me._CmbQuickSearch.ValueMember = "CID"

        Me._CmbQuickSearch.Text = ""

        bs2.DataSource = ds.Tables("Tel")

        Me._TxtFullName.DataBindings.Add("Text", bs, "CName")
        lblstatue.Text = "Add New Contact or Choose Contact from Quick Search List"

    End Sub



Private Sub bs_CurrentChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bs.CurrentChanged
        dr = DirectCast(bs.Current, DataRowView).Row
        drs = dr.GetChildRows("CT")
        Dim upperBound As Integer = drs.GetUpperBound(0)
        Dim phoneNumbers(upperBound) As String

        For index As Integer = 0 To upperBound
            phoneNumbers(index) = CStr(drs(index)("Mail"))
        Next

        Me._DomainUpDownMail.ResetText()
        Me._DomainUpDownMailMail.Items.Clear()
        Me._DomainUpDownMailMail.Items.AddRange(phoneNumbers)
        Me._DomainUpDownMailMail.SelectedIndex = 0 
    End Sub

the first problem I face, this line:

Me._DomainUpDownMailMail.SelectedIndex = 0

and the error is: InvalidArgument=Value of '0' is not valid for 'SelectedIndex'.
Parameter name: SelectedIndex

when I remove this line, the application works, but when I choose the name from ComboBox, Nothing happens

thanks for intersting

 

[jmcilhinney]

If you can't set the SelectedIndex to 0 then there are no items in the DUD. You would need to test for that condition before setting the SelectedIndex.

 

[rekhtar]

Thanks for intersting

I'll try another way