Evonet Consulting

Using Active Directory to authenticate users to your ASP.NET Web Site.

bartek.nospam@nospam.evonet.com.au (bartek) — Wed, 05 Nov 2008 15:55:15 +1100

ASP.NET membership provides the ability to authenticate users to your web application using forms based authentication against a database of users or directory services. This article explains the steps required to provide authentication against Active Directory, either for new sites with no authentication, or for existing sites using database authentication.

Step 1 – Set up the Active Directory connection string

The Active Directory connection string is simliar to the database connection string used in ASP.NET, except that it references an LDAP address. The connection string is specified in the web.config file.

The following string will authenticate users in the entire company.com.au tree:

<connectionStrings>
    <add name="ADConnectionString" 
    connectionString="LDAP://company.com.au/DC=company,DC=com,DC=au"/>
</connectionStrings>

If you want to restrict authentication to a particular OU, then you specify it in the connection string like so:

<connectionStrings>
    <add name="ADConnectionString" 
    connectionString="LDAP://dept.company.com.au/CN=dept,DC=company,DC=com,DC=au"/>
</connectionStrings>

Step 2 – Configure the Membership provider

In your web.config file, create or change the following entry to configure the Membership provider for Active Directory within the <system.web> section:

<membership defaultProvider="MyADMembershipProvider">
    <providers>
        <add name="MyADMembershipProvider" 
        type="System.Web.Security.ActiveDirectoryMembershipProvider, 
        System.Web, Version=2.0.0.0, Culture=neutral, 
        PublicKeyToken=b03f5f7f11d50a3a" 
        connectionStringName="ADConnectionString" 
        attributeMapUsername="sAMAccountName"/>
    </providers>
</membership>

The connectionStringName attribute should match the name of the connection string you created in Step 1.

You can configure the credentials used to access Active Directory using the connectionUserName and connectionPassword attributes. If you leave these blank then your application's process identity is used to access Active Directory, regardless of whether your application uses impersonation.

Step 3 – Configure the Authentication and Authorization parameters

In your web.config file, create or change the following entry to configure the authentication and authorization parameters for Active Directory within the <system.web> section:

<authentication mode="Forms">
    <forms name=".ADAuthCookie" timeout="43200"/>
</authentication>
<authorization>
    <deny users="?"/>
    <allow users="*"/>
</authorization>

The authorization settings above require every user to authenticate before accessing your web application. ASP.NET will automatically redirect these users to a Login.aspx page.

Step 4 – Create a Login page

The simplest way of creating the login page (which must be called Login.aspx) is by using the ASP.NET Login control as the following example demonstrates:

<form id="form1" runat="server">
<asp:Login ID="Login1" runat="server">
    <LayoutTemplate>
    <p>
        <asp:TextBox runat="server" ID="Username" />
    </p>
    <p>
        <asp:TextBox runat="server" ID="Password" TextMode="Password" />
    </p>
    <p>
        <asp:CheckBox ID="RememberMe" runat="server" />
    </p>
        <asp:Button ID="btnLogin" runat="server" CommandName="Login" />
    </LayoutTemplate>
</asp:Login>
<br />
<asp:Label ID="lblLoginErrorDetails" runat="server" />
</form>

If you are using the Login control, you MUST name your user name and password text boxes exactly as shown in the example.

The Login control can also provide error checking through the built in LoginError subroutine:

Protected Sub Login1_LoginError(ByVal sender As Object, 
    ByVal e As System.EventArgs) Handles Login1.LoginError
If Login1.UserName = String.Empty And Login1.Password = String.Empty Then
    lblLoginErrorDetails.Text = "Please enter your username and password."
ElseIf Login1.UserName = String.Empty Then
    lblLoginErrorDetails.Text = "Please enter your username."
ElseIf Login1.Password = String.Empty Then
    lblLoginErrorDetails.Text = "Please enter your password."
Else
    Dim userInfo As MembershipUser = Membership.GetUser(Login1.UserName)
    LoginError.Visible = "True"
    If userInfo Is Nothing Then
        lblLoginErrorDetails.Text = "There is no user in the database 
            with the username " & Login1.UserName & ". Please try again."
    Else
        If Not userInfo.IsApproved Then
            lblLoginErrorDetails.Text = "Your account has not yet been 
                approved. Please try again later."
        ElseIf userInfo.IsLockedOut Then
            lblLoginErrorDetails.Text = "Your account has been locked 
                out due to maximum incorrect login attempts. Please 
                contact the site administrator."
        Else
            lblLoginErrorDetails.Text = "Your password is incorrect, 
                please try again."
        End If
    End If
End If
End Sub

That’s all you should need to allow your users to log on. This is just the beginning of using Active Directory authentication for your web site, stay tuned for further articles in this series!

Bartek

Creating a Content Management System for your ASP.NET Web Site

bartek.nospam@nospam.evonet.com.au (bartek) — Mon, 01 Sep 2008 00:46:41 +1100

When developing web sites for clients, a simple yet powerful value added proposition is to allow your customers the ability to easily change text on their web site, such as updating contact details, news items, or promotions.

This article will show you how to integrate the popular FCKeditor HTML Text editor into an ASP.NET site to provide your users with the ability to edit their web site without them having to access the underlying HTML or source files.

The Content Management System (CMS) we will be designing in this tutorial works by storing your page HTML in a database and then using a Formview to bind the HTML to the FCKeditor text control, just like using a normal text box.

Step 1: Create the Database

For this example I will be setting up a simple SQL Server database which holds the HTML content for the pages that the customer will be editing.

I've created a simple table, T_Pages which has 2 columns, one to store the page name, and the other to store the HTML.

The PageName field stores the name of the aspx file that we are editing, and the HTML field stores the raw HTML which makes up the page:

Step 2: Set up FCKeditor

Download the latest full version of FCKeditor from http://www.fckeditor.net/download. Extract the files to a directory called fckeditor in the root of your web site.

Download the FCKeditor ASP.NET Control from http://www.fckeditor.net/download. Extract FredCK.FCKeditorV2.dll from the bin/Release/2.0/ folder and place it in the /bin directory in your web site.

Step 3: Create a site administration page to edit page content

Now we create a simple aspx page that contains a Formview. Make sure you secure this page so anonymous users cannot edit the page text!

At the top of the page, register the FCKeditor control:

<%@ Register 
    Assembly="FredCK.FCKeditorV2" 
    Namespace="FredCK.FCKeditorV2" 
    TagPrefix="FCKeditorV2" %>

