Question bound or unbound or Datagridview and combobox?

[geothom]

This is my first post here so hope I have posted correctly.
I am in the process of converting my windows form app from VB6 to vb.net (VS2012). One of my forms is a weekly work roster (or schedule) which was working fine using flexgrid. Now I am looking at Datagridview to replace the flexgrid. I have already successfully converted another form to use a bound Datagridview (no comboboxes involved). The column headings for the roster are:

Location, Start Time, End Time, Day 1, Day 2, ........ Day 7.
​

The grid is initially populated with the current roster (if exists). User also has two buttons: one to create new roster and other to save changes.
Other than Start Time and End Time, the values entered in each column are values based on other database (MS Access) tables: Location (for Location col.) and Staff Name (for each Day col).
But I need the user to have the ability to select ALL values (new or changes) from comboboxes.
My Roster database table structure reflects thecolumns of the gridview.
My question is:
Would it be best to use an unbound Datagridview (think I can manage all the code there)?
OR
Would you recommend to use a bound one, and if so, how do I incorporate the combos and still bind entries to a data table?
Thanks in advance

 

[jmcilhinney]

I can't see anything in there that suggests that unbound would be better than bound. You bind a combo box column in a DatagridView in exactly the same way as you do a regular ComboBox, i.e. set its DisplayMember, ValueMember and DataSource. The SelectedValue of the ComboBox control used to edit a cell corresponds to the Value of the cell. Here's one I prepared earlier:

Adding a ComboBox Column to a DataGridView

Just note that the grid will never create a combo box column for you automatically. You have to add them yourself, either in code or, preferably, in the designer, as demonstrated in that thread.

 

[geothom]

G'day mate! Thanks for your very valued response. Your responses to many other posts have greatly assisted me in finding my feet with the amazing features of vb/ado.net. I will proceed as per your recommendation. Keep up the good work (from another Aussie).

 

[geothom]

Problem adding second and subsequent combo columns

JM your help again pls. I have successfully added one comboboxcolumn (Location) but it is the only one I can get to load without errors. I have tried to add a second (and more) combo cols. but always get the following error: "System.FormatException, DataGridViewComboBoxCellValue is not valid" - This error is on load and any time I hover over the second (Start) combo col. The Location column works fine. Correct values (as held in the Roster database table) are being displayed so all the bindings seems ok. I have included my code (stripped down for testing this) - sorry if this is not the correct way to include code.

Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Imports System.Reflection
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms

