Populating combo box from a datatable

I need to populate several combo boxes with names pulled from a database.I would like to put the names in a datatable then use that to populate the combo boxes but it's not working. Can anyone see why?
Public Class clsData
 
    Public Sub New(ByVal sConnection As String)
        'sCN = sConnection  'set the connectionstring for this class
        Dim m_sCN As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\HelpDesk.accdb;User Id=admin;Password=;"
    End Sub
    Public Function GetTable() As DataTable
        Try
            'get all rows from Staff list
            Dim sSQL As String
            Dim dtData As New DataTable
            Dim cn As New OleDb.OleDbConnection(sCn)
            sSQL = "SELECT * FROM tblSTAFF;"
            Dim cmd As New OleDb.OleDbCommand(sSQL, cn) 'tie together connection and sql
            cn.Open()
            dtData.Load(cmd.ExecuteReader)
            cn.Close()
            cmd.Dispose()
            cn.Dispose()
            GetTable = dtData
        Catch ex As Exception
            Throw ex
        End Try
    End Function
 
    Private Sub frmDetail_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dtDetails As New DataTable
        dtDetails = oDetails.GetTable
        'cboRequestor.Items.Add = dtDetails.Rows(0).Item(1)
 
    End Sub

Open in new window

98fatboyriderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kerwinsiyCommented:
note you need to specify what is your DataTextField and DataValueField.

 
Public Class clsData
 
    Public Sub New(ByVal sConnection As String)
        'sCN = sConnection  'set the connectionstring for this class
        Dim m_sCN As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\HelpDesk.accdb;User Id=admin;Password=;"
    End Sub
    Public Function GetTable() As DataTable
        Try
            'get all rows from Staff list
            Dim sSQL As String
            Dim dtData As New DataTable
            Dim cn As New OleDb.OleDbConnection(sCn)
            sSQL = "SELECT * FROM tblSTAFF;"
            Dim cmd As New OleDb.OleDbCommand(sSQL, cn) 'tie together connection and sql
            cn.Open()
            dtData.Load(cmd.ExecuteReader)
            cn.Close()
            cmd.Dispose()
            cn.Dispose()
            GetTable = dtData
        Catch ex As Exception
            Throw ex
        End Try
    End Function
 
    Private Sub frmDetail_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dtDetails As New DataTable
        dtDetails = oDetails.GetTable
        cboRequestor.DataSource = dtDetails
        cboRequestor.DataBind()
 
 
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:

oDetails.GetTable 'where did oDetails come from here...think should just be GetTable()

'cboRequestor.Items.Add = dtDetails.Rows(0).Item(1) 'you have this commented out, so not setting anything on your drop down list
cboRequestor.DataSource = dtDetails 'think this is what you need anyway
0
Kevin CrossChief Technology OfficerCommented:
I posted before kerwinsiy post showed up -- I think the points were nicely covered there, except you will still need to ensure the the oDetails portion of code is correct.  Not sure where the extra object is coming from unless the code shown is really two different classes and oDetails is the instantiation of the data class in the form class.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

98fatboyriderAuthor Commented:
Looks like I may have posted the wrong class code earlier. Below is the revised code. Now I'm getting an error  'DataBind' is not a member of 'System.Windows.Forms.ComboBox' and error  "Argument not specified for parameter 'item' of 'Public Function Add(item As Object) As Integer'.


Public Class clsDetail
    Dim oDetails As clsDetail
    Dim m_sCN As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Help Desk.accdb;User Id=admin;Password=;"
    Public Function GetTable() As DataTable
        Try
            'get all rows from issues list
            Dim sSQL As String
            Dim dtData As New DataTable
            Dim cn As New OleDb.OleDbConnection(sCn)
            sSQL = "SELECT * FROM tblSTAFF" '"SELECT tblCALLS.Call_ID, tblCALLS.Status, tblCALLS.Description, tblCALLS.Start_Date, tblCALLS.Date_Completed, tblCALLS.Requstor FROM tblCALLS;"
            Dim cmd As New OleDb.OleDbCommand(sSQL, cn) 'tie together connection and sql
            cn.Open()
            dtData.Load(cmd.ExecuteReader)
            cn.Close()
            cmd.Dispose()
            cn.Dispose()
            GetTable = dtData
        Catch ex As Exception
            Throw ex
        End Try
    End Function
End Class
 
Public Class frmDetail
    Dim oDetails As New clsDetail
    Private Sub frmDetail_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dtDetails As New DataTable
        dtDetails = oDetails.GetTable
        cboRequestor.DataSource = dtDetails
        cboRequestor.DataBind()
        cboRequestor.Items.Add = dtDetails.Rows(0).Item(1)
    End Sub
End Class

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
cboRequestor.DataSource = dtDetails
cboRequestor.DataBind()
cboRequestor.Items.Add = dtDetails.Rows(0).Item(1)

Don't believe you need the third line as setting the data source to the data table should already include all the items that you won't have to add individually.
0
98fatboyriderAuthor Commented:
working code highlights...

Public Function GetTable(ByVal Table As String) As DataTable
'Will Return Entire Contents of a Table
Try
Dim sSQL As String
Dim dtData As New DataTable
Dim cn As New OleDb.OleDbConnection(sCn)
sSQL = "SELECT * FROM " & Table & ";"
Dim cmd As New OleDb.OleDbCommand(sSQL, cn) 'tie together connection and sql
cn.Open()
dtData.Load(cmd.ExecuteReader)
cn.Close()
cmd.Dispose()
cn.Dispose()
GetTable = dtData
Catch ex As Exception
Throw ex
End Try
End Function

Dim UserHeaders As DataTable = objData.GetUserHeaders
Dim NewRow As DataRow = UserHeaders.NewRow()
'Add Tags to Status radio buttons from the database - GStrike
For Each rb In pnlStatus.Controls
rb.Tag = objData.GetStatusID(rb.Text)
Next
'Add "All Requesters" Option to datatable
NewRow("FullName") = "All Requestors"
NewRow("User_ID") = "" 
UserHeaders.Rows.InsertAt(NewRow, 0)
'Populate Requester Combobox - GStrike
cboReq.DataSource = UserHeaders
cboReq.DisplayMember = "FullName"
cboReq.ValueMember = "User_ID"

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.