Populating Listview from SQL Database

[Papa_Don]

Hi group,

I've been playing with a ListView and I'm struggling to get it to populated with info in a database. I'm hoping you may see something in my code that is creating the error.

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim rdr As SqlDataReader = cmd.ExecuteReader

        con.ConnectionString = ("Data Source=DON-PC;Initial Catalog=DataDesignSolutions;Integrated Security=True;User ID=DON-PC;Password=be67011;")
        Dim command As New SqlCommand("", con)
        command.CommandText = "SELECT * FROM PRINTERMAINT"
        lvPrinterSetup.Items.Clear()
        con.Open()
        rdr = cmd.ExecuteReader
        Do While rdr.Read()
            lvPrinterSetup.Items.Add(New ListViewItem({rdr(0), rdr(1), rdr(2), rdr(3), rdr(4)}))
        Loop
        rdr.Close()
        con.Close()

To clarify, there are 5 columns in this data table (PRINTERMAINT). They are "PrinterID", "PrinterName", "PrinterNumber", "PrinterLocation" and "PrinterAddress".

Currently, I've attempted to push up at least 3 records. While I've not confirmed they are there, I believe they are.

If you see something that may be creating the failure, please speak up. I'm not getting any kind of error message. I'm just not getting any data to display in the ListView.

Thanks for your help.

Don

[Begginnerdev]

Is the listview set to view.Details?

You can set this in the designer, and in the code.

As far as loading data goes, the code snippet looks fine to me.

I do not use readers as much though. I use DataSets/DataAdapters.

You can do the same with them by:

Dim da As New SQLDataAdapter(cmd)
Dim ds As new DataSet

da.Fill(ds,"MyData")

IF IsNothing(ds.Tables("MyData")) = False And ds.Tables("MyData").Rows.Count > 0 Then

    For i = 0 to ds.Tables("MyData").Rows.Count - 1
        With ds.Tables("MyData")
            Dim lvi As New ListViewItem

            lvi.Text = .Rows(i)("PrinterID")
            lvi.SubItems.Add(.Rows(i)("PrinterName"))
            lvi.SubItems.Add(.Rows(i)("PrinterNumber"))
            lvi.SubItems.Add(.Rows(i)("PrinterLocation"))
            lvi.SubItems.Add(.Rows(i)("PrinterAddress"))

            ListView1.Items.Add(lvi)
        End With
    Next
End If

[Papa_Don]

Begginnerdev,

Your response prompts a question: Are there advantages or disadvantages to your method? Truthfully, I just want a good, efficient way of handling the information.

With regard to writing and including your actual code suggestion, should it be written like this?

Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim da As New SQLDataAdapter(cmd)
        Dim ds As new DataSet

        con.ConnectionString = ("Data Source=DON-PC;Initial Catalog=DataDesignSolutions;Integrated Security=True;User ID=DON-PC;Password=be67011;")
        Dim com As New SqlCommand("", con)
        command.CommandText = "SELECT * FROM PRINTERMAINT"
        lvPrinterSetup.Items.Clear()
        con.Open()

da.Fill(ds,"MyData")
IF IsNothing(ds.Tables("MyData")) = False And ds.Tables("MyData").Rows.Count > 0 Then
    For i = 0 to ds.Tables("MyData").Rows.Count - 1
        With ds.Tables("MyData")
            Dim lvi As New ListViewItem
            lvi.Text = .Rows(i)("PrinterID")
            lvi.SubItems.Add(.Rows(i)("PrinterName"))
            lvi.SubItems.Add(.Rows(i)("PrinterNumber"))
            lvi.SubItems.Add(.Rows(i)("PrinterLocation"))
            lvi.SubItems.Add(.Rows(i)("PrinterAddress"))
            ListView1.Items.Add(lvi)
        End With
    Next
End If  
        rdr.Close()
        con.Close()

Let me know if you think I need to make any changes.

Don

[Begginnerdev]

Either was is just as effecient as the other. I just learned by using DataSets. It is a personal preference kind of thing.

The advantage a dataset has is that you can reference it and use it later for updating data.

[Papa_Don]

Begginnerdev,

I've written your suggestion as follows, but I'm still not getting data populating the ListView. Am I missing something?

