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.
Thanks
Waldek
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
Me.Close()
End Sub
Private Sub frmStaffRotasEdit_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Try
If cCnn.State = ConnectionState.Closed Then
cCnn.Open()
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")
'dgvOccRecRota
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
cCnn.Close()
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
MsgBox(iCarerID)
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