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
– Evry1falls, Commented Jul 24, 2020 at 14:18
I see. So the e.ColumnIndex = 4 should be inside CellBeginEdit ? — GaroPpo
– GaroPpo, Commented Jul 24, 2020 at 14:21
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
– GaroPpo, Commented Jul 24, 2020 at 14:30

Caius Jard · Accepted Answer · 2020-07-25 04:28:25Z

0

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

edited Jul 25, 2020 at 4:28

answered Jul 24, 2020 at 15:47

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GaroPpo Over a year ago

Works perfectly. Thanks for the answer and the advice. really apreciate it.

GaroPpo Over a year ago

Is it possible to get the sum of hargatotal to label.text? I've been using CellValueChanged but it only works after i add 2 row on datagridview and the label only sum first row value. not include row 2 hargatotal value. If I add row no. 3 it will sum hargatotal Row 1 and Row 2

Caius Jard Over a year ago

Just bind the label text to that column of the datatable. Or if you mean that the label is to show the sum of all rows add another column to the datatable, set it's .Expression property to SUM([hargatotal]) or SUM([harga] * [jumlah]) and then bind the label to that new column. You keep thinking about data in terms of the UI controls; you should be thinking about it in terms of the datatable the data lives in.

GaroPpo Over a year ago

I already bind it to label but it only works if i add more than 1 column. If row 1 added, the label not show the sum but if i add row 2 it will sum the HargaTotal column but only HargaTotal Row 1.. If 1 Row added it Will not Sum HargaTotal If 2 Row added it will only sum Row 1 HargaTotal If 3 Row Added it will only sum Row 1 + Row 2 HargaTotal

GaroPpo Over a year ago

I tried this code CellValueChanged Dim sum As Integer = Convert.ToInt32(DataTabel.Compute("SUM(HargaTotal)", String.Empty)) lblGrandTotal.Text = sum

|

Collectives™ on Stack Overflow

VB.net Cell Value Return DBNull

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related