Member Avatar for Papa_Don

Group,

I've written some code to populate a listview using 3 fields from a database. When creating the listview, I named the individual columns for appearance purposes, (Account No, Company Name and Name). Those columns are being populated appropriately as expected. However data column names are being shown in column 4 5 and 6, but with no data. What in my code is causing this? Any suggestions on how to fix this?

My code is as follows:

Private Sub tbxCompanyName_Leave(sender As Object, e As EventArgs) Handles tbxCompanyName.Leave
    lvwSearchCustomer.Visible = True
    Me.lvwSearchCustomer.View = View.Details
    Me.lvwSearchCustomer.GridLines = True
    Dim strQ As String = String.Empty
    Dim datasource As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"
    conn = New SqlConnection(datasource)
    Dim searchID As String = tbxCompanyName.Text

    strQ = "SELECT CUST_ACCT_NO, 
            CUST_COMPANY_NAME,
            CONCAT(CUST_FIRST_NAME,' ',CUST_MIDDLE_INITIAL,' ',CUST_LAST_NAME) as MailingName    
            FROM CUSTREC 
            WHERE CUST_COMPANY_NAME LIKE '" & searchID & "%' OR CUST_LAST_NAME LIKE '" & searchID & "%'"

    cmd = New SqlCommand(strQ, conn)
    da = New SqlDataAdapter(cmd)
    ds = New DataSet
    da.Fill(ds, "Tables")
    Dim i As Integer = 0
    Dim j As Integer = 0
    ' adding the columns in ListView
    For i = 0 To ds.Tables(0).Columns.Count - 1
        Me.lvwSearchCustomer.Columns.Add(ds.Tables(0).Columns(i).ColumnName.ToString())
    Next
    'Now adding the Items in Listview
    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To ds.Tables(0).Columns.Count - 1
            itemcoll(j) = ds.Tables(0).Rows(i)(j).ToString()
        Next
        Dim lvi As New ListViewItem(itemcoll)
        Me.lvwSearchCustomer.Items.Add(lvi)
    Next

End Sub

In advance, thanks for your assistance.

Don

  • 2 Contributors
  • 2 Replies
  • 3K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by xrjf

Dani AI

Generated

You are seeing headers twice because the control has six columns at runtime: three you created in the designer (Account No, Company Name, Name) plus three you add in code from the DataTable’s column names. Since each ListViewItem you build only has three values, columns 4-6 remain empty except for their headers. That also explains why the extra headers show the data column names. As hinted, relying on an array like itemcoll can hide this mismatch.

Two simple fixes:

  • Either add the columns once at design time and do not add them again from the DataSet.
  • Or clear and rebuild the columns each time you populate the control, then add items to match exactly those columns.

Example approach (avoids the itemcoll array entirely and prevents re-adding):

lvwSearchCustomer.BeginUpdate()
lvwSearchCustomer.Items.Clear()
lvwSearchCustomer.Columns.Clear()

lvwSearchCustomer.Columns.Add("Account No")
lvwSearchCustomer.Columns.Add("Company Name")
lvwSearchCustomer.Columns.Add("Name")

For Each r As DataRow In ds.Tables(0).Rows
    Dim it = New ListViewItem(CStr(r("CUST_ACCT_NO")))
    it.SubItems.Add(CStr(r("CUST_COMPANY_NAME")))
    it.SubItems.Add(CStr(r("MailingName")))
    lvwSearchCustomer.Items.Add(it)
Next

lvwSearchCustomer.EndUpdate()

Extra tips:

  • If this code can run multiple times (e.g., on Leave), you must clear Items and Columns as shown, or add columns only once with If lvwSearchCustomer.Columns.Count = 0 Then ....
  • Keep using CONCAT(...) if you need NULL-safe name building; with +, any NULL will blank the whole name.
  • Parameterize the LIKE filter to handle quotes and avoid injection: pass @search with searchID & "%".

Recommended Answers

All 2 Replies

Member Avatar for Papa_Don

FYI..... In attempting to fix this, I commented out a few lines to make it more visual. I failed to remove the comment marks before posting this.

I hope this clears any questions you might have.

Don

Member Avatar for xrjf

One question: where do you define itemcoll?

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.