Click here to return to my article index
Fill a DropDownList server control with data and add a default item
In this article, we will look at one way to fill a DropDownList server control adn then add a default item. The code used to connect to the
database and retrieve a DataReader are really the same as if we were filling a DataGrid. We
create a SqlConnection object and a SqlCommand object, open the connection, and fill a SqlDataReader using the ExecuteReader function of
the SqlCommand object. Notice that we the use the CommandBehavior enumeration CloseConnection. By passing the
CommandBehavior.CloseConnection enumeration we are telling the SqlCommand object to close the connection as soon as all of the data has been
stream into the SqlDataReader. This is handy because we no longer have to worry about closing the connection. However, (Best Practice
Alert! Go here to read about how you can prevent BAD memory leaks that can occur if you are not careful with your
Connection/DataReader objects) we must still remember to close the SqlDataReader.
The code sample below uses a couple other best pratices you should understand and be aware of:
*The code below uses structured error handling techniques in the form of the Try...Catch statement. Go here
to read more about the great new structured error handling we VBers now have.
*The code below also uses a connection string stored in a web.config configuration file. Go here to read more about
the web.config configuration file and how it can be used to store application wide data.
Code:
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
If Not Page.IsPostBack Then
Dim myConnection As SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings("DSN_pubs"))
Dim myCommand As SqlCommand = new SqlCommand("Select pub_id, pub_name From publishers", myConnection)
Dim myDataReader As SqlDataReader
Try
myConnection.Open()
myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DropDownList1.DataSource = myDataReader
DropDownList1.DataBind()
Catch myException As Exception
Response.Write("An error has occurred: " & myException.ToString())
Finally
If Not myDataReader Is Nothing Then
myDataReader.Close()
End If
DropDownList1.Items.Insert(0, "Select an Item")
DropDownList1.SelectedIndex = 0
End Try
End If
End Sub
</script>
<body>
<form runat="server">
<asp:DropDownList id="DropDownList1"
runat="server"
DataTextField="pub_name"
DataValueField="pub_id" />
</form>
</body>
</html>
Result:
|