Now create a Formview with an EditItemTemplate which binds the FCKeditor control to the HTML field in your database depending on what page is to be edited. The FCKeditor control has a large number of settings (see http://docs.fckeditor.net/FCKeditor_2.x/Developers_Guide) but the main ones that we are interested in are:

ToolbarSet - This allows you to create a custom toolbar that only shows the font manipulation controls that you want your customers to access (We will be discussing this item and customising it further below, under Step 5 - Customising FCKEditor.)
EditorAreaCSS - This allows you to specify what the text inside the FCKeditor control looks like.

The Formview will also need a Save Button which runs the update command to save changes. I also recommend a Cancel button, but have left it out of this example.

The Formview code then looks something like this:

<asp:FormView DataKeyNames="page" DataSourceID="dsSiteAdministration" 
    ID="FormView1" runat="server" DefaultMode="Edit">
    <EditItemTemplate>
        <FCKeditorV2:FCKeditor ID="FCKeditor1" runat="server" 
        ToolbarSet="MyToolbarSet"
        Value='<%# Bind("html") %>' EditorAreaCSS="/css/editor.css">
        </FCKeditorV2:FCKeditor>
        <asp:LinkButton ID="btnSave" runat="server" CommandName="Update"
            Text="Save changes" />
    </EditItemTemplate>
</asp:FormView>

You also need to create the DataSource. I use the SQLDataSource here, but recommend ObjectDataSources for production environments:

<asp:SqlDataSource ID="dsSiteAdministration" runat="server" 
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
SelectCommand="SELECT * FROM [T_Pages] WHERE page=@page"
UpdateCommand="UPDATE [T_Pages] SET HTML=@HTML WHERE page=@page">
    <SelectParameters>
        <asp:QueryStringParameter Name="page" QueryStringField="page" Type="string" />
    </SelectParameters>
    <UpdateParameters>
        <asp:Parameter Name="html" Type="string" />
        <asp:QueryStringParameter Name="page" QueryStringField="page" Type="string" />
    </UpdateParameters>
</asp:SqlDataSource>

Our application will call the SiteAdministration.aspx page with a parameter telling it which page to retrieve from the database for editing. such as SiteAdministration.aspx?page=AboutUs.

You should put checks in your page to ensure that a valid QueryStringParameter is being passed.

Step 4: Modify your pages so they retrieve content from your database

For existing pages that you wish to allow editing on, simply create a new row in your T_Pages database table with the PageName column containing the page name, and the html column containing all the editable part of the page text, including all html content.

Then, simply modify your pages by putting in a repeater to display the HTML from your database:

<html>
<head>
<title>Title</title>
</head>
<body>
<asp:Repeater runat="server" ID="Repeater1" DataSourceID="dsPage">
    <ItemTemplate>
        <asp:Label ID="lblPage" runat="server" Text='<%# Eval("HTML") %>' />
    </ItemTemplate>
</asp:Repeater>
</body>
</html>

The Datasource simply looks at what page you specify (using the DefaultValue setting in the <Parameter> section) and retrieves the appropriate HTML:

<asp:SqlDataSource ID="dsPage" runat="server" 
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
SelectCommand="SELECT * FROM [T_Pages] WHERE page=@page" >
    <SelectParameters>
        <asp:Parameter Name="page" Type="string" DefaultValue="AboutUs" />
    </SelectParameters>
</asp:SqlDataSource>

We will also show a link to edit the page. For production environments, you should set this link up to only appear if the user has authenticated as a site administrator. The script below removes the '.aspx' extension from the current page and then creates a hyperlink with the remainder as a query parameter to the Site Administration page.

If HttpContext.Current.User.Identity.IsAuthenticated Then
    Dim MyPage As String = 
        LCase(Left(System.IO.Path.GetFileName(Request.ServerVariables("SCRIPT_NAME")),
        InStr(System.IO.Path.GetFileName(Request.ServerVariables("SCRIPT_NAME")), ".") - 1))
    Response.Write("<a href='/admin/siteadministration.aspx?page=" + MyPage + "'>Edit this page</a>")
End If

So let's recap with an example 'About Us' page called aboutus.aspx:

The SQL Database contains a record for 'aboutus' as the Page and the formatted HTML for the page in the HTML column.
Visitors to aboutus.aspx will get the formatted HTML page from the database via a Repeater control.
Site Administrators will see a 'Edit this Page' link which will go to siteadministration.aspx?page=aboutus.
Clicking on 'Edit this Page' will show the FCKeditor text box populated with the HTML from the database in a WYSIWYG format.

Step 5: Customising the FCKeditor Toolbar

To finish off the ToolbarSet can be customised to show only the text manipulation features you approve. The default toolbar set is defined in the \fckeditor\fckconfig.js file as follows:

FCKConfig.ToolbarSets["Default"] = [
    ['Source','DocProps','-','Save','NewPage','Preview','-','Templates'],
    ['Cut','Copy','Paste','PasteText','PasteWord','-','Print','SpellCheck'],
    ['Undo','Redo','-','Find','Replace','-','SelectAll','RemoveFormat'],
    ['Form','Checkbox','Radio','TextField','Textarea','Select','Button','ImageButton'],
    '/',
    ['Bold','Italic','Underline','StrikeThrough','-','Subscript','Superscript'],
    ['OrderedList','UnorderedList','-','Outdent','Indent','Blockquote'],
    ['JustifyLeft','JustifyCenter','JustifyRight','JustifyFull'],
    ['Link','Unlink','Anchor'],
    ['Image','Flash','Table','Rule','Smiley','SpecialChar','PageBreak'],
    '/',
    ['Style','FontFormat','FontName','FontSize'],
    ['TextColor','BGColor'],
    ['FitWindow','ShowBlocks','-','About']        // No comma for the last row.
] ;

which shows:

You may wish to cut down some of these options by defining your own toolbar set. For example:

FCKConfig.ToolbarSets["Mytoolbarset"] = [
    ['Bold','Italic','-','OrderedList','UnorderedList','-','Link','Unlink']
] ;

You need to reference your new ToolbarSet in your ASP.NET control, as discussed in Step 3.

That's it! There are many more options to play around with, and the whole source code is available to customise even futher. For more details, check out the FCKeditor web site.

Bartek

Setting up SQL Server 2008 for an ASP.NET website on IIS 7.0

bartek.nospam@nospam.evonet.com.au (bartek) — Sun, 24 Aug 2008 16:22:17 +1100

SQL Server 2008 Express edition with Tools has finally been released (It was launched on August 11th, but only today are the management tools available). As I've been building web servers today, I've put together this comprehensive guide to setting up your website to work with SQL Server 2008.

Step 1: Prerequisities

Firstly you need Windows 2008 Server and IIS 7.0 installed. Installing Windows Server 2008 is pretty straight-forward and I won't cover it here (there is an excellent article here), although be aware that as you are using ASP.NET and SQL Server you cannot install server core. Installing the IIS 7.0 server role is also quite easy, (another great article here)
Install Windows PowerShell from the Features section in the Windows Server 2008 Server Manager.
Install .NET Framework 3.5 SP1 from here.
Install Windows Installer 4.5 from here.
If you have AJAX enabled sites running on ASP.NET 2.0 then Install ASP.NET AJAX Extensions 1.0 from here.
Download SQL Server 2008 Express with Tools from here. This version includes:

SQL Server database engine - create, store, update and retrieve your data
SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases

While this guide concentrates on the Express version of SQL Server 2008, you need to ensure that it meets the requirements in your specific environment. The SQL Server 2008 blog has an article which discusses this issue (SQL Server Express and Hosting)

Step 2: Installing SQL Server 2008

Start the install from your CD or downloaded file.
Select New SQL Server stand-alone installation of add features to an existing installation
The setup program will check that you have the necessary pre-requisities in place for the SQL Server setup support files. Click OK.
Click Install to install the SQL Server setup support files.
SQL Setup will then check that you have all the necessary pre-requisities in place for the SQL Server installation. I received a warning that Windows Firewall needs port 1433 open for remote TCP/IP connection to SQL Server. Click Next.
Nothing to do on the next step if you are using the Express Edition, other you need to enter a valid product key. Click Next.
Accept the license terms and click Next.
Select the features you want to install and cick Next. At a minimum I recommend installing the Database Engine Services and the Management Tools.
Select an instance to install your database server into and a location. In the web.config file for your ASP.NET web site, if SQL is not installed in the default instance then you need to set Data Source = SERVERNAME\INSTANCENAME for your database connection string.
SQL Setup will confirm you have enough disk space. Click Next.
Configure your service account and collation settings and click Next. Use the built-in System account and don't change the default collation unless you have databases with special requirements.
Configure Mixed Mode authentication and provide a password for the sa account. This allows you to use either windows or sql authentication depending on your requirements. Choose which accounts have administrative privilages to the SQL Server database engine. Click Next.
Tick the checkboxes if you would like to send error reports and usage data to Microsoft, and click Next.
Setup will then check if the installation process will be blocked. Check the report and click Next.
Verify the SQL Server features to install and click Install.
Once the setup finishes, check Windows Update to see if there are any patches that need to be installed.

Step 3: Allowing TCP/IP Connections to SQL Server

By default, only the Named Pipes protocal is allowed to connect to the SQL Server. To allow TCP/IP connections, perform the following steps:

Open the SQL Server Configuration Manager under the Microsoft SQL Server 2008, Configuration Tools start menus.
Expand the SQL Server Network Configuration node and select Protocols for <InstanceName>. Double click on the TCP/IP Protocol and change it to Enabled.
Restart SQL Server for the changes to take effect.

Step 4: Attaching a Database

The SQL Server 2008 Management tools are almost identical to the SQL Server 2005 ones. Open up the Management Tools and login to your database. You will then see the following:
Right-click on the Databases node and select New Database to create a new database, or Attach to attach an existing one. If you are attaching a database then click the Add button, browse for your .mdf file and click OK twice to attach the database. You will be asked to also add the location of any Full-Text catalogs if they exist.

Step 5: Creating a Login for your database

By default, only Administrators have access to your database. You need to now configure your database to allow Internet users access to read and (if required) write to your data. You can either use Windows authentication or SQL Server authentication. Windows authentication uses your local server or Active Directory user account to connect, and doesn't require a password to be kept in the web.config file. SQL Server authentication is easier to seperate your user accounts for different databases and applications easily.

To set up Windows Authentication:

On a standalone Windows 2008 Web Server, the ASP.NET service runs as the NT AUTHORITY\NETWORK SERVICE account. You need to map this account to SQL Server:

In the SQL Server Management Tools, Right click on Logins under the Security Node and select New Login.
Type NT AUTHORITY\NETWORK SERVICE in the Login Name box and select Windows Authentication. Click Ok.

To set up SQL Authentication:

In the SQL Server Management Tools, Right click on Logins under the Security Node and select New Login.
Type the name of your database user in the Login Name box and select SQL Authentication. Set a password and untick Enforce Password Policy. Click Ok.

Step 6: Assigning the user permissions to your database

Now you need to assign this login the appropriate permissions in your database. To do this, expand the Databases node and then expand Security. Right-click the Users node and choose New User.
Give your Login whatever name you like and in the Login Name box type in either the name of your database user if using SQL authentication or NT AUTHORITY\NETWORK SERVICE if you are using Windows authentication (or browse to select your account). Under Database Role Membership select db_datareader (and db_datawriter if your users are performing INSERT and UPDATE SQL queries).
Click OK.

Step 7: Configuring your database connection string

You can set your database connection string by either manually editing the web.config file, or using the IIS built in Connection Strings applet.

Open Internet Information Services (IIS) Manager from the Administrative Tools start menu.
Browse to your website and double-click on Connection Strings in the right hand pane.
Either Add a new connection string or right click an existing one to Edit.
Set the name of your server and database appropriately, and then choose the credentials to match your database user. If you are using SQL Authentication then you need to set the username and password you configured.

That's it! I hope this guide helps, and please let me know if I've missed anything!

Bartek

503 "service unavailable" errors when IIS running in 32-bit mode on 64-bit Windows Server 2008

bartek.nospam@nospam.evonet.com.au (bartek) — Fri, 22 Aug 2008 10:40:41 +1100

Yesterday I managed to take down my development web server.

I changed one of my customer's web sites from an SQL database to Microsoft Access, and as I am running 64 bit Windows Server 2008 and there is no 64-bit OleDB driver for Access, I had to change the Advanced Settings in the site's application pool to enable 32-bit applications:

Unfortunately, I then started getting Service Unavailable Errors: HTTP Error 503. The service is unavailable on all the sites on my development box. Changing the above 32-bit setting back to false didn't solve the problem.

Mike Volodarsky on his IIS 7.0 Server-Side blog has a great article describing the problems that I was having here and this is the first port of call for anyone having these same issues. My own problem however, ended up being that IIS seemed stuck in 32-bit compatibility mode after I enabled 32-bit application support, which then caused all my IIS worker processes to fail (Not sure why though!)

To resolve the issue, I ended up needing to run the following script in a command prompt:

cscript c:\inetpub\adminscripts\adsutil.vbs SET /w3svc/AppPools/Enable32BitAppOnWin64 False

After I restarted my application pools, everything worked again.

Hope this helps,

Bartek

Integrating Google Maps into an ASP.NET page

bartek.nospam@nospam.evonet.com.au (bartek) — Tue, 19 Aug 2008 18:08:23 +1100

While Microsoft have released their new Virtual Earth ASP.NET Control, I still haven't found a decent wrapper that converts addresses to langitude and longitude co-ordinates. There is a Geo coder web service available here (this shows the co-ordinates for the White House) but it seems to only work for US addresses, which doesn't help me at all.

So for the moment, I'm still using Google Maps, and have prepared the following to show you how to integrate Google Maps into your ASP.NET pages.

Demo | Code

Step 1. Get a Google Maps API key

In order to use Google Maps on your site, you need to register for a free Google Maps API key from here: http://www.google.com/apis/maps/

Step 2. Download the SubGurim Google Maps wrapper dll

This one is the best Google Maps wrapper I have found so far http://en.googlemaps.subgurim.net/descargar.aspx. It is a commercial product (from $10), or you can put up with the overlayed text in the free versions.

Download the gmaps.dll file and add it to your \bin directory.

Step 3. Set up your aspx page

In your aspx page, add a few text boxes to gather the address details. You can simply use one textbox, or split the address to enable validation for suburbs, countries, etc. I haven't included any form validation in my example.

<p style="text-align:right; margin-right:300px">
    Street Address: 
    <asp:textbox ID="txtStreetAddress" runat="server" Width="150px" /><br />
    Suburb: 
    <asp:textbox ID="txtSuburb" runat="server" Width="150px" /><br />
    Country: 
    <asp:textbox ID="txtCountry" runat="server" Width="150px" /><br /><br />
    <asp:Button Text="Show Map" ID="lnkShowMap" runat="server" />
</p>

You need to add your Google API key to your web.config file like so:

<appSettings>
    <add key="googlemaps.subgurim.net" value="YourGoogleMapsAPIKeyHere" />
</appSettings>

And finally, you need to register the SubGurim wrapper at the top of your page (or in your web.config if you have a number of pages that display maps):

<%@ Register Assembly="GMaps" Namespace="Subgurim.Controles" TagPrefix="cc1" %>

Step 4: Add code to display the map

Finally, add code that collates your address fields and calls the Google Maps wrapper.

Protected Sub lnkShowMap_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Handles lnkShowMap.Click
    Dim strFullAddress As String
    Dim sMapKey As String = 
    ConfigurationManager.AppSettings("googlemaps.subgurim.net")
    Dim GeoCode As Subgurim.Controles.GeoCode

    ' Combine our address fields to create the full address.  The street, 
    ' suburb and country should be seperated by  periods (.)
    strFullAddress = txtStreetAddress.Text & ". " & txtSuburb.Text
        & ". " & txtCountry.Text

    ' Work out the longitude and latitude
    GeoCode = GMap1.geoCodeRequest(strFullAddress, sMapKey)
    Dim gLatLng As New Subgurim.Controles.GLatLng(GeoCode.Placemark.coordinates.lat, 
        GeoCode.Placemark.coordinates.lng)
    ' Display the map
    GMap1.setCenter(gLatLng, 16, Subgurim.Controles.GMapType.GTypes.Normal)
    Dim oMarker As New Subgurim.Controles.GMarker(gLatLng)
    GMap1.addGMarker(oMarker)
End Sub

That's it! Check out the demo or download the source code, and let me know if you find a good wrapper for the Virtual Earth ASP.NET control!

Bartek

Inserting a new record from the Listview's footer

bartek.nospam@nospam.evonet.com.au (bartek) — Sun, 17 Aug 2008 21:09:53 +1100

The Listview control introduced in ASP.NET 3.5 fixes a number of issues with the old Gridview and Repeater controls. In particular, one of my biggest problems with the Gridview was the inability to add an item through an empty row in the Gridview footer. While there are several workarounds to achieve this functionality, the Listview makes it nice and easy. This article demonstrates how.

Demo | Code

Step 1 - Create a Listview

Create a simple listview with a LayoutTemplate and ItemTemplate to display your data. Here is some sample code you can use:

<asp:ListView ID="Listview2" runat="server" DataKeyNames="ID" 
DataSourceID="dsListView" InsertItemPosition="LastItem">
<LayoutTemplate>
    <table cellpadding="0" cellspacing="0" class="ListviewTable" width="540">
        <tr>
            <th style="width: 40px">
                ID
            </th>
            <th style="width: 110px">
                First Name
            </th>
            <th style="width: 110px">
                Last Name
            </th>
            <th style="width: 120px">
                Department
            </th>
            <th style="width: 120px">
                Location
            </th>
            <th style="width: 40px">
            </th>
        </tr>
        <tr id="itemPlaceholder" runat="server" />
        <tr>
            <td colspan="5" align="center">
                Page:
                <asp:DataPager ID="DataPager1" runat="server" PageSize="12">
                    <Fields>
                        <asp:NumericPagerField ButtonCount="5" />
                    </Fields>
                </asp:DataPager>
            </td>
        </tr>
    </table>
</LayoutTemplate>
<ItemTemplate>
    <tr>
        <td>
            <%#Eval("ID")%>
        </td>
        <td>
            <%#Eval("FirstName")%>
        </td>
        <td>
            <%#Eval("LastName")%>
        </td>
        <td>
            <%#Eval("Location")%>
        </td>
        <td>
            <%#Eval("Department")%>
        </td>
        <td>
            
        </td>
    </tr>
</ItemTemplate>
</asp:ListView>

In the Listview control you'll see InsertItemPosition="LastItem" which puts the insert item template into the Listview's footer. You can use FirstItem to show the insert item template at the top of your data.

Step 2 - Create the SQL Data Source

My usual disclaimer here - I don't recommend using SQL Data Sources in a production environment, I instead prefer to seperate the data access layer and use Object Data sources. However, for the purposes of this demo, the SQKDataSource is a lot easier to demonstrate.

Make sure you include an InsertCommand and InsertParameters as shown below:

<asp:SqlDataSource ID="dsListview" ConnectionString="<%$ ConnectionStrings:MyConnection %>"
runat="server" SelectCommand="SELECT * FROM [T_Employees]" 
InsertCommand="Insert into T_Employees (firstname, lastname, location, department) 
values (@firstname, @lastname, @location, @department)">
    <InsertParameters>
        <asp:Parameter Name="FirstName" Type="String" />
        <asp:Parameter Name="LastName" Type="String" />
        <asp:Parameter Name="Location" Type="String" />
        <asp:Parameter Name="Department" Type="String" />
    </InsertParameters>
</asp:SqlDataSource>

Step 3 - Add the InsertItemTemplate

Finally, add an InsertDataTemplate which contains bound controls for your InsertParameters. I've also included validators here and a validation summary, to ensure that proper data is being entered into the database:

<InsertItemTemplate>
<tr>
    <td>
        &nbsp;
    </td>
    <td>
        <asp:TextBox ID="txtFirstName" runat="server" Width="80px" 
        Text='<%#Bind("FirstName")%>' />
        <asp:RequiredFieldValidator ID="RV1" ControlToValidate="txtFirstName"
            runat="server" ErrorMessage="First Name is required." Display="Dynamic" 
            ValidationGroup="InsertFields">*</asp:RequiredFieldValidator>
    </td>
    <td>
        <asp:TextBox ID="txtLastName" runat="server" Width="80px" 
        Text='<%#Bind("LastName")%>' />
        <asp:RequiredFieldValidator ID="RV2" ControlToValidate="txtLastName"
            runat="server" ErrorMessage="Last Name is required." Display="Dynamic" 
            ValidationGroup="InsertFields">*</asp:RequiredFieldValidator>
    </td>
    <td>
        <asp:TextBox ID="txtLocation" runat="server" Width="90px" 
        Text='<%#Bind("Location")%>' />
        <asp:RequiredFieldValidator ID="RV3" ControlToValidate="txtLocation"
            runat="server" ErrorMessage="Location is required." Display="Dynamic" 
            ValidationGroup="InsertFields">*</asp:RequiredFieldValidator>
    </td>
    <td>
        <asp:TextBox ID="txtDepartment" runat="server" Width="90px" 
        Text='<%#Bind("Department")%>' />
        <asp:RequiredFieldValidator ID="RV4" ControlToValidate="txtDepartment"
            runat="server" ErrorMessage="Department is required." Display="Dynamic" 
            ValidationGroup="InsertFields">*</asp:RequiredFieldValidator>
    </td>
    <td>
        <asp:LinkButton ID="LinkButton1" CommandName="Insert" Text="Add" 
        runat="server" CausesValidation="true" ValidationGroup="InsertFields" />
    </td>
</tr>
<asp:ValidationSummary ID="ValidationSummary1" runat="server" ShowMessageBox="true" 
ShowSummary="false" ValidationGroup="InsertFields" />
</InsertItemTemplate>

That's it! You now have text boxes in your Listview's footer that allow your users to insert items into your database. If you want to make the user press a button to display the Insert Item template, then in your Listview, set InsertItemPosition="None." Then, create an 'Add' button and in your code behind type 'Listview1.InsertItemPosition = InsertItemPosition.FirstItem'. After the insert you can then set 'Listview1.InsertItemPosition = InsertItemPosition.None' (Put this in your Listview1_ItemInserted Sub).

As usual, check out the demo or download the source code.

Good luck,

Bartek

Visual Studio 2008 Service Pack 1 and .NET Framework 3.5 Service Pack 1 Released

bartek.nospam@nospam.evonet.com.au (bartek) — Tue, 12 Aug 2008 16:10:52 +1100

Full details and download link at http://msdn.microsoft.com/en-us/vstudio/products/cc533447.aspx.

Visual Studio 2008 SP1 delivers:

Improved WPF designers
SQL Server 2008 support
ADO.NET Entity Designer
Visual Basic and Visual C++ components and tools (including an MFC-based Office 2007 style ‘Ribbon’)
Visual Studio Team System Team Foundation Server (TFS) addresses customer feedback on version control usability and performance, email integration with work item tracking and full support for hosting on SQL Server 2008
Richer JavaScript support, enhanced AJAX and data tools, and Web site deployment improvements

The .NET Framework 3.5 SP1 delivers:

Performance increases between 20-45% for WPF-based applications – without having to change any code
WCF improvements that give developers more control over the way they access data and services
Streamlined installation experience for client applications
Improvements in the area of data platform, such as the ADO.NET Entity Framework, ADO.NET Data Services and support for SQL Server 2008’s new features

Cheers,

Bartek

Maintaining Checkbox State in a Listview

bartek.nospam@nospam.evonet.com.au (bartek) — Sun, 10 Aug 2008 21:13:00 +1100

I wrote an article recently explaining how to create a Gridview that exports selected rows to Excel, and have received a number of queries about how to do the same with a Listview. The Listview, like the Gridview, doesn't automatically remember whether a checkbox is selected or not for each row, unless you store the value immediately in a database after each change, which is inefficient.

This example shows how to maintain checkbox state across paging and sorting within a Listview. The final steps in Gridview that exports selected rows to Excel will show you how to export these rows to Excel if you wish.

Demo | Code

Step 1 - Create a Listview

The ListView control was introduced in ASP.NET 3.5. It is an enhancement to the DataList and Repeater controls, and works by using templates to determine how records are formatted. This gives you the flexibility to display mulitple records in a horizontal or vertical grid, or in groups of 3 etc. For this example. the Listview I create will mimic the data display of a Gridview.

The basic layout of the Listview control is as follows:

<asp:ListView ID="ListView" runat="server">
    <LayoutTemplate>
        ...
    </LayoutTemplate>
    <ItemTemplate>
        ...
    </ItemTemplate>
</asp:ListView>

I am using a simple table of four data columns, with a fifth column that will display the checkboxes. I have also included a DataPager control which handles the Listview's paging.

<asp:ListView ID="Listview1" runat="server" DataKeyNames="ID" DataSourceID="dsListView">
    <LayoutTemplate>
        <table cellpadding="0" cellspacing="0" class="ListviewTable" width="540">
            <tr>
                <th style="width: 120px">
                    Fist Name
                </th>
                <th style="width: 120px">
                    Last Name
                </th>
                <th style="width: 120px">
                    Department
                </th>
                <th style="width: 120px">
                    Location
                </th>
                <th style="width: 60px; text-align: center">
                </th>
            </tr>
            <tr id="itemPlaceholder" runat="server" />
            <tr>
                <td colspan="5" align="center">
                    Page:
                    <asp:DataPager ID="DataPager1" runat="server" PageSize="12">
                        <Fields>
                            <asp:NumericPagerField ButtonCount="5" />
                        </Fields>
                    </asp:DataPager>
                </td>
            </tr>
        </table>
    </LayoutTemplate>
    <ItemTemplate>
        <tr>
            <td>
                <%#Eval("FirstName") %>
            </td>
            <td>
                <%#Eval("LastName") %>
            </td>
            <td>
                <%#Eval("Department") %>
            </td>
            <td>
                <%#Eval("Location") %>
            </td>
            <td style="text-align: center">
                <asp:CheckBox ID="chkSelect" runat="server" AutoPostBack="true" />
            </td>
        </tr>
    </ItemTemplate>
</asp:ListView>

Step 2: Create a list variable that will store ID's of selected rows

In your code behind file, create a Private Property that will store the ID's of your selected rows:

Private ReadOnly Property IDs() As List(Of Integer)
    ' Create a list of ID's that are selected.  ID's is the primary
    ' Key for this table
    Get
        If Me.ViewState("IDs") Is Nothing Then
            Me.ViewState("IDs") = New List(Of Integer)()
        End If
        Return CType(Me.ViewState("IDs"), List(Of Integer))
    End Get
End Property

Step 3: Create a Sub that will store the selected row ID's into your list variable

Now we create a Sub that I called AddRowstoIDList which goes through all the currently visible rows and adds the ones that have a checkbox selected to the private list variable created in step 2.

Protected Sub AddRowstoIDList()
    ' Loop through all the current items in the Listview
    For Each lvi As ListViewDataItem In ListView1.Items
        ' Find the checkbox in each row
        Dim chkSelect As CheckBox = CType(lvi.FindControl("chkSelect"), CheckBox)
        ' If the checkbox is ticked then add the corresponding ID to our private list
        If (Not (chkSelect) Is Nothing) Then
            ' Get the ID from the datakeynames property
                Dim ID As Integer = 
            Convert.ToInt32(Listview1.DataKeys(lvi.DisplayIndex).Value)
            If (chkSelect.Checked AndAlso Not Me.IDs.Contains(ID)) Then
                ' Add the ID to our list
                Me.IDs.Add(ID)
            ElseIf (Not chkSelect.Checked AndAlso Me.IDs.Contains(ID)) Then
                ' Not checked - remove the ID from our list
                Me.IDs.Remove(ID)
            End If
        End If
    Next
End Sub

This sub is then called whenever the Listview is paged or sorted - we want to get the current status of the items and update our list before the Listview refreshes:

Protected Sub ListView1_PagePropertiesChanging(ByVal sender As Object, 
        ByVal e As System.Web.UI.WebControls.PagePropertiesChangingEventArgs) 
        Handles Listview1.PagePropertiesChanging
    AddRowstoIDList()
End Sub

Protected Sub ListView1_Sorting(ByVal sender As Object, 
        ByVal e As System.Web.UI.WebControls.ListViewSortEventArgs) 
        Handles Listview1.Sorting
    AddRowstoIDList()
End Sub

Step 4: Make sure the checkboxes are displayed correctly when the Listview is refreshed

The next step is to set the status of each checkbox correctly when the Listview is refreshed, by a sort of paging operation for example. We do this by checking each item, finding the checkbox control, and checking it if the row ID exists in our list.

Protected Sub ListView1_ItemDataBound(ByVal sender As Object, 
        ByVal e As System.Web.UI.WebControls.ListViewItemEventArgs)
        Handles Listview1.ItemDataBound
    ' Get each Listview Item on DataBound
    Dim lvi As ListViewDataItem = e.Item
    If (lvi.ItemType = ListViewItemType.DataItem) Then
        ' Find the checkbox in the current row
        Dim chkSelect As CheckBox = CType(lvi.FindControl("chkSelect"), CheckBox)
        ' Make sure we're referencing the correct control
        If (Not (chkSelect) Is Nothing) Then
            ' If the ID exists in our list then check the checkbox
            Dim ID As Integer = Convert.ToInt32(ListView1.DataKeys(lvi.DisplayIndex).Value)
            chkSelect.Checked = Me.IDs.Contains(ID)
        End If
    End If
End Sub

That's it! The code is very similar to the last article about the Gridview, but there are some gotcha's, particularly in how to reference items within the Gridview.

Check out the Demo or download the source code.

Bartek Marnane

Gridview with highlighted search results

bartek.nospam@nospam.evonet.com.au (bartek) — Wed, 25 Jun 2008 22:03:48 +1100

Continuing on my Gridview series, the next project which had me reaching for Google was a requirement to highlight the returned search results in a Gridview.

Digging around, I found an article on 4guysfromRolla titled Highlighting Search Keywords in a DataGrid Web Control which formed the basis of my work. I ended up updating, simplying and modifing the code and decided to write a quick tutorial on how to highlight the text that a user searched for in the search results:

Demo | Code

Step 1: Create a Gridview

Create a simple Gridview with TemplateFields for the fields that you would like to search for. In my example I called the Gridview Gridview1 and created a TemplateField for my two search fields, First Name and Last Name. The other point of interest is that the Eval statement for these 2 fields is wrapped around a function that we're going to write called HighlightText.

<asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
AllowSorting="true" DataSourceID="dsGridview" Width="540px" PageSize="10">
<Columns>
    <asp:BoundField DataField="id" HeaderText="ID" SortExpression="id" />
    <asp:TemplateField HeaderText="First Name" SortExpression="FirstName">
        <ItemStyle Width="120px" HorizontalAlign="Left" />
        <ItemTemplate>
            <asp:Label ID="lblFirstname" Text='<%# HighlightText(Eval("FirstName")) %>' 
                runat="server" />
        </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
        <ItemStyle Width="120px" HorizontalAlign="Left" />
        <ItemTemplate>
            <asp:Label ID="lblLastname" Text='<%# HighlightText(Eval("LastName")) %>' 
            runat="server" />
        </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="Department" HeaderText="Department" 
        SortExpression="Department" ItemStyle-Width="130px" />
    <asp:BoundField DataField="Location" HeaderText="Location" 
        SortExpression="Location" ItemStyle-Width="130px" />
</Columns>
</asp:GridView>

Step 2: Create a datasource with a FilterExpression

In order to enable our search functionality, add a FilterExpression to the datasource. The FilterExpression that I'm using checks for the first name and last name against the Search Text box which will be created shortly. And yes, I know I shouldn't use SQL statements in my Datasource, this is merely to make the example easier to demonstrate. Normally I would use a multi-tier architecture with an ObjectDataSource such as something described by Imar Spaanjaars.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT * FROM People"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    FilterExpression="firstname like '%{0}%' or lastname like '%{1}%'">
    <FilterParameters>
        <asp:ControlParameter Name="firstname" ControlID="txtSearch" PropertyName="Text" />
        <asp:ControlParameter Name="lastname" ControlID="txtSearch" PropertyName="Text" />
    </FilterParameters>
</asp:SqlDataSource>

The % characters in the FilterExpression are a wildcard to match any text before or after the search expression.

Step 3: Add a Search Box

This bit is easy, just create a textbox that you call txtSearch - this is what is referenced in our FilterExpression in Step 2.

<asp:TextBox ID="txtSearch" runat="server" />
<asp:ImageButton ID="btnSearch" ImageUrl="images/searchbutton.png" runat="server" />
<asp:ImageButton ID="btnClear" ImageUrl="images/clearbutton.png" runat="server" />

Step 4: Add code

The comments in the code below should be self-explanatory. Basically every time we're displaying the First and Last name data in our Gridview, we check to see if there is any search text, and if there is, use a regular expression to enclose the search string in a CSS span which turns the text yellow.

Imports System.Text.RegularExpressions

Partial Class GridviewwithHighlightedSearch
    Inherits System.Web.UI.Page

    ' Create a String to store our search results
    Dim SearchString As String = ""

    Function HighlightText(ByVal InputTxt As String) As String
        ' This function is called whenever text is displayed in the FirstName and LastName 
        ' fields from our database. If we're not searching then just return the original 
        ' input, this speeds things up a bit
        If SearchString = "" Then
            Return InputTxt
        Else
            ' Otherwise create a new regular expression and evaluate the FirstName and 
            ' LastName fields against our search string.
            Dim ResultStr As Regex
            ResultStr = New Regex(SearchString.Replace(" ", "|"), RegexOptions.IgnoreCase)
            Return ResultStr.Replace(InputTxt, New MatchEvaluator(AddressOf ReplaceWords))
        End If
    End Function

    Public Function ReplaceWords(ByVal m As Match) As String
        ' This match evaluator returns the found string and adds it a CSS class I defined 
        ' as 'highlight'
        Return "<span class=highlight>" + m.ToString + "</span>"
    End Function

    Protected Sub btnClear_Click(ByVal sender As Object, ByVal e As  _
            System.Web.UI.ImageClickEventArgs) Handles btnClear.Click
        ' Simple clean up text to return the Gridview to it's default state
        txtSearch.Text = ""
        SearchString = ""
        Gridview1.DataBind()
    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As  _
            System.Web.UI.ImageClickEventArgs) Handles btnSearch.Click
        ' Set the value of the SearchString so it gets 
        SearchString = txtSearch.Text
    End Sub
