Adding Items to a Data-Bound DropDownList On The Fly in ASP.NET 2.0

by John Peterson


Introduction

I recently received an email from a visitor that was having a hard time adding a new entry to a data-bound DropDownList. While it's not the simplest thing to do, if you're using ASP.NET 2.0 the amount of actual code that you need to write is negligible.

I'll be using Visual Web Developer 2005 Express as my working environment for the rest of this article. It's available as a free download from Microsoft. If you're looking for a development environment and haven't already checked it out, do yourself a favor and do so today.

The Database

In order to illustrate the process, I'm simply going to walk you through an example. The first thing we need is a database. You can use whatever database you prefer, but for simplicity I'm just setting up a new SQL Server Express database from VWD:


Figure 1


Figure 2

As you can tell by looking at the screen captures above, I've created a new database named "sample" and a new table named "tblFruit". The table has only two columns: an integer primary key (identity) column named "id" and a varchar for the fruit names.

Okay so it's not very exciting, but the premise I'll be using for this sample is that the user is going to be selecting their favorite fruit from the DropDownList. If the list doesn't contain their favorite fruit, we allow them to add it.

I've also added a few fruits to the table to start with:


Figure 3

That pretty much handles the database setup.

The Web Form

Now that we've got our database set up we can start building our web form. I'm not much of a design guy so feel free to lay things out however you want, but here are the basic elements we'll be using.

  • a SqlDataSource control to connect to our database
  • a DropDrownList to display the list of fruits from the database
  • a Button to submit the user's fruit choice
  • a TextBox where the user can enter a new fruit name
  • a Button the user can use to add their new fruit to the database
  • a Label control to echo back the user's fruit choice once they submit it


Figure 4

Setting up the SqlDataSource Control

