Dropdownlist: Database connections problems at EditItemTemplate

Hello,

I'm attempting to generate a dropdownlist by binding it to a database table. My screen has a view of the records plus a footer for inserting new records. I want to populate a drop down list of items from the database on both FooterTemplate and EditItemTemplate. I can successfully create the list on FooterTemplate (the insert new record fields). Here, I have no problems with my database connections.

On page load, if I hit 'Edit', it gives me this error:

There is already an open DataReader associated with this Connection which must be closed first.

I tried closing the datareader and I get an error:Invalid attempt to Read when reader is closed.

Here's the snippet of code:

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load'Put user code to initialize the page here

LoadDataFromDB()

IfNot IsPostBackThen

DataBindGrid()

EndIfEndSubSub LoadDataFromDB()Dim strSQLStmntAsStringDim cmdDefaultsAs SqlCommand' Create new command object passing it our SQL query' and telling it which connection to use.

strSQLStmnt = "SELECT tblTemplates.TemplateID, tblTemplates.Description, " _

& "tblTemplates.Tuition, tblTemplates.Books, tblTemplates.TrainingTable, tblTemplates.Housing, " _

& "tblResidency.ResidencyType, tblCategory.Type, tblCategory.Description AS CategoryDesc, " _

& "tblScholarship.ScholarshipType FROM tblTemplates INNER JOIN tblResidency ON tblTemplates.ResidencyID = " _

& "tblResidency.ResidencyID INNER JOIN tblScholarship ON tblTemplates.ScholarshipID = tblScholarship.ScholarshipID " _

& "INNER JOIN tblCategory ON tblTemplates.CategoryID = tblCategory.CategoryID"

cmdDefaults =

New SqlCommand(strSQLStmnt, conDefaults)' Open the connection, execute the command, and close the connection.

conDefaults.Open()

rdrDefaults = cmdDefaults.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

DefaultsDataGrid.ShowFooter =

TrueEndSubSub DataBindGrid()

DefaultsDataGrid.DataSource = rdrDefaults

DefaultsDataGrid.DataBind()

EndSubFunction BindResidency()As DataSet'Populate the ddlDataSetConst strSQLDDLAsString = _

"SELECT Distinct ResidencyID, ResidencyType FROM tblResidency"

Dim myDataAdapterAs SqlDataAdapter =New _

SqlDataAdapter(strSQLDDL, conDefaults)

myDataAdapter.Fill(ddlDataSet)

Return ddlDataSetEndFunction

And the asp.net:

<asp:TemplateColumn HeaderText="Residency">
<ItemTemplate>
<%# Container.DataItem("ResidencyType") %>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList id="add_residency" runat="server" Width="178px" DataSource="<%# BindResidency() %>" DataTextField="ResidencyType" DataValueField="ResidencyID">
</asp:DropDownList>
</FooterTemplate>
<EditItemTemplate>
<asp:DropDownList id="edit_residency" runat="server" Width="178px" DataSource="<%# BindResidency() %>" DataTextField="ResidencyType" DataValueField="ResidencyID">
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>

Any help would be appreciated!

Thanks,

Curtis

Sure enough, after posting I figured out what the problem was. Basically, I can easily create another SQLConnection to the SQL Server db and use it to populate all my drop down lists. May not be the most efficient use of database connections, but it works.

Thanks,

Curtis

|||

Without changing some structure of your code, that is how you would have to do it. The BindResidency is being called while the datagrid is looping through your datareader, so the connection is in use at that point. You can get around it, by pre-filling the dataset (Prior to the datagrid databind call) and having the function return it. You may also want to cache the dataset in your function, especially if the list doesn't change often.

sql

Drilling Down - Toggling All Rows

Does anyone know if it's possible to expand and collapse all rows when drilling down? The way it is now, the user has to click the plus sign on each row to drill down. I would like to provide functionality to expand or collapse all rows at once while the report is being viewed. Thanks.

There may be a way - but I have not found one!

I also did not like after I expanded a group then expanded a group within a group how you have to <return> a coule of times to begin where you first started.

So, I just <jump to a URL> and then display the detail within a "frame".

Seems easier for the user to navigate in that they have the primary "expand" item still available - if you get my drift!

Hope this helps!