End Class

Step 5: Add the CSS class for highlighting

The final bit is to add the CSS claass referenced in the span called highlight. In my case I'm simply turning the background of my text yellow:

<style type="text/css">
   .highlight {text-decoration: none;color:black;background:yellow;}
</style>

That's it! I hope it helps you out, have a look at the Demo and download the Source Code.

Bartek Marnane

Error when exporting a Gridview to Excel when using Master Pages

bartek.nospam@nospam.evonet.com.au (bartek) — Thu, 19 Jun 2008 16:23:10 +1100

If you have a Gridview that exports rows to Excel (see my article Gridview that exports selected rows to Excel for an example) and you are using Master Pages, you are likely to run into the following error:

"Control 'ctl00_MainContent_Gridview1' of type 'GridView' must be placed inside a form tag with runat=server", even though you have the supposedly missing form tag.

The reason for this is described in this MSDN page and occurs because ASP.NET does not know to render this control at run time.

Simply adding the following empty function will override the default behaviour:

Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        ' Nothing needed here...
End Sub

Note that this will only occur if you are using Master Pages.

Bartek Marnane

Gridview that exports selected rows to Excel

bartek.nospam@nospam.evonet.com.au (bartek) — Tue, 17 Jun 2008 11:16:06 +1100

I recently had a project where I needed to provide a Gridview of items that the customer could individually select and export to Excel. The solution worked really well, so here it is for all to enjoy...

