How retrive data from MS Access Table in List View : VB.NET

[Naveen2961]

Hi There,

I am trying to retrieve the data from MS Access Table to LIST-VIEW, its difficult for me to write the code as I am beginner,

Need help on this ..

Any suggestions, example or template ?

Thanks in Advance..

[Oxiegen]

You can put this method in a Module, and call it from the form containing the ListView.
I've commented on what's going on in the code.

''The listview is provided as a referenced argument
    Public Sub PopulateListViewFromAccess(ByRef lv As ListView)
        'Create a connection string to the MS Access database.
        'The first version is using the standard JET driver
        'The second version is using the upgraded ACE driver for Access 2007 and above
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\mydatabase.mdb; User Id=admin; password=;"
        'Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\mydatabase.accdb; Persist Security Info=False;"

        'Create a connection to the database
        Dim conn As New System.Data.OleDb.OleDbConnection(connectionString)
        Dim com As System.Data.OleDb.OleDbCommand
        Dim reader As System.Data.OleDb.OleDbDataReader

        Try
            'Open the connection
            conn.Open()
            'Create a new instance of the command and provide the SELECT query, and the opened connection
            com = New System.Data.OleDb.OleDbCommand("SELECT * FROM <table>", conn)
            reader = com.ExecuteReader(CommandBehavior.CloseConnection)

            'Check to see if the SELECT query returned any rows
            If reader.HasRows Then
                'If so, perform a read for each row
                While reader.Read
                    'Declare a new ListViewItem, and provide the information
                    'to be shown in the very first column
                    Dim item As New ListViewItem(reader.Item("<column1>").ToString)

                    'Decare a new ListViewSubItem, and provide the information
                    'to be shown in the second (and so forth) column
                    Dim subItem As New ListViewItem.ListViewSubItem()
                    subItem.Text = reader.Item("<column2>").ToString

                    'Add the ListViewSubItem to the ListViewItem
                    item.SubItems.Add(subItem)

                    'Add the ListViewItem to the ListView
                    lv.Items.Add(item)

                    'Repeat until all rows have been read
                End While
            End If

            'Close the reader
            reader.Close()
        Catch ex As Exception
            'If something went sideways, make sure that you close the connection
            'before exiting the method.
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
    End Sub

Call the method like this from the form:

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handled MyBase.Load
   Try
      PopulateListViewFromAccess(listview1)
   ex As Exception
      MessageBox.Show(ex.ToString())
   End Try
End Sub

[Naveen2961]

Thanks for the Reply.

Tried the same code,executed, but no go,

Public Sub PopulateListViewFromAccess(ByRef lv As ListView)
        mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _
               "Data Source=K:\Amrut Diary\Amrut_Diary\ADDB.mdb")
        con = New OleDb.OleDbConnection(mystr)
        con.Open()
        Try
            'Open the connection
            con.Open()
            'Create a new instance of the command and provide the SELECT query, and the opened connection
            cmd = New System.Data.OleDb.OleDbCommand("SELECT * FROM Village", con)
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            'Check to see if the SELECT query returned any rows
            If reader.HasRows Then
                'If so, perform a read for each row
                While reader.Read
                    'Declare a new ListViewItem, and provide the information
                    'to be shown in the very first column
                    Dim item As New ListViewItem(reader.Item("<Village>").ToString)

                    'Decare a new ListViewSubItem, and provide the information
                    'to be shown in the second (and so forth) column
                    Dim subItem As New ListViewItem.ListViewSubItem()
                    subItem.Text = reader.Item("<Village>").ToString

                    'Add the ListViewSubItem to the ListViewItem
                    item.SubItems.Add(subItem)

                    'Add the ListViewItem to the ListView
                    lv.Items.Add(item)

                    'Repeat until all rows have been read
                End While
            End If

            'Close the reader
            reader.Close()
        Catch ex As Exception
            'If something went sideways, make sure that you close the connection
            'before exiting the method.
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub

Listview is coming blank.

[Lek Plepi]

Private Sub cmdSearch_Click()
Set ac = New ADODB.Connection
Set ar = New ADODB.Recordset
Call DB
ac.Open strConek
lvProducts.ListItems.Clear
With ar
criteria = "Select *From tblProducts"
.Open criteria, strConek, adOpenStatic, adLockOptimistic
.MoveFirst
Do While Not .EOF
If Mid(!Kategory, 1, Len(txtKerkimi)) = txtKerkimi Then
Set intitem = lvArtikujt.ListItems.Add(, , !id1, , 1)
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(1) = !Name
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(2) = !QTY
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(3) = !CQty
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(4) = !PriceB
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(5) = !PriceS
End If
.MoveNext
lvProducts.SetFocus

Loop
.Close

End With

[Naveen2961]

Private Sub cmdSearch_Click()
Set ac = New ADODB.Connection
Set ar = New ADODB.Recordset
Call DB
ac.Open strConek
lvProducts.ListItems.Clear
With ar
criteria = "Select *From tblProducts"
.Open criteria, strConek, adOpenStatic, adLockOptimistic
.MoveFirst
Do While Not .EOF
If Mid(!Kategory, 1, Len(txtKerkimi)) = txtKerkimi Then
Set intitem = lvArtikujt.ListItems.Add(, , !id1, , 1)
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(1) = !Name
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(2) = !QTY
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(3) = !CQty
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(4) = !PriceB
lvProducts.ListItems(lvArtikujt.ListItems.Count).SubItems(5) = !PriceS
End If
.MoveNext
lvProducts.SetFocus

Loop
.Close

End With

Thanks for the reply,

Can you please explain; the text what it differs ?

lvProducts
Kategory
lvArtikujt
txtKerkimi
Thanks in Advance.