[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can't databind multiple dropdown lists to the same datareader

Posted on 2005-04-15
9
Medium Priority
?
625 Views
Last Modified: 2012-06-27
I am executing a datareader that then is used to populate 4 drop down lists with the results.  The first drop down list gets populated with the right data but the rest only contain the "Select an Item" option that I manually insert at the top of each one.  Here is the code:

Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender
       Dim cmdAirportState As New SqlCommand("AirportsByState_Select", conRezSuite)
        cmdAirportState.CommandType = CommandType.StoredProcedure
        cmdAirportState.Parameters.Add(New SqlParameter("@AirportState", SqlDbType.Char, 2))
        cmdAirportState.Parameters("@AirportState").Value = Trim(strStateCode)
        conRezSuite.Open()
        Dim drAirports As SqlDataReader = cmdAirportState.ExecuteReader      

        ddAirFrom.DataTextField = "AirportName"
        ddAirFrom.DataValueField = "AirportID"
        ddAirFrom.DataSource = drAirports
        ddAirFrom.DataBind()
        ddAirFrom.Items.Insert(0, "Select an Item")
        ddAirFrom.SelectedIndex = 0
       
        ddAirTo.DataTextField = "AirportName"
        ddAirTo.DataValueField = "AirportID"
        ddAirTo.DataSource = drAirports
        ddAirTo.DataBind()
        ddAirTo.Items.Insert(0, "Select an Item")
        ddAirTo.SelectedIndex = 0

        ddAirRetFrom.DataTextField = "AirportName"
        ddAirRetFrom.DataValueField = "AirportID"
        ddAirRetFrom.DataSource = drAirports
        ddAirRetFrom.DataBind()
        ddAirRetFrom.Items.Insert(0, "Select an Item")
        ddAirRetFrom.SelectedIndex = 0

        ddAirRetTo.DataTextField = "AirportName"
        ddAirRetTo.DataValueField = "AirportID"
        ddAirRetTo.DataSource = drAirports
        ddAirRetTo.DataBind()
        ddAirRetTo.Items.Insert(0, "Select an Item")
        ddAirRetTo.SelectedIndex = 0

        drAirports.Close()
        conRezSuite.Close()
    End Sub

Like I said I know the stored procedure is working as well as the datareader becasue the first dropdownlist I databind (ddAirFrom) is getting populated.  Why won't the rest of them bind to the same data?
0
Comment
Question by:jacobymatt
  • 4
  • 3
  • 2
9 Comments
 
LVL 6

Expert Comment

by:Havagan
ID: 13792365
I don't use datareaders often, but I'm thinking it's because a datareader is forward only? Once it reads through and binds data on the first list, the others aren't able to move to the beginning record and start again.

Paul
0
 
LVL 2

Expert Comment

by:jellis613
ID: 13792398
If I am not mistaken, you can use a datareader to populate only one datasource.

Why dont you try the following:
1) Create a function BindAirportInfo that does the following: Binds the data to one DropDownList (something like this)
Private Function BindAirportInfo (ByRef dd As DropDownList)
        Dim cmdAirportState As New SqlCommand("AirportsByState_Select", conRezSuite)
        cmdAirportState.CommandType = CommandType.StoredProcedure
        cmdAirportState.Parameters.Add(New SqlParameter("@AirportState", SqlDbType.Char, 2))
        cmdAirportState.Parameters("@AirportState").Value = Trim(strStateCode)
        conRezSuite.Open()
        Dim drAirports As SqlDataReader = cmdAirportState.ExecuteReader      

        dd.DataTextField = "AirportName"
        dd.DataValueField = "AirportID"
        dd.DataSource = drAirports
        dd.DataBind()
        dd.Items.Insert(0, "Select an Item")
        dd.SelectedIndex = 0
End Function

2)Then in your PreRender sub, call the function for each one of the DropDownLists in question:
Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender
        BindAirportInfo(ddAirFrom)
        BindAirportInfo(ddAirTo)
        BindAirportInfo(ddAirRetFrom)
        BindAirportInfo(ddAirRetTo)
End Sub
0
 
LVL 6

Expert Comment

by:Havagan
ID: 13792543
jellis,

That would work, although it requires four trips to the database. At that point it probably makes more sense to read the data from the database once into a DataSet or DataTable and then bind the DataTable to all four Drop Down Lists.

Paul

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:jacobymatt
ID: 13793214
Yep, I solved the problem by creating 4 different datareaders in order to bind the same data to each dropdownlist.  And like Havagan said, I'm not sure that's more efficient then filling a dataset once and using it to databind all the lists.  But this code has presented a new problem.  I am currently not able to keep track of what the user chooses from the drop down because of the
.Items.Insert(0, "Select an Item")
.SelectedIndex = 0

that I have on each of the dropdownlists.  Because this is in my PreRender it is setting the value back to "Select an Item" before I can store the selected value.  

So I have a button with code:
 Private Sub btnAirAddLeg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAirAddLeg.Click
label1.text = ddAirFrom.SelectedItem.Value
End Sub

I tried moving just the part of the code that goes
.Items.Insert(0, "Select an Item")
.SelectedIndex = 0
for each of the ddlists to the Page_Load event with a If not ispostback condition thinking it would only add the "Select an Item" option when the page the loads the first time but now it is not adding that option at all, whether it's the first time the page loads or on a postback.  I'm confused.  Increasing points.  
0
 

Author Comment

by:jacobymatt
ID: 13793244
Sorry, I meant to clarify that the label.text = ddAirFrom.SelectedItem.Value that I have in the button click event is there to test whether I am retrieving the user's choice from the drop down.  But when I click the btnAirAddLeg button the label's text reads "Select an Item".  
0
 
LVL 2

