Fill Multiple Dropdown Lists with one SQL Query

Question

I can't seem to get this to work. I want to fill multiple dropdown lists with the same data. It is inefficient to run the same query each time. Here's where I've managed to get so far:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If (Not Page.IsPostBack) Then
        Dim ddlArray = {lst1, lst2, lst3, lst4, lst5, lst6, lst7, lst8, lst9, lst10}
        PopulateObjectList(ddlArray)
    End If
End Sub

Sub PopulateObjectList(ByVal ddlArray As Array)
    strSQL = "select distinct Object_ID, Object_Description from object"
    objCmd = New SqlCommand(strSQL, Master.DBConnect())

    For Each ddl In ddlArray
        ddl.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
        ddl.DataValueField = "Object_ID"
        ddl.DataTextField = "Object_Description"
        ddl.DataBind()
        ddl.Items.Insert(0, "Select Object")
    Next

    Call Master.DBDisconnect()
End Sub

This of course gives the error:

ExecuteReader requires an open and available Connection. The connection's current state is closed.

because the connection is closed after the initial datasource is assigned. I would imagine I could instead create a variable to hold the data then bind it to each dropdown list, but dataset doesn't work and I'm not sure how else to do it.

How do I populate each of the ten dropdown lists with the same data without having to run ten separate queries on the database?

Wiktor Zychla · Accepted Answer · 2014-04-09 22:20:59Z

1

  CommandBehavior.CloseConnection

You are closing the connection after first use. Instead, read the data once, copy it to a list of objects and bind to that list.

I wonder why DataSet doesn't work though.

Sub PopulateObjectList(ByVal ddlArray As Array)
strSQL = "select distinct Object_ID, Object_Description from object"

sqlCon = Master.DbConnect()

da = New SqlDataAdapter(strSQL, sqlCon)
ds = New DataSet

da.Fill(ds)

For Each ddl In ddlArray
    ddl.DataSource = ds
    ddl.DataValueField = "Object_ID"
    ddl.DataTextField = "Object_Description"
    ddl.DataBind()
    ddl.Items.Insert(0, "Select Object")
Next

Call Master.DBDisconnect()
End Sub

edited Apr 9, 2014 at 22:20

answered Apr 9, 2014 at 22:10

Wiktor Zychla

48.5k6 gold badges83 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Simply removing that will then give the error: There is already an open DataReader associated with this Command which must be closed first (I tried that, too).

Dan Over a year ago

Please post a code example of how you would resolve this. It isn't working for me the ways I've tried.

Wiktor Zychla Over a year ago

Can't validate it but this would be my approach.

Collectives™ on Stack Overflow

Fill Multiple Dropdown Lists with one SQL Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related