This seems like a messy workaround, but I ended up using two Gridviews - one to display my data and show my checkboxes, and the other which was invisible amd would eventually end up as my Excel spreadsheet. Why two? Well...

The export to Excel uses the Response.Write class which doesn't work under AJAX (to my knowledge anyway). Having the first Gridview in an UpdatePanel provides a better user experience when sorting and paging. The second Gridview is outside the UpdatePanel and invisible.
The 2 Gridviews are formatted differently. As an example, the Gridview that is exported to Excel doesn't need sorting or paging options, nor the checkboxes that are used to select rows. Also, the colours used in your website may not be ideal for an Excel spreadsheet.

Demo | Code

Step 1: Create a Gridview with selectable rows

The first Gridview is the one that you will be displaying on your webpage (I use the same table and CSS structure as in my article on Creating a great looking Gridview with filtering):

I called this Gridview gvData.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
AllowSorting="true" DataSourceID="dsGridview" EmptyDataText="No records to display."
PageSize="10" Width="540px" CssClass="Gridview" DataKeyNames="ID">
<RowStyle CssClass="data-row" />
<AlternatingRowStyle CssClass="alt-data-row" />
<HeaderStyle CssClass="header-row" />
<Columns>
    <asp:BoundField DataField="id" HeaderText="ID" SortExpression="id" ItemStyle-Width="40px"
        ItemStyle-HorizontalAlign="Center" />
    <asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName"
        ItemStyle-Width="110px" />
    <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName"
        ItemStyle-Width="110px" />
    <asp:BoundField DataField="Dept" HeaderText="Department" SortExpression="Department"
        ItemStyle-Width="120px" />
    <asp:BoundField DataField="Location" HeaderText="Location" SortExpression="Location"
        ItemStyle-Width="120px" />
    <asp:TemplateField HeaderText="">
        <ItemStyle Width="40px" HorizontalAlign="Center" />
        <ItemTemplate>
            <asp:CheckBox ID="chkSelect" runat="server" />
        </ItemTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>

