|
 |
This tutorial shows how we can use the FormView control to display data from a SQL database, and also allow adding, updating and deletion of this data. VB version.
This tutorial uses an example SQL database to show how the FormView control can display data for a user to edit, delete and also add to the database.
Web.Config:
| <connectionStrings>
<add name="BasicDataAccessConnectionString" connectionString="Data Source=CLIENT-TASK2\SQLEXPRESS;Initial Catalog=BasicDataAccess;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> |
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
We add a DropDownList control to enable selection of records, and a FormView control to allow display and editing of the selected record. Each of these controls will have their own SqlDataSource:
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="theName" DataValueField="theID" AppendDataBoundItems="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
<asp:ListItem>(Select)</asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%# ConnectionStrings:BasicDataAccessConnectionString %>" SelectCommand="SELECT [theID], [theName], [theCity] FROM [tblOne]"></asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" DataKeyNames="theID" DataSourceID="SqlDataSource2" Height="206px" Width="347px" OnItemDeleted="FormView1_ItemDeleted" OnItemInserted="FormView1_ItemInserted" OnItemUpdated="FormView1_ItemUpdated">
<EditItemTemplate>
<table style="width: 100%; height: 100%">
<tr>
<td align="right" style="width: 100px">ID:</td>
<td align="left" style="width: 100px"> <asp:Label ID="theIDLabel1" runat="server" Text=&##60;%# Eval("theID") %>'></asp:Label></td>
</tr> <tr>
<td align="right" style="width: 100px">Name:</td>
<td align="left" style="width: 100px"> <asp:TextBox ID="theNameTextBox" runat="server" Text='<%# Bind("theName") %>'> </asp:TextBox></td>
</tr> <tr>
<td align="right" style="width: 100px">City:</td>
<td align="left" style="width: 100px"> <asp:TextBox ID="theCityTextBox" runat="server" Text='<%# Bind("theCity") %>'> </asp:TextBox></td>
</tr> <tr>
<td align="center" colspan="2"> <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update" Text="Update"></asp:LinkButton> - <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
</td>
</tr> </table> <br /> </EditItemTemplate> <InsertItemTemplate>
<table style="width: 100%; height: 100%">
<tr>
<td align="right" style="width: 100px">ID:</td>
<td align="left" style="width: 100px"> <asp:TextBox ID="theIDTextBox" runat="server" Text='<%# Bind("theID") %>'></asp:TextBox>
</td>
</tr> <tr>
<td align="right" style="width: 100px">Name:</td>
<td align="left" style="width: 100px"> <asp:TextBox ID="theNameTextBox" runat="server" Text='<%# Bind("theName") %>'></asp:TextBox>
</td>
</tr> <tr>
<td align="right" style="width: 100px">City:</td>
<td align="left" style="width: 100px"> <asp:TextBox ID="theCityTextBox" runat="server" Text='<%# Bind("theCity") %>'></asp:TextBox>
</td>
</tr> <tr>
<td align="center" colspan="2"> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert"></asp:LinkButton> - <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
</td>
</tr> </table> <br /> </InsertItemTemplate> <ItemTemplate>Sample Database<br />
<table style="width: 150px; height: 150px">
<tr>
<td align="right" style="width: 100px">ID:</td> <td align="left" style="width: 100px">
<asp:Label ID="theIDLabel" runat="server" Text='<%# Eval("theID") %>'> </td> </tr> <tr>
<td align="right" style="width: 100px">Name:</td> <td align="left" style="width: 100px">
<asp:Label ID="theNameLabel" runat="server" Text='<%# Bind("theName") %>'></asp:Label> </td> </tr> <tr>
<td align="right" style="width: 100px">City:</td> <td align="left" style="width: 100px">
<asp:Label ID="theCityLabel" runat="server" Text='<%# Bind("theCity") %>'></asp:Label> </td> </tr> <tr>
<td align="center" colspan="2">
<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New"></asp:LinkButton> - <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton> - <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"></asp:LinkButton> </td> </tr> </table> </ItemTemplate> </asp:FormView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%# ConnectionStrings:BasicDataAccessConnectionString %>" DeleteCommand="DELETE FROM [tblOne] WHERE [theID] = @theID" InsertCommand="INSERT INTO [tblOne] ([theID], [theName], [theCity]) VALUES (@theID, @theName, @theCity)" SelectCommand="SELECT * FROM [tblOne] WHERE ([theID] = @theID)" UpdateCommand="UPDATE [tblOne] SET [theName] = @theName, [theCity] = @theCity WHERE [theID] = @theID">
<DeleteParameters>
<asp:Parameter Name="theID" Type="Int32" /> </DeleteParameters> <UpdateParameters>
<asp:Parameter Name="theName" Type="String" /> <asp:Parameter Name="theCity" Type="String" /> <asp:Parameter Name="theID" Type="Int32" /> </UpdateParameters> <SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="theID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> <InsertParameters>
<asp:Parameter Name="theID" Type="Int32" /> <asp:Parameter Name="theName" Type="String" /> <asp:Parameter Name="theCity" Type="String" /> </InsertParameters> </asp:SqlDataSource>
</form> |
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!
When the user updates, deletes or adds a new record using the FormView control, we need to tell the ListBox to update it's list from the database.
The following code will also make the FormView appear once the user has selected an item from the drop-down list, and the initial item of (Select) in the list will disappear once the user has selected from the list.
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
If (Not IsPostBack) Then
FormView1.Visible = False End If
Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As FormViewInsertedEventArgs)
DropDownList1.Items.Clear()
DropDownList1.DataBind() End Sub
Protected Sub FormView1_ItemUpdated(ByVal sender As Object, ByVal e As FormViewUpdatedEventArgs)
DropDownList1.Items.Clear()
DropDownList1.DataBind() End Sub
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
If DropDownList1.Items(0).Text = "(Select)" Then
DropDownList1.Items.RemoveAt(0) End If FormView1.Visible = True
Protected Sub FormView1_ItemDeleted(ByVal sender As Object, ByVal e As FormViewDeletedEventArgs)
DropDownList1.Items.Clear()
DropDownList1.DataBind() End Sub |
Download the Full Working Version of this Project written with Visual Studio.NET VB 2005 Here!
Looking for the C#.NET 2005 Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!
|
|
|