Member Avatar for aarthi anand 
    Dim total As Integer
        For Each row As DataGridViewRow In DataGridView1.Rows
            total += row.Cells("Total").Value
        Next
        TextBox2.Text = total

   // the above was the code i used and it worked good,bt if i do it for another column it is showing an error (which is given below)//



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Anand\Documents\Database3.accdb"

        Dim SQLString As String = "SELECT Received,closed,Total,TRejects FROM cc"
        Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
        Dim DataSet1 As New DataSet()
        Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)
        OleDBConn1.Open()
        OleDbDataAdapter1.Fill(DataSet1, "cc")
        DataGridView1.DataSource = DataSet1.Tables("cc")

        Dim total As Integer
        For Each row As DataGridViewRow In DataGridView1.Rows
            total += row.Cells("Total").Value
        Next
        TextBox2.Text = total

        Dim trejects As Integer
        For Each row As DataGridViewRow In DataGridView1.Rows
            trejects += row.Cells("TRejects").Value
        Next
        TextBox1.Text = trejects

/>

    pl help me out..........
  • 3 Contributors
  • 6 Replies
  • 2K Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

is spot on: that exception happens when one of the cells contains DBNull. Two quick ways to make this robust, without fighting the grid.

  1. Let Access do the math and return clean numbers (recommended). Nz turns NULL into 0, so you never add DBNull to an Integer.
Using con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Anand\Documents\Database3.accdb")
    Using cmd As New OleDb.OleDbCommand(
        "SELECT Sum(Nz([Total],0)) AS SumTotal, Sum(Nz([TRejects],0)) AS SumRejects FROM cc;", con)
        con.Open()
        Using r = cmd.ExecuteReader()
            If r.Read() Then
                TextBox2.Text = r("SumTotal").ToString()
                TextBox1.Text = r("SumRejects").ToString()
            End If
        End Using
    End Using
End Using
  1. If you must sum what is displayed, skip the new row and coerce safely. Also note the VB function is IsDBNull, not IsDbNull.
  • Either turn off the extra row: DataGridView1.AllowUserToAddRows = False, or
  • Skip it in code: If row.IsNewRow Then Continue For.
  • For each value: check value Is Nothing OrElse value Is DBNull.Value, and only then parse with Integer.TryParse.

A neat alternative that avoids loops: if your grid is bound to a DataTable, let ADO.NET aggregate for you (handles NULLs automatically).

Dim dt = TryCast(DataGridView1.DataSource, DataTable)
If dt IsNot Nothing Then
    TextBox2.Text = dt.Compute("Sum(Total)", Nothing).ToString()
    TextBox1.Text = dt.Compute("Sum(TRejects)", Nothing).ToString()
End If

removing + will not fix a DBNull; the key is to convert NULLs to 0 before adding. Also consider Option Strict On to catch these at compile time.

Recommended Answers

All 6 Replies

Member Avatar for pritaeas

it is showing an error

What error exactly?

Member Avatar for aarthi anand

Invalid CastException Was Unhandled
Operator '+' is not defined for type 'Integer' and type 'DBNull'

Member Avatar for pritaeas

One of the records contains a NULL value. Before adding, check if it's NULL and only add if it's not.

Member Avatar for aarthi anand

how am i supposed to check it???!

Member Avatar for malibumax

In lines 3, 24, and 30 did you try taking out the "+" sign? Also, did you check to see if you have any missing or broken references?

Member Avatar for pritaeas

how am i supposed to check it???!

If NOT IsDbNull(row.Cells("Total")) 
    total += row.Cells("Total").Value
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.