• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

VB 2008 DataGridView ComboBoxCell


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

1 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.
AaronroachAuthor Commented:
You my son are a genius, thanks, problem solved.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now