CODEWITHSUNDEEP
sqlvb.netms-accesslistbox
Atl LED
Atl LED

Reputation: 666

Showing all fields in listbox from database, vb.net

I'm trying to make a list box to show all the fields of a particular table in an Access database. I have a few tables, and the idea would be to have each table loaded by a different button (and the items in the box cleared). One trick is that tables aren't all the same size. How do I get all the fields to show up for each table. What I have now is only showing one field:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;data source='C:\dummy_data.accdb';"
    Dim conn As New OleDbConnection(connString)
    Dim sql As String = "SELECT * FROM Customers"
    Dim cmd As New OleDbCommand(sql, conn)
    conn.Open()
    Dim reader As OleDbDataReader = cmd.ExecuteReader()
    ClientList.Items.Clear()
    While reader.Read()
        ClientList.Items.Add(reader(0).ToString())
    End While
    reader.Close()
    conn.Close()

End Sub

Upvotes: 1

Views: 12605

Answers (2)

ron tornambe
ron tornambe

Reputation: 10780

Using a Listview control, the following code should do the trick. For simplicity I defined only 4 customer fields - you will need to define them according to your table field defintions:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ClientList.View = View.Details
    ClientList.FullRowSelect = True
    ClientList.Columns.Add("ID", 120)
    ClientList.Columns.Add("Name", 120)
    ClientList.Columns.Add("Address", 140)
    ClientList.Columns.Add("Email", 100)
    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\dummy_data.accdb;"
    ClientList.Items.Clear()
    Using conn As New Data.OleDb.OleDbConnection(connString)
        conn.Open()
        Dim sql As String = "SELECT * FROM Customers"
        Using cmd As New Data.OleDb.OleDbCommand(sql, conn)
            Dim lvi As ListViewItem
            Using oRDR As Data.OleDb.OleDbDataReader = cmd.ExecuteReader
                While oRDR.Read()
                    lvi = ClientList.Items.Add(oRDR.GetValue(0).ToString)
                    For i = 1 To oRDR.FieldCount - 1
                        lvi.SubItems.Add(oRDR.GetValue(i).ToString)
                    Next
                End While
            End Using
        End Using
        conn.Close()
    End Using

End Sub

Upvotes: 1

yu_ominae
yu_ominae

Reputation: 2935

If you are not averse to using a DataGridView instead of a ListView, you could do it this way:

    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source='PathToMyDatabase';"
    Dim sql As String = "SELECT * FROM Tメイン;"

    Dim dt As DataTable

    With New OleDbDataAdapter(sql, connString)
        Dim ds As New DataSet()
        .Fill(ds)
        dt = ds.Tables(0)
    End With

    Me.DataGridView1.DataSource = dt

I do not have 'ACE.OLEDB' on my computer, so had to use the JET provider instead, but it should work the same way.

I should add that you can use this method to retrieve the data from the DB, but there is no easy way as far as I understand to bind a DataTable to a ListView (see this MSDN question for example) and you would have to loop through the columns in your DataTable first and add the column headers to your ListView and then loop through the rows and add the data.

UPDATE:

To answer your questiona s to how to export data from the DataGridView I remembered that I wrote code to do that a little while back.

Private Function ExportToExcelFile(ByVal FileName As String) As Boolean

    With New Excel.Application
        With .Workbooks.Add()
            For Each sheet As Worksheet In .Worksheets
                If sheet.Index > 1 Then
                    Call sheet.Delete()
                End If
            Next
            With CType(.Worksheets(1), Worksheet).Range("A1")
                For Each column As DataGridViewColumn In Me.dgvData.Columns
                    .Offset(0, column.Index).Value = column.HeaderText
                    For Each row As DataGridViewRow In Me.dgvData.Rows
                        .Offset(row.Index + 1, column.Index).Value = row.Cells(column.Index).Value
                    Next
                Next
            End With
            Call .SaveAs(FileName)
        End With
        Call .Quit()
    End With

End Function

I hope this will help to get you started.

Upvotes: 1

Related Questions