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