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.
-
Choose "Configure Data Source…" from either the SqlDataSource control’s
Smart Tag menu or by right-clicking on the control.
Figure 5 -
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". -
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 6Now 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 7Once you’ve checked the checkbox close the dialog box, click the "Next >" button to get
to the test phase and then click "Finish". -
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.