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

[diana_j86]

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:

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

[Phil Weber]

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:

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

[Alex22m]

ok Thanks,

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

[sanches2]

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:

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

[JBourgeois]

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.

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.

[Phil Weber]

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.