Friend Class Form5
    Inherits System.Windows.Forms.Form
    ' ------------------------  Maintenance Log -------------------------------------
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '    Date    ! Defect# !          Description of Change
    ''''''''''''''''''!''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '---------------------------------------------------------------------------------------------------!
    Public Event DataError As DataGridViewDataErrorEventHandler

    Private OleDBConn As New OleDbConnection
    Private objDA As New OleDbDataAdapter(strSQL, OleDBConn)
    Private objCB As New OleDbCommandBuilder(objDA)

    '---------------------------------------------------------------------------------------------------!
    Private Sub Form5_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
        '---------------------------------------------------------------------------------------------------!
        TransPosition = "Start"

        OleDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & GetAppPath() & DB
        OleDBConn.Open()

        Me.CenterToScreen()
        CurrentForm = Me
        mblnFormActivated = False

        Me.bsrcLocation.DataSource = Me.GetLocationData
        Me.colLocation.DisplayMember = "WorkLoc" ' Column name of database table " WorkLocation"
        Me.colLocation.ValueMember = "WorkLoc"
        Me.colLocation.DataSource = Me.bsrcLocation

        Me.bsrcTime.DataSource = Me.GetTimeData
        Me.colStart.DisplayMember = "TimeName"
        Me.colStart.ValueMember = "TimeID"
        Me.colStart.DataSource = Me.bsrcTime

        Me.bsrcRosterGrid.DataSource = Me.GetRosterData
        Me.DataGridView1.DataSource = Me.bsrcRosterGrid

        Me.DataGridView1.Columns(0).Visible = False
        Me.DataGridView1.Columns(1).Visible = False

        Exit Sub

    End Sub
    '--------------------------------------------------------------------------------
    Private Sub Form5_Activated(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles Me.Activated
        '--------------------------------------------------------------------------------
        If mblnFormActivated = True Then
            Exit Sub
        End If
        On Error GoTo LocalError
        TransPosition = "Start"
        mblnFormActivated = True

        Exit Sub

LocalError:
        MsgBox("Form5 FaiLED - Form_Activate: " & TransPosition & ":" & Err.Number & "-" & Err.Description & " ** PLEASE NOTE DETAILS OF THIS ERROR AND REPORT IT - ASAP **", MsgBoxStyle.Critical)
        If TransState = True Then
            mobjConn.RollbackTrans()
            TransState = False
        End If

    End Sub

    '--------------------------------------------------------------------------------
    Private Function GetNameData() As DataTable
        '--------------------------------------------------------------------------------
        Dim table As New DataTable

        Try
            strSQL = "SELECT ID, StaffName FROM Staff Where Type = 2 AND (Status = 1  "
            strSQL = strSQL & "OR (Status = 2 AND EndDate > DateLastPaid))ORDER By StaffName"

            '  Now we fill the DataTable
            Me.objDA = New OleDbDataAdapter(strSQL, OleDBConn.ConnectionString)
            Dim cmdBldr As New OleDbCommandBuilder(objDA)
            objDA.Fill(table)
        Catch ex As Exception
            MsgBox("GetNameData - An exception of type " & ex.GetType().ToString() & _
                                 " was encountered.")

        End Try
        Return table

    End Function
    '--------------------------------------------------------------------------------
    Private Function GetLocationData() As DataTable
        '--------------------------------------------------------------------------------
        Dim table As New DataTable

        Try
            strSQL = "SELECT ID, WorkLoc FROM WorkLocation Where Status = 1 ORDER By WorkLoc"

            '  Now we fill the DataTable
            Me.objDA = New OleDbDataAdapter(strSQL, OleDBConn.ConnectionString)
            Dim cmdBldr As New OleDbCommandBuilder(objDA)
            objDA.Fill(table)
        Catch ex As Exception
            MsgBox("GetLocationData - An exception of type " & ex.GetType().ToString() & _
                                 " was encountered ")

        End Try
        Return table

    End Function
    '--------------------------------------------------------------------------------
    Private Function GetTimeData() As DataTable
        '--------------------------------------------------------------------------------
        Dim tblTime As New DataTable

        With tblTime.Columns
            .Add("TimeID", GetType(Integer))
            .Add("TimeName", GetType(String))
        End With

        With tblTime.Rows
            .Add(1, CStr("6:00 AM").ToString)
            .Add(2, CStr("6:30 AM").ToString)
        End With

        Return tblTime

    End Function
    '--------------------------------------------------------------------------------
    Private Function GetRosterData() As DataTable
        '--------------------------------------------------------------------------------
        Dim table As New DataTable

        CurrentRosterID = 6   ' ### Temp code
        Try
            strSQL = "Select * FROM RosterDets"
            'strSQL = strSQL & " ORDER by Location"
            Me.objDA = New OleDbDataAdapter(strSQL, OleDBConn.ConnectionString)
            Dim cmdBldr As New OleDbCommandBuilder(objDA)
            objDA.Fill(table)
        Catch ex As Exception
            MsgBox("GetRosterData - An exception of type " & ex.GetType().ToString() & _
                                 " was encountered.")

        End Try
        Return table

    End Function

    Private Sub DataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs)

        MessageBox.Show("Error happened " _
            & 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

 

[jmcilhinney]

For future reference, that is not the correct way to post code but I have fixed it for you.

As for the issue, I have addressed the same issue here:

datagridview combobox binding Error ( system.format exception )

 

[geothom]

Thanks once more for your response jm. I am struggling with one combo column (colStart) the values of which are not sourced from a database table. What I need is a list of times (say 6:00 am to 11:00 am) in 30min intervals. The combo column itself is bound to the 'StartTime' column (stored as text) in the DataGridView. Everything I have tried comes up with the same error as posted above. I have tried populating a datatable with the values and also setting the values in design using 'Items'. Obviously I am missing something very basic - can't see the wood for the trees!!

 

[jmcilhinney]

Whatever you expect to be assigned to the Value property of the cell is what must be returned by the SelectedValue of the editing ComboBox, e.g.

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim startTime = #6:00:00 AM#

        Me.TimeColumn.DataSource = Enumerable.Range(0, 11).
                                              Select(Function(i) startTime.AddMinutes(i * 30).ToShortTimeString()).
                                              ToArray()

        Dim table As New DataTable

        With table.Columns
            .Add("Name", GetType(String))
            .Add("Time", GetType(String))
        End With

        With table.Rows
            .Add("Peter", "10:30 AM")
            .Add("Paul", "7:00 AM")
            .Add("Mary", "8:30 AM")
        End With

        Me.DataGridView1.DataSource = table
    End Sub

End Class

Also, any value that is already assigned to a Value property of a cell must be able to be assigned to the SelectedValue property of an editing ComboBox, e.g. if you only have "6:00 AM" and "6:30 AM" in the drop-down list then you cannot have "6:15 AM" in the data.

 

[geothom]

Thanks yet again!!!!
Note to anyone else reading this: All the advice given to me here is good and sound!!! After implementing all of the advice, I still had the same problem!!! I created a new form and started from scratch implementing all of the advice above and it worked just fine. Seems after so many deletes/adds/changes to my original form maybe something got corrupt. So if you are having similar problems - 1st, read the advice given carefully and 2nd, try it in a fresh environment step-by-step.
Now I can go forward with my conversion from VB6 thanks to JM.