Populate a ListBox from Access Datafile

[JimRyanVB]

When I run this it successfully opens the datatable Color and returns a RecordCount = 179
However when I attempt to populate the lstColors it comes up empty?

Public Class frmColors
    Dim Colors As New dbControl

    Private Sub CmdExit_Click(sender As Object, e As EventArgs) Handles cmdExit.Click
        Me.Close()
        End
    End Sub

    Private Sub FrmColors_Shown(sender As Object, e As EventArgs) Handles MyBase.Load
        GetColors()
    End Sub

    Private Sub GetColors()
        Dim i As Integer

        Colors.ExecQuery("SELECT ColorName FROM COLOR ORDER BY ColorName")

        If NotEmpty(Colors.Exception) Then MsgBox(Colors.Exception) : Exit Sub

        For i = 0 To Colors.RecordCount - 1
            lstColors.Items.Add(Colors.DBDT)
        Next
    End Sub

    Private Function NotEmpty(text As String) As Boolean
        ' CHECK IF ERRORS PRESENT
        Return Not String.IsNullOrEmpty(text)
    End Function
End Class

=====================================================================================

Imports System.Data.OleDb

Public Class dbControl
    ' Create Your DB Connection
    Private DBCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Prismwin\Store1\Store1.mdb;")

    ' Prepare DB Command
    Private DbCmd As OleDbCommand

    ' DB Data
    Public DBDA As OleDbDataAdapter
    Public DBDT As DataTable

    ' Query Params
    Public Params As New List(Of OleDbParameter)

    ' Query Stats
    Public RecordCount As Integer
    Public Exception As String

    ' Query SQL Command
    Public Sub ExecQuery(Query As String)
        'Reset Query Stats
        RecordCount = 0
        Exception = ""

        Try
            ' Open a Connection
            DBCon.Open()

            ' Create a New Command
            DbCmd = New OleDbCommand(Query, DBCon)

            ' Load Params into DB Command
            Params.ForEach(Sub(p) DbCmd.Parameters.Add(p))

            ' Clear Params List
            Params.Clear()

            ' Execute Command & Fill DataTable
            DBDT = New DataTable
            DBDA = New OleDbDataAdapter(DbCmd)
            RecordCount = DBDA.Fill(DBDT)

        Catch ex As Exception
            Exception = ex.Message
        End Try

        ' Close Database
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Sub

    ' Include Query & Commands Params
    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New OleDbParameter(Name, Value)
        Params.Add(NewParam)
    End Sub
End Class

 

[jmcilhinney]

This makes no sense:

For i = 0 To Colors.RecordCount - 1
    lstColors.Items.Add(Colors.DBDT)
Next

Colors.DBDT is a table that contains ALL the records. Why would you add that table as a single item and why would you do that once for every record in that table? That's like saying "for as many eggs as there are in a carton, put the entire egg carton into a pan". You should be binding that DataTable to your ListBox, which means setting the DisplayMember to the name of the column whose data you want displayed, possibly setting the ValueMember to the name of the primary key column if you want to be able to access the selected PK via the SelectedValue property, and then assigning the DataTable to the DataSource property.

If you did want to add the items directly, which you should not, then you would need to actually get the text to display from each row in the table and add that, not add the table itself.

 

[JimRyanVB]

Got it thanks Jim