Bobski Bobski - 1 year ago 116 Question

Stored procedure result for combobox failing to bind

i have this code, what I'm doing is executing a StoredProcedure, that simply selects 2 fields (ID, Name) and then i want to populate a combobox with the data. Here's my code...

Using con As New SqlConnection(sConnection)
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim sqlAdp As New SqlDataAdapter

cmd.CommandText = "sp_RData"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Name", "Accounting")
cmd.Connection = con
sqlAdp.SelectCommand = cmd

ComboBox1.DataSource = ds
ComboBox1.ValueMember = "ID"
ComboBox1.DisplayMember = "Name"
End Using

My stored procedure is very simple, goes like this....

Select ID, Name from tblRData where Name = @Name

@Name is obviously the parameter I'm trying to pass.

What happens is on Combobox1.ValueMember ="ID" it says "Cannot bind to the new display member."

Anyone know why? Is this the best way to populate a combobox with data from stored procedure? Wondering if this will be bulletproof, I don't see it not, since many of the comboboxes would be populated on form_load, just wondering if there's a better way of doing this. Just getting into from the wonderful world of VB6.

Answer Source

The only thing really "wrong" with the code is:

ComboBox1.DataSource = ds

Contrary to the name, a DataSet does not contain data, but is a collection of DataTable objects where the data resides, so:

ComboBox1.DataSource = ds.Tables(0)

The code can be shorter and more efficient though:

Using con As New SqlConnection(sConnection),
     cmd As New SqlCommand("sp_RData")

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.Add("@Name", SqlDbType.Text).Value = "Accounting"

    Dim dt As New DataTable()

    ComboBox1.DataSource = dt
    ComboBox1.ValueMember = "ID"
    ComboBox1.DisplayMember = "Name"
End Using
  • DataAdapters, DBCommand object as well as a few others should be disposed after use. As a rule anything which implements a Dispose() method should be used in a Using block. The code above "stacks" the Connection and Command object into one Using block mainly to reduce indentation.
  • A DataAdapter is a very powerful thing. It can store the connection and all the SQL to Add, Insert, Update and Delete from a single base table. Since you can fill a DataTable using a DbCommand object, it is a little simpler and uses fewer 'things' to get the job done. With some providers and very large data sets, an adapter is faster, but the amount is not enough to worry about.
  • Prefer Add to AddWithValue when working with Parameters. The later requires the DB Provider to infer the data type from the data passed, which can fail in some situations.

The code above fills a local DataTable which then goes out of scope. That may be fine, but given the WHERE clause maybe not. If you later will (re)fill the CBO with names from "Janitorial" there is an easier way:

  • Declare a form/class level DataTable so you can reference it elsewhere
  • Drop the WHERE clause and fill it with ALL the names (e.g. "SELECT Id, Name FROM Departments").

Change the filter as needed:

dtDepts.DefaultView.RowFilter = String.Format("Name = '{0}", thisDept)

The View restricts what is shown to "Management" or "Secretarial" depending on what you want. The CBO bound to the table is automatically updated.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download