When you are programming web pages it is very important to optimize your code for better and faster performance. In this part I will show you how to optimize code for filling multiple dropdownlists with data from Microsoft Access. The same thing is with SQL Server. I'm using asp.net 2.0 and Microsoft Visual Studio 2005.
Add new Module to your project and name it DataOperations (Right Click Project - Add New Item - Class. Change class to module, like this:
Public Module DataOperations
End Module
Now write code for filling dropdownlist:
Imports System.Data.OleDb
Imports System.Configuration.ConfigurationManager
Public Module DataOperations
Public Sub FillDropDown(ByVal ddl As DropDownList, ByVal tbl As String, ByVal txt As String, ByVal val As String)
Dim cn As New OleDbConnection(ConnectionStrings("Data").ConnectionString.ToString)
Dim cmd As New OleDbCommand
cmd.CommandType = Data.CommandType.Text
cmd.Connection = cn
cmd.CommandText = "SELECT " & val & ", " & txt & " FROM " & tbl & " ORDER BY " & txt & " ASC"
Dim dr As OleDbDataReader
cn.Open()
dr = cmd.ExecuteReader(Data.CommandBehavior.CloseConnection)
While dr.Read
ddl.Items.Add(New ListItem(dr(txt).ToString.Trim, dr(val)))
End While
dr.Close()
End Sub
End Module
Now all you need to do to fill dropdownlist is to call this sub like:
FillDropDown(Me.cmbCategory, "Categories", "Category", "ID")
You need to pass parameters: dropdown name, table or query for fill, table column for text and table column for value.
If you need you can expand this sub by adding where and order by parameters.
Hope this helps.
Offline 
