VB.Net: Filling a ComboBox with data stored in database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: VB.Net: Filling a ComboBox with data stored in database

  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Question VB.Net: Filling a ComboBox with data stored in database

    How to fill a combobox with a data stored in a table in sql server?....
    Can we use stored procedure to do so?

    I tryed this code but it keeps giving an error message:
    "An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll"
    What does that mean????

    My code:
    Code:
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim connString As String = "server=(local);database=AdvantEdge;trusted_connection=yes"
    Dim conn As New SqlConnection(connString) 
    ' fillComboBox1()
    Dim strSQL As String = "Select * From Disk"
    Dim DA As New SqlDataAdapter(strSQL, conn)
    Dim DS As New DataSet
    DA.Fill(DS, "Disk")
    'Create and populate the DataTable to bind to the ComboBox:
    Dim dt As New DataTable
    dt.Columns.Add("Disk_Name", GetType(System.String))
    dt.Columns.Add("Disk_Key", GetType(System.String))
    ' Populate the DataTable to bind to the Combobox.
    Dim drDSRow As DataRow
    Dim drNewRow As DataRow
    For Each drDSRow In DS.Tables("Disk").Rows()
    drNewRow = dt.NewRow()
    drNewRow("Disk_Name") = drDSRow("Disk_Name")
    drNewRow("Disk_Key") = drDSRow("Disk_Key")
    dt.Rows.Add(drNewRow)
    Next
    'Bind the DataTable to the ComboBox by setting the Combobox's DataSource property to the DataTable. To display the "Description" column in the Combobox's list, set the Combobox's DisplayMember property to the name of column. Likewise, to use the "Code" column as the value of an item in the Combobox set the ValueMember property. 
    ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
    With ComboBox1
    .DataSource = dt
    .DisplayMember = "Disk_Name"
    .ValueMember = "Disk_Key"
    .SelectedIndex = 0
    End With
    End Sub
    Last edited by Hack; 04-03-2009 at 08:53 AM. Reason: Added Code Tags
    DIANA

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    It would be helpful to know on which line the SqlException occurs, and the value of any inner exception, which would tell you in more detail the cause of the error.

    I can tell you, however, that you're doing unnecessary work in the code above. You're filling a DataSet with the result of a query, then creating a new DataTable and copying data out of the DataSet into the DataTable. A DataSet already contains one or more DataTables, so you may bind the control directly to the original DataSet. Try this:
    Code:
    Dim conn As New SqlConnection(connString)
    
    Dim strSQL As String = "SELECT * FROM Disk"
    Dim da As New SqlDataAdapter(strSQL, conn)
    Dim ds As New DataSet
    da.Fill(ds, "Disk")
    
    With ComboBox1
        .DataSource = ds.Tables("Disk")
        .DisplayMember = "Disk_Name"
        .ValueMember = "Disk_Key"
        .SelectedIndex = 0
    End With
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Apr 2007
    Posts
    4
    ok Thanks,

    I solved the error...and thanks about your suggestion, it is helpful

  4. #4
    Join Date
    Apr 2009
    Posts
    1

    Thumbs up

    Quote Originally Posted by Phil Weber View Post
    It would be helpful to know on which line the SqlException occurs, and the value of any inner exception, which would tell you in more detail the cause of the error.

    I can tell you, however, that you're doing unnecessary work in the code above. You're filling a DataSet with the result of a query, then creating a new DataTable and copying data out of the DataSet into the DataTable. A DataSet already contains one or more DataTables, so you may bind the control directly to the original DataSet. Try this:
    Code:
    Dim conn As New SqlConnection(connString)
    
    Dim strSQL As String = "SELECT * FROM Disk"
    Dim da As New SqlDataAdapter(strSQL, conn)
    Dim ds As New DataSet
    da.Fill(ds, "Disk")
    
    With ComboBox1
        .DataSource = ds.Tables("Disk")
        .DisplayMember = "Disk_Name"
        .ValueMember = "Disk_Key"
        .SelectedIndex = 0
    End With
    thank you! that's the most elegant solution i 've seen fir resolving he problem
    it helped me a lot short and effectivei am pretty new in ado.net and sql

  5. #5
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    Phil solution works but it shows a recurring mistake.

    A DataSet is a tool to link together many tables. You do not need a DataSet when you have only one table, this simply adds useless overhead.

    This is an "error" often made by programmers who have been using .NET from the very beginning. The first examples that were shown in 2001 always used a DataSet. Also, DataTable objects were limited at that time and did not offer all the features available to DataSets such as a WriteXml method, so they were often used where a DataTable would have been sufficient.

    Code:
    Dim conn As New SqlConnection(connString)
    
    Dim strSQL As String = "SELECT * FROM Disk"
    Dim da As New SqlDataAdapter(strSQL, conn)
    Dim dt As New DataTable
    dt.Fill(ds, "Disk")
    With ComboBox1
        .DataSource = dt
        .DisplayMember = "Disk_Name"
        .ValueMember = "Disk_Key"
        .SelectedIndex = 0
    End With
    The code is very similar, but since if does not had the overhead of the DataSet, it requires less memory and is more efficient.

    Also for the sake of efficiency, since the data is read only, you could improve the loading time by using a DataReader to fill the table. Even if it forces you to loop through the lines, it is more efficient thant a DataAdapter when loading DataTables or any other type of collection from a database. You could even use one to fill the ComboBox directly, without having to create a DataTable, which is quite a big object to use only to display data in a ComboBox.
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

  6. #6
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Hi, Jacques, thanks for your reply. The original poster did not specify which version of .NET she was using, so I posted a reply that works with any version. You are correct that if one is using .NET 2.0 or later, one may use a DataTable rather than a DataSet to work with a single table. I'm not sure the resulting efficiency or memory savings will be noticeable in most cases, but it certainly doesn't hurt.

    A DataAdapter uses a DataReader internally to fill a DataSet/DataTable, so I don't think you gain anything by writing code to fill the object yourself. On the contrary, I prefer to avoid writing code whenever possible. The fewer lines of code you write, the less code you must debug and maintain.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  7. #7
    Join Date
    Sep 2013
    Posts
    1
    ComboBox1.DataSource = ds.Tables(0)
    ComboBox1.ValueMember = "id"
    ComboBox1.DisplayMember = "name"

    full source VB.Net Combobox from datasource

Similar Threads

  1. Replies: 2
    Last Post: 09-13-2008, 12:38 PM
  2. Telling if a database is stored locally or on a server
    By DrunkenMunk in forum VB Classic
    Replies: 4
    Last Post: 11-30-2005, 12:23 PM
  3. Replies: 6
    Last Post: 03-14-2001, 06:02 PM
  4. Replies: 7
    Last Post: 01-13-2001, 03:15 PM
  5. I am helpless - Edit Info form
    By Dan in forum VB Classic
    Replies: 0
    Last Post: 03-17-2000, 06:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center