Formula in list view from imported data in other columns

[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

[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.

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