Dim con As New SqlConnection
    Dim cmd As New SqlCommand

        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        con.ConnectionString = ("Data Source=DON-PC;Initial Catalog=DataDesignSolutions;Integrated Security=True;User ID=DON-PC;Password=be67011;")
        Dim com As New SqlCommand("", con)
        com.CommandText = "SELECT * FROM PRINTERMAINT"
        lvPrinterSetup.Items.Clear()
        con.Open()
        da.Fill(ds, "MyData")
        If IsNothing(ds.Tables("MyData")) = False And ds.Tables("MyData").Rows.Count > 0 Then
            For i = 0 To ds.Tables("MyData").Rows.Count - 1
                With ds.Tables("MyData")
                    Dim lvi As New ListViewItem
                    lvi.Text = .Rows(i)("PrinterID")
                    lvi.SubItems.Add(.Rows(i)("PrinterName"))
                    lvi.SubItems.Add(.Rows(i)("PrinterNumber"))
                    lvi.SubItems.Add(.Rows(i)("PrinterLocation"))
                    lvi.SubItems.Add(.Rows(i)("PrinterAddress"))
                    lvPrinterSetup.Items.Add(lvi)
                End With
            Next
        End If

Any thoughts?

Thanks so much for the help here. This has been a great learning experience.

Don

[Hazuan Nazri]

You can try like this, if i wrong please inform me

    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim da As SqlDataAdapter
    Dim ds As DataSet
    Dim itemcoll(100) As String
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.lvPrinterSetup.View = View.Details
        Me.lvPrinterSetup.GridLines = True
        conn = New SqlConnection("Data Source=DON-PC;Initial Catalog=DataDesignSolutions;Integrated Security=True;User ID=DON-PC;Password=be67011;")
        Dim strQ As String = String.Empty
        strQ = "SELECT * FROM PRINTERMAINT"
        cmd = New SqlCommand(strQ, conn)
        da = New SqlDataAdapter(cmd)
        ds = New DataSet
        da.Fill(ds, "PRINTERMAINT")
        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.lvPrinterSetup.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.lvPrinterSetup.Items.Add(lvi)
        Next
    End Sub

please vote me if i already fix your problem, or ask me if you got problem.
Thank You!!

[Begginnerdev]

Have you insured that the ListView is set to Details view, and has columns added to it?

If you have not added columns to it, try this:

For i = 0 To ds.Tables("MyData").Columns.Count
    'This will create a column with a default width of 100.
    lvPrinterSetup.Columns.Add(ds.Tables("MyData").Columns(i).ColumnName, 100)
Next

You also need to check for code that might be clearing the listview.

[Papa_Don]

Begginnerdev, I did define the Listview with "Details" and have already added columns.

AcHillieZ, I've removed

' adding the columns in ListView
        For i = 0 To ds.Tables(0).Columns.Count - 1
            Me.lvPrinterSetup.Columns.Add(ds.Tables(0).Columns(i).ColumnName.ToString())
        Next

Because I've defined these already in the Listview. The only question I have is: what do these lines do?

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)

The other part of this is the "itemcoll". What is its purpose? Since I've defined my listview with details and added columns, is this needed?

Thanks for the help. This is working well.

Don

[Begginnerdev]

The code snippet:

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.lvPrinterSetup.Items.Add(lvi)       
Next

Is loading values into a string array, then into a listview item, just like this:

For i = 0 to ds.Tables("MyData").Rows.Count - 1
    With ds.Tables("MyData")
        Dim lvi As New ListViewItem

        lvi.Text = .Rows(i)("PrinterID")
        lvi.SubItems.Add(.Rows(i)("PrinterName"))
        lvi.SubItems.Add(.Rows(i)("PrinterNumber"))
        lvi.SubItems.Add(.Rows(i)("PrinterLocation"))
        lvi.SubItems.Add(.Rows(i)("PrinterAddress"))

        ListView1.Items.Add(lvi)
    End With
Next

It just enumerates on the index, instead of referencing the column name.

[Begginnerdev]

The question is, are all columns from the database present in the list view?

ie: If you have more columns in the ListViewItem than that exists in the ListView - the data will not render.

[Papa_Don]

Begginnerdev,

Yes, all fields are shown. In this particular instance, I needed it to be shown.

I was asking about "ItemColl" because it was originally defined as Dim itemcoll(100) As String. Part of that question also has to do with (100). It's still unclear as to what that does as well. And further, is it needed?

Thanks again,

Don

[Hazuan Nazri]

its refer to your column item and it will count your row to show on listview.

if i wrong please inform me. Thank you!

[Papa_Don]

Hazuan Nazri

By the way, the code is working fine now. I have made 1 small addition:

lvPrinterSetup.Items.Clear()

I found as I was making new entries, it wasn't clearing and then adding the new item. Consequently I was getting the same information listed two or three times. This fixed that.

Thanks greatly for the help.

Don

[Hazuan Nazri]

no problem!! dont forget make mark this article as solve!! Thank you! hope we can work together again!