The key to simplifying the entire process comes when you set up the SqlDataSource control so I'm going to walk you through that step-by-step.

  1. Choose "Configure Data Source..." from either the SqlDataSource control's Smart Tag menu or by right-clicking on the control.


    Figure 5

  2. On the first screen simply choose the appropriate connection to the database that contains your fruit table.

    If it's not already saved there, you'll then be asked if you want to save the connection string in the application configuration file (which you'll probably want to). I called my connection string the ever so creative "myConnectionString".

  3. The next screen is where all the magic happens. First we'll build our Select statement. I'm simply going to retrieve all the rows from our table and sort them by the fruit name. To do this I simply check the "*" checkbox on the main screen and then pop open the "Order By..." dialog box and tell it to sort by "Name" and choose the "Ascending" radio button. In my case the resulting SQL statement looks like this:

    SELECT * FROM [tblFruit] ORDER BY [name]


    Figure 6

    Now for the magic. Before you click the "Next >" button, click on "Advanced...". There you'll find two options. The one we're interested in is called "Generate INSERT, UPDATE, and DELETE statements". By selecting its checkbox, VWD Express will generate the appropriate commands to insert, update, and delete rows from our fruit table for us.


    Figure 7

    Once you've checked the checkbox close the dialog box, click the "Next >" button to get to the test phase and then click "Finish".

  4. If you now take a look at the SqlDataSource control in source view you should see something like this:

    <asp:SqlDataSource ID="mySqlDataSource" runat="server"
        ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
        DeleteCommand="DELETE FROM [tblFruit] WHERE [id] = @id"
        InsertCommand="INSERT INTO [tblFruit] ([name]) VALUES (@name)"
        SelectCommand="SELECT * FROM [tblFruit] ORDER BY [name]"
        UpdateCommand="UPDATE [tblFruit] SET [name] = @name WHERE [id] = @id"
    >
        <DeleteParameters>
            <asp:Parameter Name="id" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="id" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="name" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>

    As it turns out, we'll only be using the Select and Insert commands so you can delete the others and their associated parameters which leaves you with this:

    <asp:SqlDataSource ID="mySqlDataSource" runat="server"
        ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
        InsertCommand="INSERT INTO [tblFruit] ([name]) VALUES (@name)"
        SelectCommand="SELECT * FROM [tblFruit] ORDER BY [name]"
    >
        <InsertParameters>
            <asp:Parameter Name="name" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>

    Now that we've set up our SqlDataSource control, the rest is pretty simple.

The Mundane Stuff

I'm not going to go into any detail here, but somewhere along the way you need to set up the databinding for the DropDownList, set the text on the buttons, add event handlers for the button click events, and tell your form what to do when you click the submit button. It's all pretty boring and it's stuff you've probably done more then you'd like to admit. If you're curious about anything you can check out the details in the code listing that will follow shortly.

The Cool Part

In the event handler for the "Add Fruit" button paste the following code:

Protected Sub btnAddFruit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    mySqlDataSource.InsertParameters("name") = New Parameter("name", TypeCode.String, txtNewFruit.Text)
    mySqlDataSource.Insert()

    txtNewFruit.Text = Nothing
End Sub

If you've been paying attention, you'll quickly see that we're simply reusing the same SqlDataSource control that we use to get the data to add the new fruit to the table. Notice how little code there is to write. We don't need to open a connection, create a command, or anything like that. Simply set the value of the parameters and perform the insert. Quick, simple, and easy!

The Code

Since the code is relatively short, I'm going to include the full listing here instead of making you download it.

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        lblFruitSelection.Text = "You told us that " & ddlFruitList.SelectedItem.Text & " are your favorite fruit."
    End Sub

    Protected Sub btnAddFruit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        mySqlDataSource.InsertParameters("name") = New Parameter("name", TypeCode.String, txtNewFruit.Text)
        mySqlDataSource.Insert()

        txtNewFruit.Text = Nothing
    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Fruit Selection</title>
</head>
<body>
    <form id="myForm" runat="server">
    <div>
        <asp:SqlDataSource ID="mySqlDataSource" runat="server"
            ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
            InsertCommand="INSERT INTO [tblFruit] ([name]) VALUES (@name)"
            SelectCommand="SELECT * FROM [tblFruit] ORDER BY [name]"
        >
            <InsertParameters>
                <asp:Parameter Name="name" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
        <p>
        Please choose your favorite fruit:
        <asp:DropDownList ID="ddlFruitList" runat="server" DataSourceID="mySqlDataSource"
            DataTextField="name" DataValueField="id">
        </asp:DropDownList>
        <asp:Button ID="btnSubmit" runat="server" Text="Submit Fruit Choice" OnClick="btnSubmit_Click"/>
        </p>
        <p>
        If your favorite fruit isn't listed, please add it:
        <asp:TextBox ID="txtNewFruit" runat="server"></asp:TextBox>
        <asp:Button ID="btnAddFruit" runat="server" Text="Add Fruit" OnClick="btnAddFruit_Click" />
        </p>
        <p>
        <asp:Label ID="lblFruitSelection" runat="server"></asp:Label>
        </p>
    </div>
    </form>
</body>
</html>

Runtime

Okay so now that we're done... here's what you'll get if you load the page in a browser:


Figure 8

Let's say my favorite fruit is pears and we see that pears are not listed in the DropDownList. So I type "Pears" in into the textbox, click the "Add Fruit" button. This causes the page to reload and adds a row for pears to the database and repopulates the DropDownList with the new list of fruits which now includes pears.


Figure 9

Since it's not the focus of the example, submitting the form doesn't do anything. I simply echo back the user's fruit choice via the label control.


Figure 10

Conclusion

I hope this example has helped show you just how easy it can be to reuse DataSource controls in ASP.NET 2.0. Not only does it allow you to code faster, it also produces shorted code that is easier to manage.



About the Author

From ASP101

Articles originally posted on ASP101.com

Comments

  • The main gucci Endeavor Dialogue : Who cares for practically nothing wins?!

    Posted by BobHotgloff on 05/01/2013 07:09am

    Concise blog post explains the inner workings of the nike and consequently everything you want to accomplish right now. [url=http://www.mizunogoruhujp.com/]ミズノ[/url] The Trick For nike [url=http://www.mizunogoruhujp.com/ミズノ-ゴルフクラブ-c-1.html]ミズノ アイアン[/url] Brief blog post will show you most of the cogs and wheels on mizuno as well as things you want to do right now. [url=http://www.mizunogoruhujp.com/ゴルフグローブ-c-33.html]ミズノ グローブ[/url] Specifically what everyone else has been doing when contemplating nike and moreover those things that you'd probably like to handle different. [url=http://www.mizunogoruhujp.com/ゴルフバッグ-c-7.html]ミズノゴルフ[/url] Honest piece of writing unwraps Six brand-new stuff of mizuno that noone is talking about. [url=http://www.mizunogoruhu.com/]ミズノゴルフ[/url] A nice double turn on mizuno [url=http://www.mizunogoruhu.com/ミズノmizuno-クラブ-c-4.html]ミズノ グラブ[/url] Appliances and developing in The philipines - nike simply leaves with no farewell [url=http://www.mizunogoruhu.com/ミズノmizuno-アイアン-c-3.html]ミズノ アイアン[/url] Robust recommendations for nike which can be used beginning today. [url=http://www.mizunogoruhu.com/ミズノmizuno-バッグ-c-5.html]ミズノゴルフ[/url] Concise piece of content reveals the proven facts about mizuno and the way that it could actually may affect anybody.

    Reply
  • Nice one there

    Posted by Slalaleasyday on 03/13/2013 07:23am

    Nice Post. ---------- I love http://youtube.com

    Reply
  • High Heel Shoes Or Mbt Health Shoes

    Posted by mbt shoes on 12/01/2012 11:49am

    In light of the skin MBT sandals, available with a soft cloth dips gently wipe shoeshine color, note that, shoeshine not besmear too thick, because the shoeshine has certain volatile and dry, with too much, time for a long time will cause vamp weather-shack. In addition, the best besmear before unripe chicken oil or a pig oil, can make the face of soft moist, changeful form, for smooth leather sandals, can use dry cloth to wipe stain, then spread on some shoeshine. Storage MBT suede leather sandals, check for any suede besmirch at first, usable fine sand in besmirch place gently friction, both decontamination also can maintain suede erect. if sole is sludge, also want to brush dips in water to clean.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • "Security" is the number one issue holding business leaders back from the cloud. But does the reality match the perception? Keeping data close to home, on premises, makes business and IT leaders feel inherently more secure. But the truth is, cloud solutions can offer companies real, tangible security advantages. Before you assume that on-site is the only way to keep data safe, it's worth taking a comprehensive approach to evaluating risks. Doing so can lead to big benefits.

  • Hybrid cloud platforms need to think in terms of sweet spots when it comes to application platform interface (API) integration. Cloud Velocity has taken a unique approach to tight integration with the API sweet spot; enough to support the agility of physical and virtual apps, including multi-tier environments and databases, while reducing capital and operating costs. Read this case study to learn how a global-level Fortune 1000 company was able to deploy an entire 6+ TB Oracle eCommerce stack in Amazon Web …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds