Member Avatar for simonfoley

Hi there

Another noob question from me...one of these days I'll get the hang of this VB!

I am importing data from an Access table that has four columns/fields. I have set up the relevant headings etc on the list view control...but I have also set up a 5th column as I want to have that representing the percentage change between columns 3 and 4 (which are numeric).

Here's my code. What I want to know, is that having confused myself with code I've written / adapted from advice given etc where do I put in a forumula to add something to a column in my list view that is a calculation based on two columns imported from the table?

Private Sub citydata_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dataCity As New DataTable()
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\globexdata\globex.mdb"
        Dim sqlString As String = "SELECT * FROM cities"
        Dim dataAdapt As New OleDb.OleDbDataAdapter(sqlString, connectionString)

        dataAdapt.Fill(dataCity)
        dataAdapt.Dispose()

        Dim qtyCols As Integer = dataCity.Columns.Count
        Dim qtyRows As Integer = dataCity.Rows.Count

        Dim itm As ListViewItem
        Dim Row As DataRow
        Dim str(qtyCols - 1) As String

        Dim i As Integer = 0
        Dim y As Integer

        While i <= qtyRows - 1
            y = 0
            Row = dataCity.Rows(i)
            While y <= qtyCols - 1
                If Len(Row(y).ToString) > 0 Then
                    str(y) = Row(y)
                Else
                    str(y) = "" 
                End If
                y = y + 1
            End While
            itm = New ListViewItem(str)
            lstViewpop.Items.Add(itm)
            i = i + 1
        End While
        

    End Sub
  • 3 Contributors
  • 2 Replies
  • 101 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by Member #857553

Dani AI

Generated

A few concise, practical options beyond the snippets already suggested by and .

One clean approach is to add a computed column to the DataTable itself so the percentage is available as a normal column when you iterate rows or bind. This offloads the arithmetic and null/zero handling to the DataTable expression engine and keeps the UI loop simple:

' replace [Col3] and [Col4] with your real column names
dataCity.Columns.Add("PercentChange", GetType(Double),
    "IIF(IsNull([Col4]) OR [Col4] = 0, NULL, ([Col3] / [Col4]) * 100)")

Another approach is to compute the value in the SELECT so the adapter returns the computed field directly. In Jet/Access SQL you can use IIf and Nz to protect against null/zero denominators, e.g.:

SELECT *, IIf(Nz([Col4],0)=0, NULL, ([Col3]/[Col4])*100) AS PercentChange FROM cities;

If you keep the current item-by-item ListView population, follow these practical rules: parse numeric text with TryParse (avoid Val/CStr conversions that hide errors), explicitly handle DBNull and zero denominators, format the result for display (fixed decimals and a percent sign if desired), call ListView.BeginUpdate/EndUpdate while adding many items, and clear the Items collection before repopulating. For larger datasets consider binding to a DataGridView or using ListView virtual mode for better performance.

For reference on DataTable computed columns and the expression syntax see the DataColumn.Expression docs and for manipulating subitems in a WinForms ListView see ListViewItem.SubItems. These help make the computed value robust, avoid division-by-zero and localization issues, and keep your UI code tidy.

Recommended Answers

All 2 Replies

Member Avatar for lolafuertes

Just some hints:
If you want one additional column then change to

Dim str(qtyCols) As String

Then, before creating the ListViewitem, add the calculation.

str(qtyCols) = (Microsoft.VisualBasic.Val(str(qtyCols-2))/Microsoft.VisualBasic.Val(str(qtyCols-1)) * 100).ToString

or whatelse calculation you need

Hope this helps.

Member Avatar for Member #857553

I shortened your code up a little. Turned lines 10 - 34 into 3 lines.

Dim dataCity As New DataTable()
 Dim connectionString As String = "......."
 Dim sqlString As String = "SELECT COALESCE(c1,'')," & _
                                  "COALESCE(c2,'')," & _
                                  "COALESCE(c3,'')," & _
                                  "COALESCE(c4,'')  FROM cities"
 Dim dataAdapt As New System.Data.SqlServerCe.SqlCeDataAdapter(sqlString, connectionString)
 dataAdapt.Fill(dataCity)
 dataAdapt.Dispose()

 For Each R As DataRow In dataCity.Rows
      lstviewpop.Items.Add(New ListViewItem(R.ItemArray.Cast(Of String).ToArray))
 Next

I used 4 columns c1,c2,c3,c4

You'll notice the coalesce in the select statement. That converts a null value to an empty string. If your columns are not nullable then you don't need it.

The code iterates through each Row in the table and uses its ItemArray property to add it to the list view. The ItemArray is an array of Object so it must be changed to an Array of String, which is what the .Cast(of String) is doing.


To add an additional column with a calculation you do what the above poster said.

Swap out the For each statement with this 1

For Each R As DataRow In dataCity.Rows
      lstviewpop.Items.Add(New ListViewItem(R.ItemArray.Cast(Of String).ToArray))
      With lstviewpop.Items(lstviewpop.Items.Count - 1)
          If .SubItems.Count = 4 Then
              If IsNumeric(.SubItems(2).Text) _
                 AndAlso IsNumeric(.SubItems(3).Text) _
                 AndAlso .SubItems(3).Text > 0 Then
      
                .SubItems.Add(CStr(.SubItems(2).Text / .SubItems(3).Text))
              End If
          End If
      End With
  Next
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.