VB.Net binding datagridview Comboboxcolumn to datagridviewTextboxColumn

Question

I am loading data from two tables: institute and country. Institute has 3 columns: instId, name, countryId. And country has 2 columns: countryId, name where countryId is a foreign key from country table. I fill these two tables in dataset. I have datagridview and set its datasource to institute table in my dataset. I also create datagridviewcomboboxcolumn and bind it country table. Have a look to the following code:

    Public Class frmDGV

    Dim sqlConn As SqlConnection
    Dim dsOptions As DataSet
    Dim daInstitute As SqlDataAdapter
    Dim daAdapter As SqlDataAdapter
    Dim bsCountry As BindingSource

    Private Sub frmTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            dsOptions = New DataSet
            loadOptions()

            dgvInstitute.DataSource = dsOptions.Tables("institute")
            bsCountry = New BindingSource(dsOptions, "country")

            Dim col As New DataGridViewComboBoxColumn
            col.DataPropertyName = "countryName"
            col.HeaderText = "Country"
            col.Name = "cName"

            col.DataSource = bsCountry
            col.DisplayMember = "countryName"
            col.ValueMember = "countryId"

            dgvInstitute.Columns.Add(col)
            dgvInstitute.Columns(0).Width = 60
            dgvInstitute.Columns(1).Width = 200
            dgvInstitute.Columns(2).Width = 60
            dgvInstitute.Columns(3).Width = 120

        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
    End Sub

    Sub loadOptions()
        Dim sql As String

        Try
            sqlConn = New SqlConnection(connString)
            sqlConn.Open()

            sql = "select instId, name, countryId from institute"
            daInstitute = New SqlDataAdapter(sql, sqlConn)
            daInstitute.Fill(dsOptions, "institute")
            '----------------------------------------------------------------------

            sql = "select countryId, countryName from country"
            daAdapter = New SqlDataAdapter(sql, sqlConn)
            daAdapter.Fill(dsOptions, "country")
            '----------------------------------------------------------------------

            sqlConn.Close()
        Catch ex As Exception
            sqlConn.Close()
            MsgBox(Err.Description)
        End Try
    End Sub
End Class

How can I display the proper country name in the combobox based on the countryId in the datagridview using the binding techniques not using a loop? See the following picture: enter image description here

score 4 · Accepted Answer · answered Jan 13 '14 at 11:16

4

Change DataPropertyName for comboboxcolumn in your datagridview:

...
col.DataPropertyName = "countryId"
...

.DataPropertyName - is a column name from datagridview.DataSource,which you want to show in the current column.

answered Jan 13 '14 at 11:16

Fabio

31,528
4
33
72

Mr. Fabio, would you please send the full code based on the my code? Thanks – Hilal Al-Rajhi Jan 21 '14 at 17:33
@HilalAl-Rajhi, My opinion, that your code must work, only change one line as in my answer... – Fabio Jan 21 '14 at 19:11
Thank you Mr. Fabio, You are right. I just modified the code and pasted the line: `col.DataPropertyName = "countryId"` – Hilal Al-Rajhi Jan 24 '14 at 15:53
Thanks a lot for you brothers all, Hilal Al-Rajhi who asked this question in a clear and great way, and for "Fabio" who get a new very useful answer, Really millions thanks for you bros, I really got what I was looking for and I was think it is not possible, but all thanks to you all, again thankX ^_^ – MAMPRO Apr 28 '19 at 20:22

score 0 · Answer 2 · answered Jan 13 '14 at 10:36

0

To be honest im not sure if you can due to the way you have bound this.

Does the country column need to be in a ComboBox? You could just include the country name in your first query.

The only way i could really see of doing this would be something like

for(int i = 0; i<dgvInstitute.Rows.Count; i++)
{
    dgvInstitute.Rows[rowIndexYouWant].Cells["cName"].Value = dgvInstitute.Rows[rowIndexYouWant].Cells["countryId"].Value;

}

answered Jan 13 '14 at 10:36

JohnyHarkness

88
7

Yes, Country has to be a combobox for the purpose of enabling the user to change the country where the institute is located. I could use textbox and bind the two tables in an sql query but I want to learn new technique. But you suggestion works fine but not quite my need. – Hilal Al-Rajhi Jan 21 '14 at 17:22

VB.Net binding datagridview Comboboxcolumn to datagridviewTextboxColumn

2 Answers2

Linked