The main point of interest is the checkbox that is included as a field:

<asp:TemplateField HeaderText="">
    <ItemStyle Width="40px" HorizontalAlign="Center" />
    <ItemTemplate>
        <asp:CheckBox ID="chkSelect" runat="server" />
    </ItemTemplate>
</asp:TemplateField>

You also need to set the Datakeynames property to the ID of your primary database key.

Step 2: Create an invisible Gridview that will be exported to Excel

This Gridview is the one that will be exported to Excel. The main things to note are the changes to the Gridview properties:

Disable paging
Disable sorting
Set visible = 'True'

Once again, set datakeynames to the ID of your primary database key.

You also don't need the checkbox template field.

I called this gvExporttoExcel.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="False"
DataSourceID="dsGridview" EmptyDataText="There are no records to display." Width="690px"
DataKeyNames="ID" Visible="false">
<Columns>
    <asp:BoundField DataField="id" HeaderText="Sort" SortExpression="id" ItemStyle-Width="40px"
        ItemStyle-HorizontalAlign="Center" />
    <asp:BoundField DataField="FirstName" HeaderText="Sort" SortExpression="FirstName"
        ItemStyle-Width="120px" />
    <asp:BoundField DataField="LastName" HeaderText="Sort" SortExpression="LastName"
        ItemStyle-Width="120px" />
    <asp:BoundField DataField="Department" HeaderText="Sort" SortExpression="Department"
        ItemStyle-Width="130px" />
    <asp:BoundField DataField="Location" HeaderText="Sort" SortExpression="Location"
        ItemStyle-Width="130px" />
