locked
help me RRS feed

  • Question

  • hi , i'm using vb .net 2010 and i don't know how to insert items from a table dataset (sql server) to a combobox ..help please
    Sunday, June 5, 2011 10:22 PM

Answers

  • You have two options. 1 read from database using SqlConnection, SqlCommand and SqlDataReader. Iterate through reader object and add each item.

    Second option is using SqlDataAdapter and DataSet then bind it to ComboBox using DataSource, DisplayMember, ValueMember properties.

     


    Thanks,
    A.m.a.L Hashim
    Microsoft Most Valuable Professional
    Dot Net Goodies
    Don't hate the hacker, hate the code
    • Proposed as answer by RohitArora Monday, June 6, 2011 1:41 PM
    • Marked as answer by Helen Zhou Monday, June 13, 2011 5:24 AM
    Monday, June 6, 2011 6:21 AM
  • Hello karawena,

    You need to specify the datasource and the display member and the value member of the combobox to the table and the columns. the datasource is the table. the display member is the column you want to display in the combobox and the value member is the value that will be used as the selected item in the combobox. so the selected value would be your id which you can use to get the related records for your combobox 2.

    The following code assumes that you are already using ADODB code, and have a connection object which is already connected to the database.

    If you do not have the above, please see this thread for help.

    Code:

    Dim strSQL as String    'Declare the variables we need 
    Dim oRS as ADODB.Recordset
      Set oRS = New ADODB.Recordset
     
                               'Load the data
    '** change this SQL to load the data you want.
      strSQL = "SELECT Colour FROM Colours"
     
    '** change oConn to the name of your Connection object
      oRS.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
                               'Fill the combo box (or ListBox)
    '** change the name of the combo to the one you want to fill
      With cboColour
        .Clear
        Do While Not oRS.EOF
    '** change the name of the field here to the one you want to show
          .AddItem oRS.fields("Colour").value
          oRS.MoveNext
        Loop
      End With
     
                               'Tidy up
      oRS.Close
      Set oRS = Nothing

     

     

    You can also include hidden (Integer) data for each item shown, such as an ID field - which is useful if two entries have the same text! You can add this by adding the extra field into your SQL, and inserting the following line immediately after the "AddItem" line:

    VB Code:

    1.  .ItemData(.NewIndex) = oRS.fields("ColourID").Value 



    Note that all of the above could easily be written as a sub, so that it can be used from anywhere in your program that needs to fill a combobox with data. eg:

    Code:

    Public Sub FillCombo(objComboBox As ComboBox, _
                         oConn As ADODB.Connection, _
                         strSQL As String, _
                         strFieldToShow As String, _
                         Optional strFieldForItemData As String)
    'Fills a combobox with values from a database
     
    'Parameters:
      'objComboBox    = the ComboBox to fill
      'oConn          = the connection to the database
      'strSQL         = the SQL to load the data
      'strFieldToShow = the name of the field to show
      'strFieldForItemData (optional) = the name of the field to put into ItemData (Integer type fields only)
     
    'Example usage (standard):
      'Call FillCombo(Combo1, oConn, "SELECT Colour FROM Colours", "Colour")
     
    'Example usage (with ItemData):
      'Call FillCombo(Combo1, oConn, "SELECT Colour, ColourID FROM Colours", "Colour", "ColourID")
     
     
    Dim oRS As ADODB.Recordset  'Load the data
      Set oRS = New ADODB.Recordset
      oRS.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
                                
      With objComboBox          'Fill the combo box
        .Clear
        If strFieldForItemData = "" Then
          Do While Not oRS.EOF      '(without ItemData)
            .AddItem oRS.fields(strFieldToShow).Value
            oRS.MoveNext
          Loop
        Else
          Do While Not oRS.EOF      '(with ItemData)
            .AddItem oRS.fields(strFieldToShow).Value
            .ItemData(.NewIndex) = oRS.fields(strFieldForItemData).Value
            oRS.MoveNext
          Loop
        End If
      End With
     
      oRS.Close                 'Tidy up
      Set oRS = Nothing
     
    End Sub

     

     

     

    ..to do the same for a ListBox, simply use the same code but replace the text "Combo" with "List".

     

    Also here is a complete demo about Populating data from database to a ComboBox on CodeProject using DataAdaptor, you may change the code to VB.net by a converter:http://www.developerfusion.com/tools/convert/csharp-to-vb/

    • Marked as answer by Helen Zhou Monday, June 13, 2011 5:24 AM
    Tuesday, June 7, 2011 8:40 AM

