[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Populating combo box from a datatable

Posted on 2008-11-04
6
Medium Priority
?
909 Views
Last Modified: 2008-11-28
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
Comment
Question by:98fatboyrider
  • 3
  • 2
6 Comments
 
LVL 2

Accepted Solution

by:
kerwinsiy earned 750 total points
ID: 22882785
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22882801

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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 750 total points
ID: 22882810
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:98fatboyrider
ID: 22883064
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22883199
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
 

Author Comment

by:98fatboyrider
ID: 23056767
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question