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:

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

LoadDataFromDB()

If Not IsPostBack Then

DataBindGrid()

End If

End Sub

Sub LoadDataFromDB()

Dim strSQLStmnt As String

Dim cmdDefaults As 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 = True

End Sub

Sub DataBindGrid()

DefaultsDataGrid.DataSource = rdrDefaults

DefaultsDataGrid.DataBind()

End Sub

Function BindResidency() As DataSet

'Populate the ddlDataSet

Const strSQLDDL As String = _

"SELECT Distinct ResidencyID, ResidencyType FROM tblResidency"

Dim myDataAdapter As SqlDataAdapter = New _

SqlDataAdapter(strSQLDDL, conDefaults)

myDataAdapter.Fill(ddlDataSet)

Return ddlDataSet

End Function

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