</Columns>
<PagerSettings Mode="NumericFirstLast" PageButtonCount="10" />
</asp:GridView>

Step 3: Set EnableEventValidation="false"

In your page declarations, set EnableEventValidation='false', otherwise you will get the error: RegisterForEventValidation can only be called during Render();

<%@ Page Language="VB" CodeFile="GridviewwithSelectedExport.aspx.vb"
    Inherits="GridviewwithSelectedExport" EnableEventValidation="false" %>

You will also need to create an empty sub that overrides the Gridview's Render process:

Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
     ' Nothing needed here...
End Sub

Step 4: Create a list variable that will store ID's of selected rows

In your code behind file, create a Private Property that will store the ID's of your selected rows:

Private ReadOnly Property IDs() As List(Of Integer)
    ' Create a list of ID's that are selected.  ID's is the primary
    ' Key for this table
    Get
        If Me.ViewState("IDs") Is Nothing Then
            Me.ViewState("IDs") = New List(Of Integer)()
        End If
        Return CType(Me.ViewState("IDs"), List(Of Integer))
    End Get
End Property

Step 5: Create a Sub that will store the selected row ID's into your list variable

Now we create a Sub that I called AddRowstoIDList which goes through all the currently visible rows and adds the ones that have a checkbox selected to the private list variable created in step 4.

Protected Sub AddRowstoIDList()
    ' Loop through all the currently displayed rows in the Gridview
    For Each gvr As GridViewRow In gvData.Rows
        ' Find the checkbox in each row
        Dim chkSelect As CheckBox = CType(gvr.FindControl("chkSelect"), CheckBox)
        ' If the checkbox is ticked then add the corresponding ID to our private list
        If (Not (chkSelect) Is Nothing) Then
            ' Get the ID from the datakeynames property
            Dim ID As Integer = Convert.ToInt32(gvData.DataKeys(gvr.RowIndex)("ID"))
            If (chkSelect.Checked AndAlso Not Me.IDs.Contains(ID)) Then
                ' Add the ID to our list
                Me.IDs.Add(ID)
            ElseIf (Not chkSelect.Checked AndAlso Me.IDs.Contains(ID)) Then
                ' Not checked - remove the ID from our list
                Me.IDs.Remove(ID)
            End If
        End If
    Next
End Sub

This sub is then called whenever the Gridview is paged or sorted - we want to get the current status of the rows and update our list before the Gridview refreshes:

Protected Sub Gridview1_PageIndexChanging(ByVal sender As Object, ByVal e & _
        As GridViewPageEventArgs) Handles gvData.PageIndexChanging
    AddRowstoIDlist()
End Sub

Protected Sub Gridview1_Sorting(ByVal sender As Object, ByVal e As & _
        System.Web.UI.WebControls.GridViewSortEventArgs) Handles gcData.Sorting
    AddRowstoIDlist()
End Sub

Step 6: Make sure the checkboxes are displayed correctly when the Gridview is refreshed

The next step is to set the status of each checkbox correctly when the Gridview is refreshed, by a sort of paging operation for example. We do this by checking each row, finding the checkbox control, and checking it if the row ID exists in our list.

Protected Sub gvData_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) & _
        Handles gvData.RowDataBound
    ' Get each Gridview Row on DataBound
    Dim gvr As GridViewRow = e.Row
    If (gvr.RowType = DataControlRowType.DataRow) Then
        ' Find the checkbox in the current row
        Dim chkSelect As CheckBox = CType(gvr.FindControl("chkSelect"), CheckBox)
        ' Make sure we're referencing the correct control
        If (Not (chkSelect) Is Nothing) Then
            ' If the ID exists in our list then check the checkbox
            Dim ID As Integer = Convert.ToInt32(gvData.DataKeys(gvr.RowIndex)("ID"))
            chkSelect.Checked = Me.IDs.Contains(ID)
        End If
    End If
End Sub

Step 7: Create a download button that exports the selected rows to Excel

Finally, create a download button anywhere on your page:

<asp:LinkButton ID="btnDownload" runat="server" Text="Download selected rows to Excel" />

The next bit of code manipulates our hidden Gridview (gvExporttoExcel) by making visible any rows that are marked for export, then writing those to an Excel file.

Add the following code behind to export the data to Excel:

