显示数据库中列表框中的所有字段，vb.net

Showing all fields in listbox from database, vb.net

提问by Atl LED

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:

我正在尝试制作一个列表框来显示 Access 数据库中特定表的所有字段。我有几张桌子，我的想法是让每个桌子通过不同的按钮加载（并清除框中的项目）。一个技巧是桌子的大小不一样。如何让每个表的所有字段都显示出来。我现在只显示一个字段：

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

采纳答案by yu_ominae

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

如果您不反对使用 aDataGridView而不是 a ListView，您可以这样做：

    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.

我的计算机上没有“ACE.OLEDB”，因此必须改用 JET 提供程序，但它的工作方式应该相同。

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 DataTableto a ListView(see this MSDN questionfor example) and you would have to loop through the columns in your DataTablefirst and add the column headers to your ListViewand then loop through the rows and add the data.

我应该补充一点，您可以使用此方法从数据库中检索数据，但据我所知，将 a 绑定DataTable到 a没有简单的方法ListView（例如，请参阅此 MSDN 问题），您必须遍历列在您的DataTable第一个中，将列标题添加到您的ListView，然后遍历行并添加数据。

UPDATE:

更新：

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

为了回答您关于如何从 导出数据的问题，DataGridView我记得不久前我编写了代码来执行此操作。

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.

我希望这将有助于您入门。

回答by ron tornambe

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:

使用 Listview 控件，以下代码应该可以解决问题。为简单起见，我只定义了 4 个客户字段 - 您需要根据表字段定义来定义它们：

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