How to effective DropDownList code

[Kenta]

I have an Web application that retrieves some db info and send it out to an html table. All created on the fly in vb code behind. For every post (row) in the db I also fill puts an instance of a Dropdownlist with items read from the db.
That means that for every row I read from the database I read the same db info into the Dropdownlist (see the skeleton example below).

A lot of overhead I think. It must be possible to create the Dropdownlist once before the loop and copy from that instance in the loop. But I don’t get it, how can I change the code to manage that?

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cboCombo2 As DropDownList
        Dim i As Integer
        Dim SupposedDbRs As Integer = 5

        'It must be possible to create my DropDownList ONCE here and copy instances from it into the loop but how?

        While i < SupposedDbRs
            cboCombo2 = addCombo()
            cboCombo2.ID = i
            form1.Controls.Add(cboCombo2)
            i = i + 1
        End While
    End Sub
    Function addCombo() As DropDownList
        Dim cboCombo As New DropDownList
        'Some db initializing code here

        cboCombo.Items.Add("one") ' Normally posts from a db recordset
        cboCombo.Items.Add("two")
        cboCombo.Items.Add("three")
        cboCombo.Items.Add("four")
        Return cboCombo
    End Function
End Class

//KentA

[yarbrough40]

You mention "Recordset" which I'm assuming you are attempting to use ADO am I correct? if so I would recommend not doing that as it is no longer supported and problematic. I suggest using ADO.net instead.

a potential solution for you might look something like this:

Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As OleDbConnection = New OleDbConnection(ConnStringHere)
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = con
cmd.CommandText = 'select * from MyDatabaseTable'
con.Open()

Dim dr As OleDbDataReader = cmd.ExecuteReader
While dr.read
cboCombo.Items.Add(dr.item(0).tostring)
End While

con.Close()

[Kenta]

No it was of old habit I wrote that way. It vill be a .net dbreader. But it still does not answer my question, it must be possible in some way to effective my code,

[yarbrough40]

please explain what you are trying to do again. your initial explanation is confusing.
do you want to create a dropdownlist then read the items in the list and pass those values somewhere else?

[Kenta]

Sorry if I am confusing. My problem is that for every loop in my While statement I will have to do the same db call to read the same posts that fill my DropDownlist.

In my skeleton code it would be five db calls. If you could read from the db once above the while loop and copy my DropDownList from there I would “save” four db calls, and some program over head. But I have found a workaround for it with the ListItemsCollec tion. My new algorithm goes like this and it is acceptable for me.

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cboCombo As DropDownList
        Dim i As Integer, SupposedDbRs As Integer = 5
        Dim ListCollect As New ListItemCollection()

        ListCollect = addListItems()

        While i < SupposedDbRs
            cboCombo = New DropDownList
            cboCombo.ID = i
            cboCombo.DataSource = ListCollect
            cboCombo.DataTextField = "text"
            cboCombo.DataValueField = "value"
            cboCombo.SelectedValue = i + 1
            cboCombo.DataBind()

            form1.Controls.Add(cboCombo)
            i = i + 1
        End While
    End Sub
    Function addListItems() As ListItemCollection
        Dim ListColl As New ListItemCollection()

        ListColl.Add(New ListItem("One", "1"))
        ListColl.Add(New ListItem("Two", "2"))
        ListColl.Add(New ListItem("Three", "3"))
        ListColl.Add(New ListItem("Four", "4"))
        ListColl.Add(New ListItem("Five", "5"))

        Return ListColl
    End Function