Protected Sub btnDownload_Click(ByVal sender As Object, ByVal e As System.EventArgs) & _
        Handles btnDownload.Click
    ' First, check the contents of the current Gridview page to make sure that
    ' all the checked items are in our list.  We need this as the user may have checked or 
    ' unchecked records since the last paging or sorting operation.
    AddRowstoIDList()
    ' Loop through our second Gridview, and make visible any row where the ID of the record
    ' is in our list of checked records
    For Each gvr As GridViewRow In gvExporttoExcel.Rows
        Dim ID As Integer = Convert.ToInt32(gvExporttoExcel.DataKeys(gvr.RowIndex)("ID"))
        If Not Me.IDs.Contains(ID) Then
            gvr.Visible = False
        Else
            gvr.Visible = True
        End If
    Next
    If Me.IDs.Count = 0 Then
        ' Display an error here if you like
    Else
        gvExporttoExcel.Visible = True
        ' Write the gridview to Excel
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "application/vnd.xls"
        Dim stringWrite As New System.IO.StringWriter()
        Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
        gvExporttoExcel.RenderControl(htmlWrite)
        Response.Write(stringWrite.ToString())
        Response.End()
        gvExporttoExcel.Visible = False
    End If
End Sub

That should be it! Check out the Demo or download the source code.

Bartek Marnane

'dropdownlist1' has a SelectedValue which is invalid because it does not exist in the list of items

bartek.nospam@nospam.evonet.com.au (bartek) — Sat, 07 Jun 2008 14:21:06 +1100

When you bind a dropdownlist to a table of data, and you try to edit an entry that contains data that has been deleted, you invariably will run into the error: 'dropdownlist1' has a SelectedValue which is invalid because it does not exist in the list of items.

Consider the following example:

You have a series of Events that are displayed in a calendar, where each event can have a Category. You provide your users the ability to edit their events, and administrators the ability to edit categories.

When creating a new event you would have a dropdownlist that references the categories table like so:

<asp:DropDownList ID="ddlCategory" runat="server" Style="width: 150px;" CssClass="TextField" 
    SelectedValue='<%# Bind("Category") %>' DataSourceID="dsPopulateEventCategories" 
    DataTextField="Name" DataValueField="Name" AppendDataBoundItems="true">
    <asp:ListItem Text="Please Select" Value=""></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateEventCategories" runat="server" 
ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
SelectCommand="SELECT Name from [T_Categories]"></asp:SqlDataSource>

The same code would be used in your EditItemTemplate for your Formview in case the user wants to change the category. But if an administrator deletes or renames the category in the meantime then you get the following error:

'ddlCategory' has a SelectedValue which is invalid because it does not exist in the list of items

This is basically because the dropdownlist populates the categories from the category table - and the category that you have bound to your dropdownlist doesn't exist in that table.

To solve the problem neatly, first of all remove the SelectedValue property for your dropdownlist to remove your binding. Your dropdownlist then beomes:

<asp:DropDownList ID="ddlCategory" runat="server" Style="width: 150px;" CssClass="TextField"
    DataSourceID="dsPopulateEventCategories" DataTextField="Name" DataValueField="Name"
    AppendDataBoundItems="true">
    <asp:ListItem Text="Please Select" Value=""></asp:ListItem>
</asp:DropDownList>

Secondly, in the PreRender subroutine for your Formview, add some code which will search through the data items bound to your dropdownlist, and if your category isn't found then display the "Please Select" option:

Protected Sub Formview1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) & _ 
    Handles Formview1.PreRender
    If Formview1.CurrentMode = FormViewMode.Edit Then
        Dim rowView As DataRowView = CType(Formview1.DataItem, DataRowView)
        If Not IsNothing(rowView) AndAlso Not IsNothing(CType(Formview1. & _
            FindControl("ddlCategory"), DropDownList).Items. & _
            FindByValue(rowView("Category").ToString())) Then
                CType(Formview1.FindControl("ddlCategory"), DropDownList).SelectedValue & _ 
                    = rowView("Category").ToString()
        End If
    End If
End Sub

The final step is to save the selected category back into the database when the user clicks the Save button to complete editing:

Protected Sub Formview1_ItemUpdating(ByVal sender As Object, ByVal e As & _
    System.Web.UI.WebControls.FormViewUpdateEventArgs) & _
    Handles Formview1.ItemUpdating
        e.NewValues("Category") = CType(formview1.FindControl("ddlCategory"), & _
            DropDownList).SelectedValue
End Sub

Hope this helps!

Bartek Marnane

Creating a great looking Gridview with filtering

bartek.nospam@nospam.evonet.com.au (bartek) — Sat, 31 May 2008 12:21:29 +1100

Matt Berseth (http://www.mattberseth.com/) has some of the best ASP.NET 2.0 articles around, and his examples are always well documented.

Matt recently wrote some articles about how to a design good looking Gridview's (Building a vs2008 Styled Grid and Gridview Themes based on Google's Personalized Homepage) which was exactly what I was after.

I have been looking for quite some time for a good looking Gridview and Matt's were great, although they didn't include any filtering. Nearly all of my Gridviews have filtering enabled and so here is a quick tutorial to show you how to design a stylish Gridview based on Matt's design with filtering enabled for some of the columns.

Demo | Code

Step 1: Create the Gridview and Datasource

Create a simple Gridview and Datasouce. In this example I am using a SQL Datasource, but I recommend using a ObjectDataSource for production environments. Set the ConnectionString to the value in your web.config file and the ItemStyle-Width for each of your fields depending on the type of data and how much space you have.

<asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
AllowSorting="true" DataSourceID="dsGridview" Width="650px" PageSize="20"
CssClass="Gridview">
<Columns>
    <asp:BoundField DataField="id" HeaderText="id" SortExpression="id" 
        ItemStyle-Width="50px" ItemStyle-HorizontalAlign="Center" />
    <asp:BoundField DataField="FirstName" HeaderText="Sort" SortExpression="FirstName"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="LastName" HeaderText="Sort" SortExpression="LastName"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="Department" HeaderText="Sort" SortExpression="Department"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="Location" HeaderText="Sort" SortExpression="Location"
        ItemStyle-Width="150px" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>" 
SelectCommand="SELECT * FROM [T_Employees]" />

Step 2: Create the table used for the Gridview headers

Now we create a simple table to hold the headings and filter drop down boxes.

<table style="width: 650px" border="0" cellpadding="0" cellspacing="1" 
class="GridviewTable">
<tr>
    <td style="width: 50px;">
        ID
    </td>
    <td style="width: 150px;">
        First Name
    </td>
    <td style="width: 150px;">
        Last Name
    </td>
    <td style="width: 150px;">
        Department
    </td>
    <td style="width: 150px;">
        Location
    </td>
</tr>
<tr>
    <td style="width: 50px;">
    </td>
    <td style="width: 150px;">
    </td>
    <td style="width: 150px;">
    </td>
    <td style="width: 150px;">
        <asp:DropDownList ID="ddldepartment" DataSourceID="dsPopulateDepartment" 
            AutoPostBack="true" DataValueField="department" runat="server" Width="130px" 
            Font-Size="11px" AppendDataBoundItems="true">
            <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>
    </td>
    <td style="width: 150px;">
        <asp:DropDownList ID="ddlLocation" DataSourceID="dsPopulateLocation" 
            AutoPostBack="true" DataValueField="location" runat="server" Width="130px" 
            Font-Size="11px" AppendDataBoundItems="true">
            <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>
    </td>
</tr>
<tr>
    <td colspan="5">
        <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" 
            AllowPaging="True" AllowSorting="true" DataSourceID="dsGridview" 
            Width="650px" PageSize="10" CssClass="Gridview">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="Sort" SortExpression="id" 
                    ItemStyle-Width="50px" ItemStyle-HorizontalAlign="Center" />
                <asp:BoundField DataField="FirstName" HeaderText="Sort" 
                    SortExpression="FirstName" ItemStyle-Width="150px" />
                <asp:BoundField DataField="LastName" HeaderText="Sort" 
                    SortExpression="LastName" ItemStyle-Width="150px" />
                <asp:BoundField DataField="Department" HeaderText="Sort" 
                    SortExpression="Department" ItemStyle-Width="150px" />
                <asp:BoundField DataField="Location" HeaderText="Sort" 
                    SortExpression="Location" ItemStyle-Width="150px" />
            </Columns>
        </asp:GridView>
    </td>
</tr>
</table>

For the last cell, set the td colspan value to the number of fields in your Gridview. Move your Gridview into that last cell.

Step 3: Create the style sheet

The stylesheet that I use has the following items:

