<%@ Import Namespace="System.Data" %>
<%@ Page Language="VB" AutoEventWireup="false" %>
<script language="VB" runat="server">
'As the FormView becomes databound, the DataBound events of the dropdownlists
'will trigger. At this stage the page has not been rendered yet and we can
'manipulate the displayed values on the dropdownlists
Protected Sub ddlProvince_DataBound(ByVal sender As Object, ByVal e As EventArgs)
Dim ddl As DropDownList = CType(sender, DropDownList)
'add an empty item title "make a selection"
AddEmptyItem(ddl)
Dim frmV As FormView = CType(ddl.NamingContainer, FormView)
If Not frmV.DataItem Is Nothing Then
'Let's pull the province value from the databound item. The data
'in my application is supplied by BLL as a dataview. Therefore each
'item bound to the FormView is of type DataRowView. So let's cast
'that DataItem to the appropriate type to be able to use it
Dim strProvince As String = CType(frmV.DataItem, DataRowView)("Province")
ddl.ClearSelection()
'be careful of the possibility that the value saved on the
'database does not exist in the valid selections that are displayed
'on the list
Dim li As System.Web.UI.WebControls.ListItem = ddl.Items.FindByValue(strProvince)
If Not li Is Nothing Then li.Selected = True
End If
'since the city selection is dependent on the province, we
'have to databind the city list after we changed the selection for the province
ddl = CType(frmV.FindControl("ddlCity"), DropDownList)
If Not ddl Is Nothing Then ddl.DataBind()
End Sub
Protected Sub ddlCity_DataBound(ByVal sender As Object, ByVal e As EventArgs)
Dim ddl As DropDownList = CType(sender, DropDownList)
Dim frmV As FormView = CType(ddl.NamingContainer, FormView)
If Not frmV.DataItem Is Nothing Then
Dim strCity As String = CType(frmV.DataItem, DataRowView)("City")
ddl.ClearSelection()
Dim li As System.Web.UI.WebControls.ListItem = ddl.Items.FindByValue(strCity)
If Not li Is Nothing Then li.Selected = True
End If
'add an empty item title "make a selection"
AddEmptyItem(ddl)
End Sub
'==============================================================================================
'The concept is basically to intercept the 2-way databinding within the ItemUpdating event
'by passing into the FormViewUpdateEventArgs the new values that we need to update.
'This interception gives us the opportunity to set the selections for the cascading dropdownlist
'without relying on the Bind method to effect the 2-way databinding
'==============================================================================================
Protected Sub FormView1_ItemUpdating(ByVal sender As Object, ByVal e As FormViewUpdateEventArgs)
Dim strProv As String = CType(CType(sender, FormView).FindControl("ddlProvince"), DropDownList).SelectedValue
e.NewValues("Province") = strProv
Dim strCity As String = CType(CType(sender, FormView).FindControl("ddlCity"), DropDownList).SelectedValue
e.NewValues("City") = strCity
e.Cancel = False
End Sub
Protected Sub FormView1_ItemInserting(ByVal sender As Object, ByVal e As FormViewInsertEventArgs)
Dim strProv As String = CType(CType(sender, FormView).FindControl("ddlProvince"), DropDownList).SelectedValue
e.Values("Province") = strProv
Dim strCity As String = CType(CType(sender, FormView).FindControl("ddlCity"), DropDownList).SelectedValue
e.Values("City") = strCity
e.Cancel = False
End Sub
Protected Sub FormView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewPageEventArgs)
If FormView1.CurrentMode = FormViewMode.Edit Then
Dim cs As ClientScriptManager = Page.ClientScript
Dim cstype As Type = Me.GetType()
' Check to see if the startup script is already registered.
If Not cs.IsStartupScriptRegistered(cstype, "AlertMessage") Then
Dim cstext As String = "alert('You cannot navigate to another page while in Edit mode. Please complete the Edit operation first.');"
cs.RegisterStartupScript(cstype, "AlertMessage", cstext, True)
End If
lblStatus.Text = "Your last operation (navigating to another page) has failed because you are in Edit mode."
e.Cancel = True
End If
End Sub
Protected Sub odsAddresses_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
'set the current page to the newly inserted record after inserting
'by using the returnValue, which in my sql would return the row number
'based on ordering by the Primary Key to set the page index of the FormView
FormView1.PageIndex = Convert.ToInt32(e.Command.Parameters("@RowNum").Value)
End Sub
Protected Sub FormView1_ItemCommand(ByVal sender As Object, ByVal e As FormViewCommandEventArgs)
If e.CommandName.Equals("Cancel") Then FormView1.ChangeMode(FormViewMode.ReadOnly)
End Sub
Private Sub AddEmptyItem(ByRef ddl As DropDownList)
Dim li As New System.Web.UI.WebControls.ListItem("Make a Selection", "")
ddl.Items.Insert(0, li)
End Sub
</script>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="MainContent">
<table>
<tr>
<td>
<h3>
Demo for 2-way databinding cascading lists within a FormView</h3>
<asp:FormView ID="FormView1" runat="server" DataKeyNames="PK_ID" DataSourceID="sqldsAddresses"
AllowPaging="True" OnItemUpdating="FormView1_ItemUpdating" OnItemInserting="FormView1_ItemInserting"
OnItemCommand="FormView1_ItemCommand" OnPageIndexChanging="FormView1_PageIndexChanging">
<EditItemTemplate>
<%---------------------EditItemTemplate Object datasources definitions-----------------------------------
Notice that the datasources are specific to this template and are declared within its boundaries
--------------------------------------------------------------------------------------------------------%>
<asp:SqlDataSource ID="sqldsCountries" runat="server" ConnectionString="<%$ ConnectionStrings:LoveYourEnemies %>"
SelectCommand="SELECT Condition AS Country FROM Attribute_Table WHERE (Category = 'Country-Province') GROUP BY Condition">
</asp:SqlDataSource>
<asp:SqlDataSource ID="sqldsProvinces" runat="server" ConnectionString="<%$ ConnectionStrings:LoveYourEnemies %>"
SelectCommand="SELECT Detail AS Province FROM Attribute_Table WHERE (Category='Country-Province') AND Condition=@Country">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCountry" Name="Country" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="sqldsCities" runat="server" ConnectionString="<%$ ConnectionStrings:LoveYourEnemies %>"
SelectCommand="SELECT Detail as City FROM Attribute_Table WHERE Category='Province-City' AND Condition=@Province">
<SelectParameters>
<asp:ControlParameter ControlID="ddlProvince" Name="Province" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<%-------------- end of the datasource difinitions for the EditItemTemplate ----------%>
<%------------- Begin of markup for the EditItemTemplate -----------------------------------%>
<%-- create a panel to be able to mark the Update hyperlink as the default button so that if
the user presses the Enter key while editing the Update button will clicked --%>
<asp:Panel ID="PanelForEditTemplate" runat="server" DefaultButton="UpdateButton">
<table class="FormViewSubStyel1">
<caption style="color: Black; background-color: gainsboro;">
FormView in Edit mode</caption>
<thead>
<tr>
<th>
ID
</th>
<th>
<asp:Label ID="lblID" runat="server" Text='<%# Bind("PK_ID") %>'></asp:Label>
</th>
<th class="Origin">
Original Values</th>
</tr>
</thead>
<tr>
<td class="label2">
Company
</td>
<td colspan="2">
<asp:TextBox MaxLength="50" Width="300" ID="txtCompany" runat="server" Text='<%# Bind("Company") %>'></asp:TextBox>
<%-- Let's add a requiredfieldvalidator to ensure that edited values are not empty
but notice to set the CausesValidation=false on the Cancel CommandButton --%>
<asp:RequiredFieldValidator ID="valCompany" runat="Server" ControlToValidate="txtCompany"
ForeColor="white" ErrorMessage="Cannot leave the company name empty" Text="*"
SetFocusOnError="true" Display="Dynamic"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="label2">
Street Address
</td>
<td colspan="2">
<asp:TextBox ID="txtStreet" MaxLength="50" Width="300" runat="server" Text='<%# Bind("Street") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="valStreet" runat="Server" ControlToValidate="txtStreet"
ForeColor="white" ErrorMessage="Cannot leave the street address empty" Text="*"
SetFocusOnError="true" Display="Dynamic"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="label2">
Country
</td>
<td>
<asp:DropDownList ID="ddlCountry" runat="server" DataSourceID="sqldsCountries" AutoPostBack="True"
DataTextField="Country" DataValueField="Country" SelectedValue='<%# Bind("Country") %>'>
</asp:DropDownList>
</td>
<td class="Origin">
<asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
</td>
</tr>
<tr>
<td class="label2">
Province/State
</td>
<td>
<asp:DropDownList ID="ddlProvince" runat="server" DataSourceID="sqldsProvinces" AutoPostBack="true"
DataTextField="Province" DataValueField="Province" OnDataBound="ddlProvince_DataBound">
</asp:DropDownList>
<asp:RequiredFieldValidator ForeColor="white" ID="valddlProvince" runat="server"
ErrorMessage="Cannot leave the province selection blank" Text="*" Display="Dynamic"
ControlToValidate="ddlProvince"></asp:RequiredFieldValidator>
</td>
<td class="Origin">
<asp:Label ID="lblProvince" runat="server" Text='<%# Eval("Province") %>'></asp:Label>
</td>
</tr>
<tr>
<td class="label2">
City
</td>
<td>
<asp:DropDownList ID="ddlCity" runat="server" DataSourceID="odsCities" OnDataBound="ddlCity_DataBound">
</asp:DropDownList>
<asp:RequiredFieldValidator ForeColor="white" ID="valddlCity" runat="server" ErrorMessage="Cannot leave the city selection blank"
Text="*" Display="Dynamic" ControlToValidate="ddlCity"></asp:RequiredFieldValidator>
</td>
<td class="Origin">
<asp:Label ID="lblCity" runat="server" Text='<%# Bind("City") %>'></asp:Label>
</td>
</tr>
</table>
<asp:LinkButton ID="UpdateButton" runat="server" CommandName="Update" Text="Update"></asp:LinkButton>
<asp:LinkButton ID="btnCancel" CausesValidation="false" runat="server" CommandName="Cancel"
Text="Cancel"></asp:LinkButton>
</asp:Panel>
<%---------------------- end of markup for the EditItemTemplate ---------------------------%>
</EditItemTemplate>
<ItemTemplate>
<table class="FormViewStyle1">
<caption style="color: Black; background-color: gainsboro;">
FormView in ReadOnly mode</caption>
<tr>
<td>
Company
</td>
<td>
<asp:Label ID="lblCompany" runat="server" Text='<%# Eval("Company") %>'></asp:Label>
</td>
</tr>
<tr>
<td>
Street Address
</td>
<td>
<asp:Label ID="lblStreet" runat="server" Text='<%# Eval("Street") %>'></asp:Label>
</td>
</tr>
<tr>
<td>
Country
</td>
<td>
<asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
</td>
</tr>
<tr>
<td>
Province/State
</td>
<td>
<asp:Label ID="lblProvince" runat="server" Text='<%# Eval("Province") %>'></asp:Label>
</td>
</tr>
<tr>
<td>
City
</td>
<td>
<asp:Label ID="lblCity" runat="server" Text='<%# Eval("City") %>'></asp:Label>
</td>
</tr>
</table>
<asp:LinkButton ToolTip="Click here to Edit the record" Text="Edit" runat="server"
ID="Edit" CommandName="Edit"></asp:LinkButton>
<asp:LinkButton ToolTip="Click here to insert a new Record" Text="Insert" runat="server"
ID="Insert" CommandName="New"></asp:LinkButton>
</ItemTemplate>
<InsertItemTemplate>
<%-----------------------------------------------------------------------------------------------------
Object Datasources defintions for the InsertItemTemplate
Notice that these definitions are specific to this tempalte and are only visible within its defintition
--------------------------------------------------------------------------------------------------------%>
<asp:SqlDataSource ID="sqldsCountries" runat="server" ConnectionString="<%$ ConnectionStrings:LoveYourEnemies %>"
SelectCommand="SELECT Condition AS Country FROM Attribute_Table WHERE (Category = 'Country-Province') GROUP BY Condition">
</asp:SqlDataSource>
<asp:SqlDataSource ID="sqldsProvinces" runat="server" ConnectionString="<%$ ConnectionStrings:LoveYourEnemies %>"
SelectCommand="SELECT Detail AS Province FROM Attribute_Table WHERE (Category='Country-Province') AND Condition=@Country">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCountry" Name="Country" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="sqldsCities" runat="server" ConnectionString="<%$ ConnectionStrings:LoveYourEnemies %>"
SelectCommand="SELECT Detail as City FROM Attribute_Table WHERE Category='Province-City' AND Condition=@Province">
<SelectParameters>
<asp:ControlParameter ControlID="ddlProvince" Name="Province" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<%-- create a panel to be able to mark the Update hyperlink as the default button so that if
the user presses the Enter key while inserting the insert button will clicked --%>
<asp:Panel ID="PanelForEditTemplate" runat="server" DefaultButton="btnInsert">
<table class="FormViewSubStyel1">
<caption style="color: Black; background-color: gainsboro;">
FormView in Insert mode</caption>
<tr>
<td class="label2">
Company
</td>
<td>
<asp:TextBox MaxLength="50" Width="300" ID="txtCompany" runat="server" Text='<%# Bind("Company") %>'></asp:TextBox>
<%-- Let's add requiredfieldvalidators to ensure that newly inserted records receive values
but notice to set the CausesValidation=false on the Cancel CommandButton --%>
<asp:RequiredFieldValidator ID="valCompany" runat="Server" ControlToValidate="txtCompany"
ForeColor="white" ErrorMessage="Cannot leave the company selection empty" Text="*"
SetFocusOnError="true" Display="Dynamic"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="label2">
Street Address
</td>
<td>
<asp:TextBox ID="txtStreet" MaxLength="50" Width="300" runat="server" Text='<%# Bind("Street") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="valStreet" runat="Server" ControlToValidate="txtStreet"
ForeColor="white" ErrorMessage="Cannot leave the street selection empty" Text="*"
SetFocusOnError="true" Display="Dynamic"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="label2">
Country
</td>
<td>
<%-- when we insert a new record there is no country value to be bound upon
displaying the empty record. Therefore let's add a default System.Web.UI.WebControls.ListItem with a null
value so that we can validate it using a RequiredFieldValidator to ensure that
the user will enter a value in it. I will use here the AppendDataBoundItems
property of the dropdownlist so that any databound items to be created will not
replace the default System.Web.UI.WebControls.ListItem that I just created. --%>
<asp:DropDownList ID="ddlCountry" runat="server" DataSourceID="sqldsCountries" AutoPostBack="True"
DataTextField="Country" DataValueField="Country" SelectedValue='<%# Bind("Country") %>'
AppendDataBoundItems="true">
<asp:ListItem Value="">Select a Country</asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ForeColor="white" ID="valddlCountry" runat="server" ErrorMessage="Cannot leave the country blank"
Text="*" Display="Dynamic" ControlToValidate="ddlCountry"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="label2">
Province/State
</td>
<td>
<asp:DropDownList ID="ddlProvince" runat="server" DataSourceID="sqldsProvinces" AutoPostBack="true"
OnDataBound="ddlProvince_DataBound" AppendDataBoundItems="true" DataTextField="Province"
DataValueField="Province">
<asp:ListItem Value="">Select a Province</asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ForeColor="white" ID="valddlProvince" runat="server"
ErrorMessage="Cannot leave the province selection blank" Text="*" Display="Dynamic"
ControlToValidate="ddlProvince"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="label2">
City
</td>
<td>
<asp:DropDownList ID="ddlCity" runat="server" DataSourceID="sqldsCities" OnDataBound="ddlCity_DataBound"
DataTextField="City" DataValueField="City" AppendDataBoundItems="true">
<asp:ListItem Value="">Select a City</asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ForeColor="white" ID="valddlCity" runat="server" ErrorMessage="Cannot leave the city selection blank"
Text="*" Display="Dynamic" ControlToValidate="ddlCity"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
<asp:LinkButton ID="btnInsert" runat="server" CommandName="Insert" Text="Insert"></asp:LinkButton>
<asp:LinkButton ID="btnCancel" runat="server" CausesValidation="false" CommandName="Cancel"
Text="Cancel"></asp:LinkButton>
</asp:Panel>
</InsertItemTemplate>
</asp:FormView>
<%-- The Datasource definition for the FormView is declared outside of the FormView
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasourcestatuseventargs(VS.80).aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp
CREATE PROCEDURE dbo.InsertAddress
@Company nvarchar(50),
@Street nvarchar(50),
@Country nvarchar(50),
@Province nvarchar(50),
@City nvarchar (50),
@PK_ID INT OUTPUT
as
DECLARE @RowNum int;
INSERT INTO Addresses(Company, Street, Country, Province, City)
VALUES(@Company, @Street, @Country, @Province, @City);
SELECT @PK_ID= SCOPE_IDENTITY();
SELECT @RowNum=rownum
FROM(
SELECT ROW_NUMBER() OVER(ORDER BY PK_ID) as rownum, PK_ID
FROM Addresses
) AS A
WHERE PK_ID=@PK_ID
Order By PK_ID;
RETURN @RowNum;
--------------------------------------------------------------------------------------------------%>
<asp:SqlDataSource ID="sqldsAddresses" runat="server" ConnectionString="<%$ ConnectionStrings:LoveYourEnemies %>"
SelectCommand="SELECT * FROM Addresses" UpdateCommand="Update Addresses SET Company=@Company, Street=@Street, Country=@Country, Province=@Province, City=@City WHERE PK_ID=@PK_ID"
InsertCommand="InsertAddress" InsertCommandType="StoredProcedure" OnInserted="sqldsAddresses_Inserted">
<UpdateParameters>
<asp:Parameter Name="PK_ID" Type="Int32" />
<asp:Parameter Name="Company" Type="String" />
<asp:Parameter Name="Street" Type="String" />
<asp:Parameter Name="Country" Type="String" />
<asp:Parameter Name="Province" Type="String" />
<asp:Parameter Name="City" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="Company" Type="String" />
<asp:Parameter Name="Street" Type="String" />
<asp:Parameter Name="Country" Type="String" />
<asp:Parameter Name="Province" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="PK_ID" Type="Int32" Direction="Output" DefaultValue="0" />
<asp:Parameter Name="RowNum" Type="Int32" Direction="ReturnValue" DefaultValue="0" />
</InsertParameters>
</asp:SqlDataSource>
<%-- let's add a summary validation control to display a pop up message --%>
<asp:ValidationSummary ID="valSumm" runat="server" ShowMessageBox="true" ShowSummary="false" />
<%-- Let's add a label to display any status--%>
<asp:Label ID="lblStatus" runat="server" EnableViewState="false" CssClass="ErrMessage"></asp:Label>
</td>
</tr>
</table>
</asp:Content>