Load data from sql to a list view VB

To load your ListView using your SQL database , you will first need to make a query ... and that same query, store it inside a DataTable object, so finally you only have to travel with a For Each (or any repetitive cycle that is more comfortable). Personally I prefer this to travel DataTables.

I'm going to use Microsoft Access as a database ... just do not worry about this, because the procedure to extract data is exactly the same.

Protected Friend Function SelectQuery(ByVal query As String) As DataTable
    Dim tabla As New DataTable
    con.Open()
Try
    comando = New OleDbCommand(query, con)
    adapter = New OleDbDataAdapter(comando)
    adapter.Fill(tabla)
    comando.Dispose()
    adapter.Dispose()
Catch ex As Exception
    MsgBox("Error en la consulta: " + ex.Message, MsgBoxStyle.Critical)
End Try
    con.Close()
    Return tabla
End Function

What this function consists of ... well, as you can see, this function is of type DataTable and will need a parameter that will be the query (String) to your database. data.

• First, the function declares an object of the DataTable class and opens the connection to the database.
• We then go inside a Try Catch . Initializing a class attribute called command, instance of the OleDbCommand class (which in your case, when using SQL, must be SqlCommand ), passing it as parameter our query and our connection.
• After that, we initialize another class attribute named adapter, instance of the OleDbDataAdapter class (which in your case, must be SqlDataAdapter ), passing it as parameter our OleDbCommand object.
• We use the Fill method, passing it our DataTable object as a parameter. Here our data filling adapter using the received query and will connect to your database thanks to the last connection.
• We remove objects (command / adapter) and free up memory with Dispose , since everyone's duty has ended.

Now we move on to the next step, which is very simple, when we go to request data for our ListView. As an example, I'm going to capture all this data from this table.

Public Class DemoClassListView
Dim admin As New Acceso
Private Sub DemoClassListView_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ListView1.View = View.Details

    ListView1.Columns.Add("Id_Cliente", 75, HorizontalAlignment.Center)
    ListView1.Columns.Add("Empresa", 150, HorizontalAlignment.Center)
    ListView1.Columns.Add("Direccion", 150, HorizontalAlignment.Center)
    ListView1.Columns.Add("Correo", 150, HorizontalAlignment.Center)

    For Each itm In admin.SelectQuery("SELECT Id_Cliente, Empresa, Direccion, Correo FROM Cliente").Rows
        Dim obj As New ListViewItem(itm(0).ToString)
        obj.SubItems.Add(itm(1).ToString)
        obj.SubItems.Add(itm(2).ToString)
        obj.SubItems.Add(itm(3).ToString)
        ListView1.Items.Add(obj)
    Next
End Sub
End Class

Note that we first had to build our ListView without data. And then proceed to tour our DataTable, I want you to pay close attention to this line here because it is key:

For Each itm In admin.SelectQuery("SELECT Id_Cliente, Empresa, Direccion, Correo FROM Cliente").Rows

This is the declaration of our cycle for each. Where we create a temporary object that will capture the data contained in the rows of our DataTable.

Within the cycle:

Dim obj As New ListViewItem(itm(0).ToString)
    obj.SubItems.Add(itm(1).ToString)
    obj.SubItems.Add(itm(2).ToString)
    obj.SubItems.Add(itm(3).ToString)
    ListView1.Items.Add(obj)

Since we have captured the row in our "temporary variable" itm , we will show you the column according to its index ... it works exactly like the fixes. The columns that you select in your query will always start from 0.

Customer_ID = 0, Company = 1, Address = 2, Mail = 4, ColumnQueSigue = 5 ... and so on.

Likewise, if you are too lazy to do it this way, you can simply write the name of each column :

Dim obj As New ListViewItem(itm("Id_Cliente").ToString)
        obj.SubItems.Add(itm("Empresa").ToString)
        obj.SubItems.Add(itm("Direccion").ToString)
        obj.SubItems.Add(itm("Correo").ToString)
        ListView1.Items.Add(obj)

And our final product:

The generally recommended, when working with this level of database, is to use DataGrids ... you would not even have the need to build the columns ... you can select the columns you want. Make a JOIN to your liking without having to add columns before ... for example ...