.GridviewDiv {font-size: 62.5%; font-family: 'Lucida Grande', 
    'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
Table.Gridview{border:solid 1px #df5015;}
.GridviewTable{border:none}
.GridviewTable td{margin-top:0;padding: 0; vertical-align:middle }
.GridviewTable tr{color: White; background-color: #df5015; height: 30px; text-align:center}
.Gridview th{color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;
    padding:0.5em 0.5em 0.5em 0.5em;text-align:center}  
.Gridview td{border-bottom-color:#f0f2da;border-right-color:#f0f2da;
    padding:0.5em 0.5em 0.5em 0.5em;}
.Gridview tr{color: Black; background-color: White; text-align:left}
:link,:visited { color: #DF4F13; text-decoration:none }

You should be able to just copy this into your css file without it affecting your exiting style sheets, although be careful if you have already set :link and :visited in your site.

Step 4: Add the filtering drop down boxes and data sources

In the table created in Step 2, add a dropdownlist to each of the cells in the second row that contain the field you want to filter. Make sure eac dropdownlist is smaller than the cell it is going into, otherwise your table borders will not be aligned. Set up a datasource which gets the each possible value of that field within your table. I do this by running a DISTINCT for all values in the table I am filtering:

<asp:DropDownList ID="ddldepartment" DataSourceID="dsPopulateDepartment" 
AutoPostBack="true" DataValueField="department" runat="server" Width="130px" Font-Size="11px" 
AppendDataBoundItems="true">
    <asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateDepartment" runat="server" 
ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>" SelectCommand="SELECT 
DISTINCT Department from [T_Employees]"></asp:SqlDataSource>

Create as many dropdownlists as fields that you would like to filter by.

A few things to note:

Set the AppendDataBoundItems=True property for your dropdownlist as it will be filled at runtime.
Set the AutoPostBack=True property so that the Gridview is refreshed when the selection changes.
Make sure your ListItem for 'All' has '%' as the value. Your filter expression will be SELECT * FROM [TABLE NAME] Where [FieldName] like '{0}%' where {0} is the value in your dropdownlist. If your dropdownlist is set to all then the query string will be SELECT * FROM [TABLE NAME] Where [FieldName] like '%%' which, in SQL returns all values.

Step 5: Add filtering to your Gridview's Datasource

Add a FilterExpress so your Gridview's Datasource such as

[Field1] like '{0}%' and [Field2] like '{1}%' and [Field3] like '{2}%' and [Field4] like '{3}%' and ... etc

Your fields then need to be added to the FilterParameters section in the same order as your filter expression. The FilterParameters section references the SelectedValue of your dropdownlists.

<asp:SqlDataSource ID="dsGridview" runat="server" 
    ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
    SelectCommand="SELECT * FROM [T_Employees]" FilterExpression="Department like '{0}%'
    and Location like '{1}%'">
    <FilterParameters>
        <asp:ControlParameter Name="Department" ControlID="ddldepartment" 
            PropertyName="SelectedValue" />
        <asp:ControlParameter Name="Location" ControlID="ddllocation" 
            PropertyName="SelectedValue" />
    </FilterParameters>
</asp:SqlDataSource>

That's it! Check out the Demo or download the source code and have a play.

Bartek Marnane

Using CultureInvariantValues = true

bartek.nospam@nospam.evonet.com.au (bartek) — Sat, 24 May 2008 11:32:01 +1100

I ran into a frustrating error recently, where my compare validator was refusing to co-operate when testing 2 dates.

Here is my code:

Start date: 
<asp:TextBox runat="server" ID="txtStartDate" Width="80px" CssClass="TextField" 
Text='<%# Bind("startdate","{0:d}") %>' />
End date: 
<asp:TextBox runat="server" ID="txtenddate" Width="80px" CssClass="TextField" 
Text='<%# Bind("enddate","{0:d}") %>' />
<asp:CompareValidator ID="CompareValidator1" ControlToCompare="txtstartdate" 
ControlToValidate="txtenddate"
Operator="GreaterThanEqual" ErrorMessage="The start date has to be before the end date"
Display="None" runat="server" />

The problem was that the Compare Validator would not work when the start date was 05/06/2008 and the end date was 03/07/2008 (these are all in DD/MM/YYYY). The reason was that the compare validator did not realise the value was a date and saw simply that 05 was greater than 03 in terms of ASCII values.

The fix should have been simply to add type="date" to the CompareValidator, however on it's own that doesn't work if you're using UK English dates - you also need to add CultureInvariantValues="true" (unless your Globalisation is set properly in your web.config - which mine wasn't...)

The full code then becomes:

Start date: 
<asp:TextBox runat="server" ID="txtStartDate" Width="80px" CssClass="TextField" 
Text='<%# Bind("startdate","{0:d}") %>' />
End date: 
<asp:TextBox runat="server" ID="txtenddate" Width="80px" CssClass="TextField" 
Text='<%# Bind("enddate","{0:d}") %>' />
<asp:CompareValidator ID="CompareValidator2" ControlToCompare="txtstartdate" 
ControlToValidate="txtenddate" CultureInvariantValues="true" Type="Date"
Operator="GreaterThanEqual" ErrorMessage="The start date has to be before the end date"
Display="None" runat="server" />

MSDN has more information here: http://msdn.microsoft.com/en-us/library/a7zyyk0c(VS.80).aspx

Bartek Marnane

Editing date and time in a Formview

bartek.nospam@nospam.evonet.com.au (bartek) — Sat, 17 May 2008 11:17:11 +1100

This is a simple example for how to provide an editing interface for date and time fields.

Say that you are storing Booking details and your BookingDate field contains both the date and time, eg '21/05/2008 10:30:00 AM'

If you wanted to display the date and time strings seperately you can do this in a formview quite easily:

Date: 
<asp:Label ID="lblDate" runat="server" Text='<%# Eval("bookingdate", "{0:d}") %>' />
Time:
<asp:Label ID="lblTime" runat="server" Text='<%# Eval("bookingdate", "{0:%h:mm ttt}") %>' />

But to edit the date and time you would use a normal date field (with your choice of date picker) and then a dropdownlist for the time:

Date: 
<asp:TextBox ID="txtDate" runat="server" Text='<%# Eval("bookingdate", "{0:d}") %>' />
Time:
<asp:DropDownList ID="ddlHours" runat="server" Width="40px" Font-Size="11px">
<asp:ListItem>07</asp:ListItem>
    <asp:ListItem>08</asp:ListItem>
    <asp:ListItem>09</asp:ListItem>
    <asp:ListItem>10</asp:ListItem>
    <asp:ListItem>11</asp:ListItem>
    <asp:ListItem>12</asp:ListItem>
    <asp:ListItem>13</asp:ListItem>
    <asp:ListItem>14</asp:ListItem>
    <asp:ListItem>15</asp:ListItem>
    <asp:ListItem>16</asp:ListItem>
    <asp:ListItem>17</asp:ListItem>
    <asp:ListItem>18</asp:ListItem>
    <asp:ListItem>19</asp:ListItem>
    <asp:ListItem>20</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlMins" runat="server" Width="40px" Font-Size="11px">
    <asp:ListItem>00</asp:ListItem>
    <asp:ListItem>05</asp:ListItem>
    <asp:ListItem>10</asp:ListItem>
    <asp:ListItem>15</asp:ListItem>
    <asp:ListItem>20</asp:ListItem>
    <asp:ListItem>25</asp:ListItem>
    <asp:ListItem>30</asp:ListItem>
    <asp:ListItem>35</asp:ListItem>
    <asp:ListItem>40</asp:ListItem>
    <asp:ListItem>45</asp:ListItem>
    <asp:ListItem>50</asp:ListItem>
    <asp:ListItem>55</asp:ListItem>
</asp:DropDownList>

Note the use of 'Eval' in the BookingDate Textbox - normally you would use 'Bind' for 2 way databinding, but we don't want to store the date - we want to store a combination of the date and time in the same field, which is done in the Formview's ItemUpdating subroutine:

Protected Sub Formview1_ItemUpdating(ByVal sender As Object, ByVal e & _
As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles Formview1.ItemUpdating
    e.NewValues("bookingdate") = CType(Formview1.FindControl("txtBookingDate"), TextBox).Text & _
        " " & CType(Formview1.FindControl("ddlHours"), DropDownList).SelectedValue & ":" & _
        CType(Formview1.FindControl("ddlMins"), DropDownList).SelectedValue
End Sub

This simply combines the textbox and dropdownlists into a formatted date which is then saved in your database as BookingDate.

Bartek Marnane