All replies

  • You have two options. 1 read from database using SqlConnection, SqlCommand and SqlDataReader. Iterate through reader object and add each item.

    Second option is using SqlDataAdapter and DataSet then bind it to ComboBox using DataSource, DisplayMember, ValueMember properties.

     


    Thanks,
    A.m.a.L Hashim
    Microsoft Most Valuable Professional
    Dot Net Goodies
    Don't hate the hacker, hate the code
    • Proposed as answer by RohitArora Monday, June 6, 2011 1:41 PM
    • Marked as answer by Helen Zhou Monday, June 13, 2011 5:24 AM
    Monday, June 6, 2011 6:21 AM
  • Hello karawena,

    You need to specify the datasource and the display member and the value member of the combobox to the table and the columns. the datasource is the table. the display member is the column you want to display in the combobox and the value member is the value that will be used as the selected item in the combobox. so the selected value would be your id which you can use to get the related records for your combobox 2.

    The following code assumes that you are already using ADODB code, and have a connection object which is already connected to the database.

    If you do not have the above, please see this thread for help.

    Code:

    Dim strSQL as String    'Declare the variables we need 
    Dim oRS as ADODB.Recordset
      Set oRS = New ADODB.Recordset
     
                               'Load the data
    '** change this SQL to load the data you want.
      strSQL = "SELECT Colour FROM Colours"
     
    '** change oConn to the name of your Connection object
      oRS.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
                               'Fill the combo box (or ListBox)
    '** change the name of the combo to the one you want to fill
      With cboColour
        .Clear
        Do While Not oRS.EOF
    '** change the name of the field here to the one you want to show
          .AddItem oRS.fields("Colour").value
          oRS.MoveNext
        Loop
      End With
     
                               'Tidy up
      oRS.Close
      Set oRS = Nothing

     

     

    You can also include hidden (Integer) data for each item shown, such as an ID field - which is useful if two entries have the same text! You can add this by adding the extra field into your SQL, and inserting the following line immediately after the "AddItem" line:

    VB Code:

    1.  .ItemData(.NewIndex) = oRS.fields("ColourID").Value 



    Note that all of the above could easily be written as a sub, so that it can be used from anywhere in your program that needs to fill a combobox with data. eg:

    Code:

    Public Sub FillCombo(objComboBox As ComboBox, _
                         oConn As ADODB.Connection, _
                         strSQL As String, _
                         strFieldToShow As String, _
                         Optional strFieldForItemData As String)
    'Fills a combobox with values from a database
     
    'Parameters:
      'objComboBox    = the ComboBox to fill
      'oConn          = the connection to the database
      'strSQL         = the SQL to load the data
      'strFieldToShow = the name of the field to show
      'strFieldForItemData (optional) = the name of the field to put into ItemData (Integer type fields only)
     
    'Example usage (standard):
      'Call FillCombo(Combo1, oConn, "SELECT Colour FROM Colours", "Colour")
     
    'Example usage (with ItemData):
      'Call FillCombo(Combo1, oConn, "SELECT Colour, ColourID FROM Colours", "Colour", "ColourID")
     
     
    Dim oRS As ADODB.Recordset  'Load the data
      Set oRS = New ADODB.Recordset
      oRS.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
                                
      With objComboBox          'Fill the combo box
        .Clear
        If strFieldForItemData = "" Then
          Do While Not oRS.EOF      '(without ItemData)
            .AddItem oRS.fields(strFieldToShow).Value
            oRS.MoveNext
          Loop
        Else
          Do While Not oRS.EOF      '(with ItemData)
            .AddItem oRS.fields(strFieldToShow).Value
            .ItemData(.NewIndex) = oRS.fields(strFieldForItemData).Value
            oRS.MoveNext
          Loop
        End If
      End With
     
      oRS.Close                 'Tidy up
      Set oRS = Nothing
     
    End Sub

     

     

     

    ..to do the same for a ListBox, simply use the same code but replace the text "Combo" with "List".

     

    Also here is a complete demo about Populating data from database to a ComboBox on CodeProject using DataAdaptor, you may change the code to VB.net by a converter:http://www.developerfusion.com/tools/convert/csharp-to-vb/

    • Marked as answer by Helen Zhou Monday, June 13, 2011 5:24 AM
    Tuesday, June 7, 2011 8:40 AM
  • Hi karawena,

    Did this help? If you have any further questions / If you still have any questions about this issue, please feel free to let me know. We will continue to work with you on this issue.


    Helen Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 13, 2011 5:24 AM