Hilal Al-Rajhi Hilal Al-Rajhi - 5 months ago 40
Vb.net Question

VB.Net binding datagridview Comboboxcolumn to datagridviewTextboxColumn

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

Answer

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.