Expert Comment

by:jellis613
ID: 13793333
You should only need to bind the controls one time. Why dont you move this to the Page_Load sub, and only run it "If Is Not Postback". That way the user-selected value should be saved on postbacks (since the data will not be re-databound)
0
 

Author Comment

by:jacobymatt
ID: 13793466
Okay to answer that I need to back up.  I have one aspx page that I am cycling out usercontrols on to give sort of a wizard feel to the application.  So if you think of the code you've been looking at as code on usercontrol2 then here is the on the previous usercontrol or usercontrol1:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not IsPostBack Then
            Dim dsRequest As New DataSet
            daStates.Fill(dsRequest, "States")
            Dim dtStates As DataTable = dsRequest.Tables("States")
            ddDestinationState.DataSource = dtStates
            ddDestinationState.DataTextField = "State"
            ddDestinationState.DataValueField = "Code"
            ddDestinationState.DataSource = dtStates
            ddDestinationState.DataBind()
            ddDestinationState.Items.Insert(0, "Select an Item")
            ddDestinationState.SelectedIndex = 0          
        End If
    End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
 Me.Parent.FindControl("UserControl2").Visible = True
                Me.Visible = False

Then back on UserContro2 the part I left off of the PreRender Event for brevity is:
Dim ddDestinationState As System.Web.UI.WebControls.DropDownList
        ddDestinationState = Me.Parent.FindControl("TravelMethod1").FindControl("ddDestinationState")
        Dim strStateCode As String = ddDestinationState.SelectedItem.Value

That comes just before where I initialize the cmdAirportState object which takes strStateCode as the input parameter.  Now I could not get this to work if I put it in the Page_Load event, which I tried first.  I'm not sure why, but it seems like it couldn't get the value from the previous control.  As soon as I put in the PreRender it worked, but creates the problem I have mentioned.  Thanks for your help!
0
 
LVL 2

Accepted Solution

by:
jellis613 earned 400 total points
ID: 13793497
how about this: put the code to bind the step 2 DropDownLists inside the btnNext_Click Sub
That way, these controls will only be databound and made visible after the button is clicked (you might want to put in a validator or an If statement in the function to make sure that the SelectedIndex of ddDestinationState is > 0. Otherwise, instead of using a Next button, use AutoPostBack for ddDestinationState)
0
 

Author Comment

by:jacobymatt
ID: 13794330
There's the winner.  This whole concept of accessing controls on a usercontrol from another usercontrol is new to me, but it really opens up a lot of doors and keeps you from having to use Session variables.  Here's the working code in case anyone's interested.  

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
Dim strOriginStateCode As String = ddOriginState.SelectedItem.Value
                Dim strDestinationStateCode As String = ddDestinationState.SelectedItem.Value

                Dim cmdAirportState As New SqlCommand("AirportsByState_Select", conRezSuite)
                cmdAirportState.CommandType = CommandType.StoredProcedure
                cmdAirportState.Parameters.Add(New SqlParameter("@AirportState", SqlDbType.Char, 2))
                cmdAirportState.Parameters("@AirportState").Value = strOriginStateCode
                conRezSuite.Open()

                Dim ddAirFrom As Web.UI.WebControls.DropDownList = Me.Parent.FindControl("Air1").FindControl("ddAirFrom")
                Dim drAirports As SqlDataReader = cmdAirportState.ExecuteReader
                ddAirFrom.DataTextField = "AirportName"
                ddAirFrom.DataValueField = "AirportID"
                ddAirFrom.DataSource = drAirports
                ddAirFrom.DataBind()
                drAirports.Close()
                ddAirFrom.Items.Insert(0, "Select an Item")
                ddAirFrom.SelectedIndex = 0

                Dim ddAirRetTo As Web.UI.WebControls.DropDownList = Me.Parent.FindControl("Air1").FindControl("ddAirRetTo")
                Dim drAirports4 As SqlDataReader = cmdAirportState.ExecuteReader
                ddAirRetTo.DataTextField = "AirportName"
                ddAirRetTo.DataValueField = "AirportID"
                ddAirRetTo.DataSource = drAirports4
                ddAirRetTo.DataBind()
                drAirports4.Close()
                ddAirRetTo.Items.Insert(0, "Select an Item")
                ddAirRetTo.SelectedIndex = 0

                Dim ddAirTo As Web.UI.WebControls.DropDownList = Me.Parent.FindControl("Air1").FindControl("ddAirTo")
                cmdAirportState.Parameters("@AirportState").Value = strDestinationStateCode
                Dim drAirports2 As SqlDataReader = cmdAirportState.ExecuteReader
                ddAirTo.DataTextField = "AirportName"
                ddAirTo.DataValueField = "AirportID"
                ddAirTo.DataSource = drAirports2
                ddAirTo.DataBind()
                drAirports2.Close()
                ddAirTo.Items.Insert(0, "Select an Item")
                ddAirTo.SelectedIndex = 0

                Dim ddAirRetFrom As Web.UI.WebControls.DropDownList = Me.Parent.FindControl("Air1").FindControl("ddAirRetFrom")
                Dim drAirports3 As SqlDataReader = cmdAirportState.ExecuteReader
                ddAirRetFrom.DataTextField = "AirportName"
                ddAirRetFrom.DataValueField = "AirportID"
                ddAirRetFrom.DataSource = drAirports3
                ddAirRetFrom.DataBind()
                drAirports3.Close()
                ddAirRetFrom.Items.Insert(0, "Select an Item")
                ddAirRetFrom.SelectedIndex = 0

                conRezSuite.Close()
                Me.Parent.FindControl("Air1").Visible = True
                Me.Visible = False
End Sub
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month20 days, 7 hours left to enroll

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question