I have a SQL table that contains three items (e.g. Yes, No, Maybe So) and I create a dynamic drop down DropDownList to record the response for multiple multiple questions.

In the code behind I am using the following (Example 1) to populate a DropDownList from an SQL table to and ItemTemplate of a DataGrid.

(Example 2) is used in the Code page to retrieve the list. The problem is that I have to repeat the same DropDownList (with different ID's - Comment1, Comment2, Comment3...etc.) 81 times.

If I use the same DataSource="<%# LoadComments() %>" for each of the 80 instances, each iteration of the list adds the original 3 items in the list to the next list, where by the 80th instance there are 240 items listed in the drop down, from a list that only has three items.

Is there a way to use this single instance to populate each of the 80 instances of the list? I know I can hard code 80 instances of the list with List Item, but I would prefer to use the database to populate the list, as later additions to the list, do not to be modified for each of the 80 lists...

Thanks for your assistance, in advance!!

Example 1

Function LoadComments()
Dim objConnComments As SqlConnection
Dim mySettingsComments As New NameValueCollection
mySettingsComments = AppSettings
Dim strConnComments As String
strConnComments = mySettingsComments("connString")
objConnComments =New SqlConnection(strConnComments)
objConnComments.Open()

Dim strComments As String

strComments ="SELECT Comment_ID, Comment_Code, Comment_Description " & _
"FROM Hanes_Comments " & _
"Order By Comment_Code ASC"

Dim ObjCmdComments As New SqlDataAdapter(strComments, objConnComments)
ObjCmdComments.Fill(dsComments,"Hanes_Comments")

Return dsComments
Response.Write(dsComments.ToString)

End Function

EXAMPLE 2:

<asp:DropDownList
runat="server"
ID="Comments1"
DataSource="<%# LoadComments() %>"
DataMember="Hanes_Comments"
DataTextField="Comment_Description"
DataValueField="Comment_Code"
Width="80"
Height="20"
Font-Size="XX-Small">
</asp:DropDownList>