VB 2008 DataGridView ComboBoxCell

Posted on 2009-12-17
Last Modified: 2013-12-26

This one is really bothering me, This was working absolutely perfectly for ages, then i came to test something that required me to do this process beforehand and got errors. As far as i can think there has been no change to this form of any relevance, please scan the code below, here is what i am doing.

The first sub loads the Datagrid, replacing some columns with Combobox columns in the process.

The second sub assigns the tag property of the ItemType comboboxcell upon selection.

The third sub sets the datasource for the second comboboxcell based on the now populated tag of the first.

I am getting a system.FormatException, but as i say it was working perfectly for ages. Is this a bug????

Public Sub dgvNewJobListrefresh(ByRef statusView As DataGridView)
        Call ConnectDatabase()
        Dim myCommand As New MySqlCommand()
        Dim cboitem As New DataGridViewComboBoxColumn
        Dim cbostock As New DataGridViewComboBoxColumn
        Dim cbovat As New DataGridViewComboBoxColumn
        Dim builder As MySqlCommandBuilder = New MySqlCommandBuilder(myAdapter)
        Dim myItemCommand As New MySqlCommand()
        Dim myItemAdapter As New MySqlDataAdapter
        Dim myItemData As New DataTable
        Dim myStockCommand As New MySqlCommand()
        Dim myStockAdapter As New MySqlDataAdapter
        Dim myStockData As New DataTable
        Dim myVatCommand As New MySqlCommand()
        Dim myVatAdapter As New MySqlDataAdapter
        Dim myVatData As New DataTable
        Dim indType As Integer
        Dim indStock As Integer
        Dim indVAT As Integer
        Dim SQL = "SELECT ItemType, ItemStock, VATCode, " _
                & "JobID, ItemNotes As 'Notes', ItemPP As 'PP', " _
                & "ItemFile As 'File Name', ItemUnitPrice As 'Unit', " _
                & "ItemVAT AS 'Item VAT', ItemPrice As 'Line Total(net)' " _
                & "FROM tbljobsitems " _
                & "WHERE JobID is null"

        myCommand.Connection = conn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand

        myItemCommand.Connection = conn
        myItemCommand.CommandText = "SELECT ItemType, ItemTypeID, StockType " _
        & "FROM restblitemtypes " _
        & "WHERE TempID = " & Template

        myItemAdapter.SelectCommand = myItemCommand

        myStockCommand.Connection = conn
        myStockCommand.CommandText = "SELECT StockName, StockID " _
        & "FROM restblstocks " _
        & "WHERE CatID = 1"

        myStockAdapter.SelectCommand = myStockCommand

        myVatCommand.Connection = conn
        myVatCommand.CommandText = "SELECT VatPerc, VatID " _
        & "FROM restblvat "

        myVatAdapter.SelectCommand = myVatCommand

        dgvNewJob.DataSource = myData
        'dgvNewJob.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells'
        dgvNewJob.Columns(3).Visible = False

        indVAT = dgvNewJob.Columns.IndexOf(dgvNewJob.Columns("VATCode"))
        indStock = dgvNewJob.Columns.IndexOf(dgvNewJob.Columns("ItemStock"))
        indType = dgvNewJob.Columns.IndexOf(dgvNewJob.Columns("ItemType"))


        cbostock.Name = "Stock"
        cbostock.DataPropertyName = "ItemStock"
        cbostock.DataSource = myStockData
        cbostock.ValueMember = "StockID"
        cbostock.DisplayMember = "StockName"
        cbostock.DropDownWidth = 200

        cboitem.Name = "Item Type"
        cboitem.DataPropertyName = "ItemType"
        cboitem.DataSource = myItemData
        cboitem.ValueMember = "ItemTypeID"
        cboitem.DisplayMember = "ItemType"
        cboitem.DropDownWidth = 150
        cboitem.Tag = myItemData.Columns(2)

        cbovat.Name = "VAT"
        cbovat.DataPropertyName = "VATCode"
        cbovat.DataSource = myVatData
        cbovat.ValueMember = "VatID"
        cbovat.DisplayMember = "VatPerc"

        dgvNewJob.Columns.Insert(indType, cboitem)
        dgvNewJob.Columns.Insert(indStock, cbostock)
        dgvNewJob.Columns.Insert(indVAT, cbovat)

    End Sub

Private Sub dgvNewJob_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvNewJob.CellValueChanged
        cboTemplate.Enabled = False
        If e.ColumnIndex = 0 Then
            Dim mySTCommand As New MySqlCommand()
            Dim mySTAdapter As New MySqlDataAdapter
            Dim mySTData As New DataTable
            Dim mySTReader As MySqlDataReader
            Dim TypeValue As Integer
            Dim TagVal As Integer
            Dim TypeCell As DataGridViewComboBoxCell
            TypeCell = dgvNewJob.Rows(e.RowIndex).Cells(0)
            TypeValue = dgvNewJob.Rows(e.RowIndex).Cells(0).Value
            mySTCommand.Connection = conn
            mySTCommand.CommandText = "SELECT StockType " _
            & "FROM restblitemtypes " _
            & "WHERE ItemTypeID = " & TypeValue
            Call ConnectDatabase()
            mySTReader = mySTCommand.ExecuteReader()

            While mySTReader.Read()
                TagVal = mySTReader.Item("StockType").ToString
                TypeCell.Tag = TagVal
            End While
        End If
    End Sub

Private Sub dgvNewJob_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvNewJob.CellClick
        If e.ColumnIndex = 1 Then
            Dim TypeCell As DataGridViewComboBoxCell = dgvNewJob.CurrentRow.Cells(0)
            Dim StockCell As DataGridViewComboBoxCell = dgvNewJob.CurrentRow.Cells(e.ColumnIndex)
            Dim myStockCommand As New MySqlCommand()
            Dim myStockAdapter As New MySqlDataAdapter
            Dim myStockData As New DataTable

            'MessageBox.Show("Row = " & e.RowIndex & ", Column = " & e.ColumnIndex & " Tag = " & TypeCell.Tag, "index.", MessageBoxButtons.OK)'
            myStockCommand.Connection = conn
            myStockCommand.CommandText = "SELECT StockName, StockID " _
            & "FROM restblstocks " _
            & "WHERE StockType = " & TypeCell.Tag.ToString()

            myStockAdapter.SelectCommand = myStockCommand

            StockCell.DataSource = myStockData
            StockCell.ValueMember = "StockID"
            StockCell.DisplayMember = "StockName"
        End If
    End Sub

Open in new window

Question by:Aaronroach
    LVL 15

    Accepted Solution

    system.FormatException says that any declared numeric / Date (Iformatable) column, has´nt the desired value type.
    or the format mask is´nt valid for the data.
    Has changed you DataType for any column on DataBase since DatagridView Design?
    Also if you declare a column as String in Database and always use numbers, and on DGV you declare it as numeric, a data mithcmach can produce the error.
    LVL 5

    Author Closing Comment

    You my son are a genius, thanks, problem solved.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now