• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 932
  • Last Modified:

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

0
98fatboyrider
Asked:
98fatboyrider
  • 3
  • 2
2 Solutions
 
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now