Question combox on datgridview not matching column

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
148
Programming Experience
1-3
Hi I have a Combobox on a dataviewgrid on a form. The DataGridView is tied to my customers table and shows the records alright and has a County column.
The Combox is tied to my County Table and consists of an ID column and County column and is working well.

But I can't get the combox to replace the County Column in the Customers Table in the Datagridview.

I am using a sqlconnection to ms sql database and take the two tables into a dataset, then link using the Column1.bindingsource=mydataset.Tables("County")

Any help appreciated. I don't have the code to hand at the moment but will post it tomorrow.
 
Last edited:

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
148
Programming Experience
1-3
Hi jm, here is my code...TblCounnty, ID PK int, County nvarchar(50), TblClient also has County column as nvarchar(50)!


Imports System.Data
Imports System.Data.SqlClient


Module GlobalVariables
'Declare Global Variables & Constansts
Public connStr As String = "...SQLEXPRESS;Database=*******;Trusted_Connection=yes;"
End Module

Public Class Form1
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim bs As BindingSource





Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim conn As New SqlClient.SqlConnection(connStr)
Try
'Populate Combobox first !
cmd = New SqlCommand("SELECT * FROM TblCounty", conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet()
da.Fill(ds, "TblCounty")
conn.Open()
Me.BindingSource2.DataSource = ds.Tables("TblCounty")
Me.Column1.DataSource = Me.BindingSource2
Me.Column1.DisplayMember = "County"
Me.Column1.ValueMember = "CID"


Me.Column1.DataPropertyName = "CID"


da = Nothing
conn.Close()


'Populate DatGridView
cmd = New SqlCommand("SELECT * FROM TblClients", conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet()
da.Fill(ds, "TblClients")
'create new binding source
bs = New BindingSource()
conn.Open()
'DataGridView1.DataSource = ds.Tables("TblService").DefaultView
bs.DataSource = ds.Tables("TblCLients").DefaultView
'bind BindingSource to Bingding Navigator
Me.BindingNavigator1.BindingSource = bs
'bind DataGridView to BindingNavigator's BindingSource
Me.DataGridView1.DataSource = Me.BindingNavigator1.BindingSource




'Now order the combobox to where it shoud be !
For Each column As DataGridViewColumn In Me.DataGridView1.Columns
column.DisplayIndex = column.Index
Next


Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")


End Try


da = Nothing
'connStr = Nothing
conn.Close()
End Sub

Your help is apprecaited
 

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
148
Programming Experience
1-3
Amended code now shows Combobox in correct place, but not the correct values !

here is the amended code...TblCounnty, 'ID' PK int, 'County' nvarchar(50), TblClient also has 'County' column as nvarchar(50)!

Works now shows Dropdown combobox in correct place but not the correct values from the TblCLients, for the COunty column !

see changes underlined !

Public Class Form1
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim bs As BindingSource





Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim conn As New SqlClient.SqlConnection(connStr)
Try
'Populate Combobox first !
cmd = New SqlCommand("SELECT * FROM TblCounty", conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet()
da.Fill(ds, "TblCounty")
conn.Open()
Me.BindingSource2.DataSource = ds.Tables("TblCounty")
Me.Column1.DataSource = Me.BindingSource2
Me.Column1.DisplayMember = "County"
Me.Column1.ValueMember = "ID"


Me.Column1.DataPropertyName = "ID"


da = Nothing
conn.Close()


'Populate DatGridView
cmd = New SqlCommand("SELECT * FROM TblClients", conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet()
da.Fill(ds, "TblClients")
'create new binding source
bs = New BindingSource()
conn.Open()
'DataGridView1.DataSource = ds.Tables("TblService").DefaultView
bs.DataSource = ds.Tables("TblCLients").DefaultView
'bind BindingSource to Bingding Navigator
Me.BindingNavigator1.BindingSource = bs
'bind DataGridView to BindingNavigator's BindingSource
Me.DataGridView1.DataSource = Me.BindingNavigator1.BindingSource




'Now order the combobox to where it shoud be !
For Each column As DataGridViewColumn In Me.DataGridView1.Columns
column.DisplayIndex = Me.Column1.Index
Next


Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")


End Try


da = Nothing
'connStr = Nothing
conn.Close()
End Sub

Your help is apprecaited[/QUOTE]
 

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
148
Programming Experience
1-3
Here is my dataset update command that no longer updates because the Combobox is in the DGV, I think !!
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click


Dim cB As SqlCommandBuilder = New SqlCommandBuilder(da)

da.UpdateCommand = cB.GetUpdateCommand()
da.Update(ds, "TblClients")
 
Top Bottom