Hello!
I was wondering if maybe one of you guys could help me with this, I'm a student and I usually find my way to solve this kind of problems by doing some Google searches, but now I'm completely lost.
I have two tables in a database:
PERSONS PHONES
+----+-------+ +-----------+----------+
| ID | Name | | Person_ID | Phone |
+----+-------+ +-----------+----------+
| 1 | John | | 1 | 47281923 |
| 2 | Paul | | 1 | 92145694 |
| 3 | Chris | | 2 | 12345678 |
+----+-------+ | 3 | 83929171 |
+-----------+----------+
So, in order to get all phones from all persons, I have to execute this sentence:
SELECT name, phone FROM Persons, Phones WHERE Persons.ID = Phones.Person_ID
So the output of that SQL query should be:
+-------+----------+
| Name | Phone |
+-------+----------+
| John | 47281923 |
| John | 92145694 |
| Paul | 12345678 |
| Chris | 83929171 |
+-------+----------+
So far, so good. The problem is that I'm showing that result in a DataGridView (VB.NET) and I want the phones to populate a ComboBox in a cell of the DataGridView, so the DataGridView would be like:
+-----+----------+------------------------------------------------+
| ROW | NAME | PHONE |
+-----+----------+------------------------------------------------+
| 1 | John | (ComboBox, with phones 47281923 and 92145694) |
| 2 | Paul | (ComboBox, with phone 12345678) |
| 3 | Chris | (ComboBox, with phone 83929171) |
+-----+----------+------------------------------------------------+
I know that I can create a DataGridViewComboBoxColumn, and assign a DataSource to that column, but that would be a DatasSource to the entire column and not each individual "Phone cell" of each row.
How can I achieve this? I'm using Visual Basic, using Windows Forms, working with ODBC and an Informix database to store/get the data.
EDIT 1:
I tried this, a modified version of one of the answers, so I can add data from a table instead of a list:
For i = 0 To dgvPersons.Rows.Count
'The cell with index 1 has the IDNumber of that person.
Dim PersonID As String = connection.ExecuteSelect("SELECT PersonID FROM Persons where IDNumber = '" + dgvPersons.Rows(i).Cells(1).Value.ToString() + "'").Rows(0)(0).ToString()
'It seems I need to convert the Phone to Varchar(20) in the query because the phone is a BigInt and I get an error if I do not convert it.
Dim PersonPhones = connection.ExecuteSelect("SELECT phone::varchar(20) phone from Phones where PersonID = '" + PersonID + "'")
setCellComboBoxItems(dgvClientes, i, 7, TelefonosCliente)
Next
Private Sub setCellComboBoxItems(dataGrid As DataGridView, rowIndex As Integer, colIndex As Integer, itemsToAdd As DataTable)
Dim dgvcbc As DataGridViewComboBoxCell = DirectCast(dataGrid.Rows(rowIndex).Cells(colIndex), DataGridViewComboBoxCell)
For Each row As DataRow In itemsToAdd.Rows
dgvcbc.Items.Add(row.Item(0))
Next
End Sub
When I do that, the program takes forever to load (I have 105 rows) and I have no items in the ComboBoxes, even when I click the ComboBox, it doesn't do anything.
The executeSelect function is in another class, I wrote it.
The "IDNumber" is a number that identifies the person. IDNumber is a Unique Key, but PersonID is a serial Primary Key. I show only the IDNumber in the DataGridView, but the IDNumber exists in the database table.
My DataGridView is already populated with attributes of the Persons table so the DataSource from the DataGridView itself is already set. I suppose I cannot change the contents of the Phone column if that's the case?
Maybe there is a way to create a binding source that allows the DataGridView to put the ComboBoxes automatically?
