VB.net Cell Value Return DBNull

Question

I have an DataGridView with custom DataTable (not SQL database). where my coding like this

Sub Tabel()
        Tabel1 = New DataTable

        With Tabel1
            .Columns.Add("kodebarang")
            .Columns.Add("namabarang")
            .Columns.Add("satuan")
            .Columns.Add("harga", GetType(Double))
            .Columns.Add("jumlah", GetType(Integer))
            .Columns.Add("hargatotal", GetType(Double))
        End With

        DataGridView1.DataSource = Tabel1

        With DataGridView1
            .Columns(0).HeaderText = "ID"
            .Columns(1).HeaderText = "Item Name"
            .Columns(2).HeaderText = "Unit"
            .Columns(3).HeaderText = "Unit Price"
            .Columns(4).HeaderText = "Qty"
            .Columns(5).HeaderText = "Total Price"

            .Columns(3).DefaultCellStyle.Format = "###,###,###"
            .Columns(5).DefaultCellStyle.Format = "###,###,###"

            .Columns(1).ReadOnly = True
            .Columns(2).ReadOnly = True
            .Columns(3).ReadOnly = True
            .Columns(5).ReadOnly = True

            .Columns(3).DefaultCellStyle.BackColor = Color.LightBlue
            .Columns(4).DefaultCellStyle.BackColor = Color.LightGray

            .Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            .Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            .Columns(1).HeaderCell.Style.BackColor = Color.LightBlue

            .Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(3).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(4).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(5).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter

            .Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        End With

        For i = 0 To DataGridView1.Columns.Count - 1
            DataGridView1.Columns.Item(i).SortMode = DataGridViewColumnSortMode.Programmatic
        Next i
    End Sub

and I use CellEndEdit to change datagrid cell value.

Private Sub DataGridView_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        celWasEndEdit = DataGridView1(e.ColumnIndex, e.RowIndex)

        txtNamaBarang.Text = DataGridView1.Rows.Count - 1

        If e.ColumnIndex = 0 Then
            Call Connect()

            Sql = "SELECT * FROM databarang WHERE kodebarang='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
            Cmd = New OdbcCommand(Sql, Con)
            Read = Cmd.ExecuteReader

            While Read.Read()
                DataGridView1.Rows(e.RowIndex).Cells(1).Value = Read("namabarang")
                DataGridView1.Rows(e.RowIndex).Cells(2).Value = Read("satuan")
                DataGridView1.Rows(e.RowIndex).Cells(3).Value = Read("hargapartai")
            End While
        ElseIf e.ColumnIndex = 4 Then
            Dim Quantity As Integer = DataGridView1.Rows(e.RowIndex).Cells(4).Value
            Dim UnitPrice As Integer = DataGridView1.Rows(e.RowIndex).Cells(3).Value

            Dim TotalPrice As Integer = Quantity * UnitPrice
            DataGridView1.Rows(e.RowIndex).Cells(5).Value = TotalPrice
        End If
    End Sub

DataGridView Screenshot

but i got error when I change Column 4 (Qty) Value. Conversion from type 'DBNull' to type 'Integer' is not valid., but if change my code to

ElseIf e.ColumnIndex = 4 Then
            DataGridView1.Rows(e.RowIndex).Cells(3).Value, MsgBoxStyle.Information, "Error")
        End If

I can get the Column 3 Value in MsgBox.

In order to use CellEndEdit you must first use CellBeginEdit with this scenario of yours. Also, if you want to format the DataGridView, use CellFormatting — evry1falls
I see. So the e.ColumnIndex = 4 should be inside CellBeginEdit ? — GaroPpo
So i have 6 Column in total, where: Column 0 = ID Number, Column 1 = Item Name, Column 2 = Unit, Column 3 = Unit Price, Column 4 = Quantity, Column 5 = Total Price. What i need to do is Calculate Total Price Where if user Enter number ini Column 4, the Column 5 Value will show the result of Column 3 * Column 4. I add it on CellEndEdit, because i thought the command will be generate when it's EndEdit the cell — GaroPpo

Caius Jard Caius Jard · Accepted Answer · 2020-07-24T15:47:02

You don't need to do any of that if your DataGridView is bound to a DataTable; just set an expression on the total column that calculates the multiple of the other two columns:

    Tabel1 = New DataTable

    With Tabel1
        .Columns.Add("kodebarang")
        .Columns.Add("namabarang")
        .Columns.Add("satuan")
        .Columns.Add("harga", GetType(Double))
        .Columns.Add("jumlah", GetType(Integer))
        Dim dc = .Columns.Add("hargatotal", GetType(Double))   ''changed line
        dc.Expression = "[harga] * [jumlah]"                   ''new line
    End With

    DataGridView1.DataSource = Tabel1

On a related note, you shouldn't be accessing data via the datagridview; data lives in the datatable and should be accessed from there, not via the control that is displaying it

VB.net Cell Value Return DBNull

1 Answers