Hi All,
I have a problem with a datagridview (7 columns) of which last 2 columns are combo boxes. The datagrid view was set in design view with column names same as a table called OccRecStaff but without a datasource. Although the datagrid view works perfectly for the first 5 columns which are text boxes, it wont display any data in the combo boxes. Clicking the combo boxes works and displays lists from tables Rotas and Rooms. Rooms data table has RoomID field whilst Rotas has RotaID field. Both these fields exist as fields in OccRecStaff table.

Anyone got any ideas? Code below.

Imports System.Data
Imports System.Data.OleDb
Imports System.Globalization.Calendar

Imports System.Xml
Imports System.IO
Imports System.Data.Common
Imports System

Imports Microsoft.Office.Interop
Imports System.Math
Public Class frmStaffRotasEdit
Dim cCnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\CCAdb.mdb;Persist Security Info=True;Mode=Share Deny None;")
Dim strCarer, sSname, sFname, sString, strSQL As String
Dim iCarerID, iRoomID As Integer
Dim sCarerID As String
Dim daCarer As New OleDb.OleDbDataAdapter("SELECT Carer.CarerID, Carer.sname + ' ' + Carer.fname AS Cname FROM Carer ORDER BY Carer.sname, Carer.fname;", cCnn)
Dim daOccRecRota As New OleDb.OleDbDataAdapter("SELECT * FROM OccRecStaff ORDER BY RoomID,DDate,CarerID;", cCnn)
Dim daRotas As New OleDb.OleDbDataAdapter("SELECT Rotas.RotaID, Rotas.RotaDesc, Rotas.TimeStart, Rotas.TimeEnd FROM Rotas ORDER BY Rotas.RotaID;", cCnn)

Dim daRooms As New OleDb.OleDbDataAdapter("SELECT Rooms.RoomID, Rooms.RoomNo, Rooms.RoomMove FROM Rooms ORDER BY Rooms.RoomID;", cCnn)
Dim ds As New DataSet
Dim dtCarer, dtOccRecRota, dtRotas, dtRooms As New DataTable

Dim sConnectString As String
Dim cmbRotas As OleDbCommandBuilder

Dim changes As DataSet

Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click

End Sub

Private Sub frmStaffRotasEdit_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If cCnn.State = ConnectionState.Closed Then
End If
daCarer.Fill(ds, "Carer")
dtCarer = ds.Tables("Carer")
cboCarer.DataSource = dtCarer
cboCarer.ValueMember = "CarerID"
cboCarer.DisplayMember = "Cname"
daRotas.Fill(ds, "Rotas")
dtRotas = ds.Tables("Rotas")
daRooms.Fill(ds, "Rooms")
dtRooms = ds.Tables("Rooms")
daOccRecRota.Fill(ds, "OccRecStaff")
dtOccRecRota = ds.Tables("OccRecStaff")
dgvOccRecRota.AutoGenerateColumns = False

dgvOccRecRota.DataSource = dtOccRecRota
dgvOccRecRota.Columns(0).DataPropertyName = "OccID"
dgvOccRecRota.Columns(1).DataPropertyName = "CarerID"
dgvOccRecRota.Columns(2).DataPropertyName = "DDate"
dgvOccRecRota.Columns(3).DataPropertyName = "StartTime"
dgvOccRecRota.Columns(3).DefaultCellStyle.Format = "HH:mm"
dgvOccRecRota.Columns(4).DataPropertyName = "EndTime"
dgvOccRecRota.Columns(4).DefaultCellStyle.Format = "HH:mm"
Dim cboCol5 As DataGridViewComboBoxColumn = dgvOccRecRota.Columns("RotaID")
Dim cboCol6 As DataGridViewComboBoxColumn = dgvOccRecRota.Columns("RoomID")

With cboCol5
.DataPropertyName = ("OccRecStaff.RotaID")
.DataSource = ds.Tables("Rotas")
.ValueMember = ("RotaID")
.DisplayMember = "RotaDesc"
'.ValueType = GetType(Integer)
.Visible = True
End With

With cboCol6
.DataPropertyName = "OccRecStaff.RoomID"
.DataSource = ds.Tables("Rooms")
.ValueMember = "RoomID"
.DisplayMember = "RoomNo"
'.ValueType = GetType(Integer)
.Visible = True
End With

Catch ex As Exception
MsgBox("Error:" & Err.Description)
End Try

End Sub

Private Sub cboCarer_DropDownClosed(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCarer.DropDownClosed
iCarerID = Me.cboCarer.SelectedValue

End Sub

Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click
'refresh datagridview with filter selection

End Sub

Private Sub dgvOccRecRota_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles dgvOccRecRota.DataError
MessageBox.Show("Error Occured " & e.Context.ToString())
If (e.Context = DataGridViewDataErrorContexts.Commit) Then
MessageBox.Show("Commit Error")
End If
If (e.Context = DataGridViewDataErrorContexts.CurrentCellChange) Then
MessageBox.Show("Cell Change")
End If
If (e.Context = DataGridViewDataErrorContexts.Parsing) Then
MessageBox.Show("Parsing Error")
End If
If (e.Context = DataGridViewDataErrorContexts.LeaveControl) Then
MessageBox.Show("Leave Control Error")
End If
If (TypeOf (e.Exception) Is ConstraintException) Then
Dim view As DataGridView = CType(sender, DataGridView)
view.Rows(e.RowIndex).ErrorText = "An error"
view.Rows(e.RowIndex).Cells(e.ColumnIndex).ErrorText = "an error"

e.ThrowException = False
End If

End Sub
End Class

I have eventually abandoned trying to use the datagridviewcombobox column to edit datagridview column table data. A shame because clicking on a datagrid view combo column would have been really neat. Found some comments on the internet which allude to VS2008 having some improvements in this area. I have VS2005. I've returned to using datagridviewtextbox columns with combo boxes outside the datagridview to filter datagridview contents. The DGV can now be used to view then delete or edit data as required.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.