1

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
Improve this question
7
  • You could load the first row of the DB table you want into a DataTable using a DataAdapter and then iterate through the columns in the table and read the information from there. Commented Mar 19, 2013 at 0:53
  • If this needs to be done in with a listview, can you have it load the correct number of columns for each field? I'm playing with that now. A real plus would be adding the column name/label as the field name. Commented Mar 19, 2013 at 0:55
  • ooops, when you say fields, you mean each field for every row in each table? Or you just mean the you want a list of the available fields from each table? Commented Mar 19, 2013 at 0:55
  • Yes, it can be done with a ListView, but you need to make sure that the view is switched to details, otherwise you won't see the columns. If you have a table you can just bind it to the ListView and it will show all the data autmoatically for you. Commented Mar 19, 2013 at 0:58
  • I want each instance (row) of each field displayed in a selectable displayable object (i'm guessing listbox might not be the best choice), so that I can further manipulate the selected instance. I'm stuck on populating the listbox/listview/datatable. Commented Mar 19, 2013 at 0:59

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I could just add the 'DataGridView' directly from the designer in VS. I didn't even know 'DataGridView' existed. I am concerned as I'm building this as a Add-In to Excel, do you know of any good examples of how to add selected 'DataGridView' items into an Excel worksheet? I've dine it before with a listbox, but when I tried to mod it for DataGridView, I clearly not using the right syntax/commands.
It's actually quite easy, because you can loop through the rows and columns of it and get the cell values, so inserting data into Excel is very easy. Have a look at this SO question for example: stackoverflow.com/questions/7777205/…
If you bind the DataGridView to a DataTable, you can also loop through the DataTable in the same way and work with that to export the data to Excel.
Yes, but I need to be able to deal with each entire row if selected, not just the current active cell. That certainly works to add the whole thing, I'm worried about adding a row of data. But I'm also reading up on this now as we go :p
I think the selected row is actually a property of the DataGridView, so you can loop through the cells that way and export them. You can also get the row index of the selected cell and get the selected row that way. Its a very flexible control. If you get stuck on the export part and ask here I'm sure you'll get loads of answers :)
1

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
Improve this answer

4 Comments

Is there something that needs to be imported for this sub to work? I get multiple errors: View not being in System.Windows.Forms.ListBox, Columns not being in Systems.Windows.Forms.Listbox, etc
You must change your ListBox to a ListView.
Sorry, I thought since your opening line said "Using a ListBox.." that this was for a list box. Got it to work, maybe you should edit that part for clarity?
Oops. Right you are. Edit made. Hope it helped anyway.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.