Archive for category Web Development

jQuery Validation – Show & Focus on first error only!

Have you ever wanted to show one error message at a time, or create a different type of visual queue for a specific invalid element on a form? Using the validate options, you can easily create any effect you want.

The example below, is designed to show the first error message in a form and to set focus on that first element. The message is displayed in a basic JavaScript alert box, not very elegant but it makes for a simple demo.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Show JS Alert - First Validation Error</title>
    <script src="Scripts/jquery-1.4.2.js" type="text/javascript"></script>
    <script src="Scripts/jquery.validate.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            $("#elForm").validate({
                onfocusout: false,
                invalidHandler: function(form, validator) {
                    var errors = validator.numberOfInvalids();
                    if (errors) {
                        alert(validator.errorList[0].message);
                        validator.errorList[0].element.focus();
                    }
                },
                rules: {
                    TextBox1: { required: true, minlength: 2 },
                    TextBox2: { required: true, minlength: 2 },
                    TextBox3: { required: true, minlength: 2 }
                },
                messages: {
                    TextBox1: { required: "TextBox1 Required", minlength: "TextBox1 MinLength" },
                    TextBox2: { required: "TextBox2 Required", minlength: "TextBox2 MinLength" },
                    TextBox3: { required: "TextBox3 Required", minlength: "TextBox3 MinLength" }
                },
                errorPlacement: function(error, element) {
                    // Override error placement to not show error messages beside elements //
                }
            });
        });
    </script>

    <style type="text/css">
        label { width: 90px; display: block; float: left; }
        ul { list-style: none; }
        ul li { line-height: 1.8; }
    </style>
</head>
<body>
    <form id="elForm" action="#">
    <div>
        <h1>jQuery Validation - Show First Error Only</h1>
        <ul>
            <li><label>Text Box 1</label><input type="text" name="TextBox1" id="TextBox1" value="" /></li>
            <li><label>Text Box 2</label><input type="text" name="TextBox2" id="TextBox2" value="" /></li>
            <li><label>Text Box 3</label><input type="text" name="TextBox3" id="TextBox3" value="" /></li>
        </ul>
        <input type="submit" id="submit" value="Validate" />
    </div>
    </form>
</body>
</html>

Here are the important parts to focus on.

1. invalidHandler, this option give you access to the current validator and all the errors messages/items via the “validator.errorlist”.

                invalidHandler: function(form, validator) {
                    var errors = validator.numberOfInvalids();
                    if (errors) {
                        alert(validator.errorList[0].message);  //Only show first invalid rule message!!!
                        validator.errorList[0].element.focus(); //Set Focus
                    }
                }

2. errorPlacement, this function controls how error messages are displayed. Since we don’t want any error messages displayed by default, we can override the default errorPlacement with a empty method call.

                errorPlacement: function(error, element) {
                    // Override error placement to not show error messages beside elements //
                }

The end result is a alert box showing the first broken rule for the first invalid element on the page. In the example each input has 2 rules (required and minLength), if you enter a single character into the first text box the message will change from “TextBox1 Required” to “TextBox1 MinLength”.

Download JSON to CSV using JavaScript

Here is a simple way to convert your JSON to CSV, if your using FF or Chrome that supports data URI’s then this function convert your data and tell your browser to download the results as a text file. If you are using Internet Explorer (“IE”), a new window will popup so you can copy & paste or to use File -> Save As to download the results as text. The JSON parsing is done with json.org’s open source JSON parser, which you can download here.

Why can’t IE download the file to CSV like FireFox? The long answer is due to IE’s data URI support that was missing until IE8, and when implemented in IE8+, the security restrictions are set to prevent solutions like this from working! To force a download of your JSON data in CSV via IE, you’ll need to use an alternative technique like Server Side Parsing.

		function DownloadJSON2CSV(objArray)
		{
			var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
			var str = '';
			
			for (var i = 0; i < array.length; i++) {
				var line = '';
				for (var index in array[i]) {
					if(line != '') line += ','
				
					line += array[i][index];
				}
 
				str += line + '\r\n';
			}

			if (navigator.appName != 'Microsoft Internet Explorer')
			{
				window.open('data:text/csv;charset=utf-8,' + escape(str));
			}
			else
			{
				var popup = window.open('','csv','');
				popup.document.body.innerHTML = '<pre>' + str + '</pre>';
			}			
		}

WordPress 3.0 – A CMS Solution?

I started working on a project site that is going to use WordPress as the CMS engine. I wasn’t sure if this was going to work, but after a little time I found this was pretty easy to do. You start by deleting all the default content and disabling the default plug-ins and management screen options. Once everything is removed/disabled, you start by creating pages (e.g. Home, About, News, etc…) that will display all the content you need on the website. One you have your main page setup (e.g. Home), you need to change the default home page from a dynamic page of blog posts to a static page. You change the default home page under Settings –> Reading –> Front Page Display –> A Static Page –> Choose an option from the Front Page drop down list. That’s it, you now have WordPress running as a basic CMS soltuion.

I wish I could say that’s it, but most likely you’ll need to download and install some additional plug-ins. I found many plug-ins still do not support WordPress 3.0, so you’ll need to be careful! If you happen to install a bad plug-in, there is an easy fix to get you site back online… Delete the plug-in folder (via FTP or Hosting Control Panel) and WordPress will automatically disable the plug-in.

Here is the list of plug-ins I added for my project:

  1. Contact Form 7
    This is a great plug-in, it provides a easy way to create forms that get emailed to you. You create the forms using HTML online and a shortcode style syntax for the input fields. The shortcode inputs are then accessible in your email, that you also create using HTML.
  2. WP Google Maps
    This is a very basic Google Maps plug-in, the is about 10 options and the only think extra you need is a GoogleMaps API key.
  3. WordPress.com Stats
    I’ve been using this on my blog and love the UI. I wish this was all being done client-side, but for some reason it was created as service. By default it injects a smiley face into your page, so make sure you disable this in your themes style sheet.

The only other thing I added was a SSL plug-in that I created based on 3 other plug-ins. I created my own, so I could force WP to load specific pages as HTTPS and then default back to HTTP if it was not explicitly set to support SSL. I found most of the default plug-ins will leave the user browsing in SSL after they visit a SSL page, I didn’t want this and my plug-ins solves this problem.

<?php
/*
Plugin Name: WPSSL Force SSL
Plugin URI: 
Description: This plug-in is based on WPSSL, forcessl and various other posts/comments I found while searching for a soltuion.  The plug-in is used by adding the meta tag "force_ssl" with any value to any pages where you want SSL ("HTTPS") enabled.  If the page does NOT have this set and your not looking at an admin page (you can enable SSL for admin/login page in wpconfig.php), then display as HTTP.  This prevent links on the SSL page from displaying as SSL for non-secure pages.  This plug-in was tested on WordPress 3.0.
Author: Zachary Hunter (based on Austin Web Development)
Version: 1.0
Author URI: 


*/

function wpssl_forcessl() 
{
	global $post;
	
    $post_id = $post;
   
    if (is_object($post_id)) 
    {
		$post_id = $post_id->ID;
	}
	
    $force_ssl  = get_post_meta($post_id, 'force_ssl');

    if(!empty($force_ssl))
    {
    	if(!stristr($_SERVER['REQUEST_URI'], 'wp-admin')) {
			if($_SERVER["HTTPS"] != "on") {
				$newurl = "https://" . $_SERVER["SERVER_NAME"] . $_SERVER["REQUEST_URI"];
            	header("Location: $newurl");
            	exit();
        	}
     	}
    } else {
		if(!stristr($_SERVER['REQUEST_URI'], 'wp-admin')) {
			if($_SERVER["HTTPS"] == "on") {
				$newurl = "http://" . $_SERVER["SERVER_NAME"] . $_SERVER["REQUEST_URI"];
				header("Location: $newurl");
				exit();
			}
	}	}
}

add_action('wp', 'wpssl_forcessl');
?>

To use the plug-in follow these steps.

1. Copy the code above into a file in your plugin folder (e.g. /wp-content/plugins/wpssl/wpssl.php).
2. Go to the Plugins -> Plugins and enable “WPSSL Force SSL”.
2. Go to the Page you want to force SSL and open the “Custom Fields” section.
3. Click “Add Custom Field”, and use the settings below (name = force_ssl, value = true).
** DONE **

Improved NPOI ExportToExcel Function

I was looking over my generic export DataTable to Excel function the other day and noticed an issue. My default method was throwing everything into a string format, which was preventing users from highlighting number columns for subtotals. To fix the problem they could use “Text to Columns”, but this was becoming a common complaint I wanted to resolve. I came up with an improved “ExportToExcel” function that will insert the correct data type.

        /// <summary>
        ///   Render DataTable to Excel File
        /// </summary>
        /// <param name = "sourceTable">Source DataTable</param>
        /// <param name = "fileName">Destination File Name</param>
        public static void ExportToExcel(DataTable sourceTable, string fileName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            Sheet sheet = workbook.CreateSheet("Sheet1");
            Row headerRow = sheet.CreateRow(0);

            // Create Header Style
            CellStyle headerCellStyle = workbook.CreateCellStyle();
            headerCellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
            headerCellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

            // Create Date Style
            CellStyle dateCellStyle = workbook.CreateCellStyle();
            dateCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");

            // Build Header
            foreach (DataColumn column in sourceTable.Columns)
            {
                Cell headerCell = headerRow.CreateCell(column.Ordinal);
                headerCell.SetCellValue(column.ColumnName);
                headerCell.CellStyle = headerCellStyle;
            }

            // Build Details (rows)
            int rowIndex = 1;
            int sheetIndex = 1;
            const int maxRows = 65536;

            foreach (DataRow row in sourceTable.Rows)
            {
                // Start new sheet max rows reached
                if (rowIndex % maxRows == 0)
                {
                    // Auto size columns on current sheet
                    for (int h = 0; h < headerRow.LastCellNum; h++)
                    {
                        sheet.AutoSizeColumn(h);
                    }

                    sheetIndex++;
                    sheet = workbook.CreateSheet("Sheet" + sheetIndex);
                    Row additionalHeaderRow = sheet.CreateRow(0);

                    for (int h = 0; h < headerRow.LastCellNum; h++)
                    {
                        Cell additionalHeaderColumn = additionalHeaderRow.CreateCell(h);
                        additionalHeaderColumn.CellStyle = headerRow.GetCell(h).CellStyle;
                        additionalHeaderColumn.SetCellValue(headerRow.GetCell(h).RichStringCellValue);
                    }

                    rowIndex = 1;
                }

                // Create new row in sheet
                Row dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    Cell dataCell = dataRow.CreateCell(column.Ordinal);

                    switch (column.DataType.FullName)
                    {
                        case "System.String":
                            dataCell.SetCellValue(row[column].ToString());
                            break;
                        case "System.Int":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Double":
                        case "System.Decimal":
                            double val;
                            dataCell.SetCellValue(Double.TryParse(row[column].ToString(), out val) ? val : 0);
                            break;
                        case "System.DateTime":
                            DateTime dt = new DateTime(1900, 01, 01);
                            DateTime.TryParse(row[column].ToString(), out dt);

                            dataCell.SetCellValue(dt);
                            dataCell.CellStyle = dateCellStyle;
                            break;
                        default:
                            dataCell.SetCellValue(row[column].ToString());
                            break;
                    }
                }

                rowIndex++;
            }

            for (int h = 0; h < headerRow.LastCellNum; h++)
            {
                sheet.AutoSizeColumn(h);
            }

            ExportToExcel(workbook, fileName);
        }

The key part of the function above to review is the “switch (column.DataType.FullName)” code block. This grabs the DataTable’s column data type to use in the SetCellValue() call.

Once the workbook is built, we call a overload of the same function that expects a NPOI workbook. This overload will send a XLS file back to the user via their web browser using the HttpResponse stream.

        /// <summary>
        /// Render Excel File to HttpResponse (Browser)
        /// </summary>
        /// <param name="workbook">NPOI Workbook</param>
        /// <param name="fileName">Destination File Name</param>
        public static void ExportToExcel(HSSFWorkbook workbook, string fileName)
        {
            using (MemoryStream memoryStream = new MemoryStream())
            {
                workbook.Write(memoryStream);
                memoryStream.Flush();

                try
                {
                    HttpResponse response = HttpContext.Current.Response;
                    response.ClearContent();
                    response.ClearHeaders();
                    response.Buffer = true;
                    response.ContentType = "application/vnd.ms-excel";
                    response.AddHeader("Content-Length", memoryStream.Length.ToString());
                    response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
                    response.BinaryWrite(memoryStream.GetBuffer());
                    response.Flush();
                    response.End();
                }
                catch
                {
                    // Do nothing, error expected due to Flush();
                }
            }
        }

I’ve used the above function to send files with 3-4 sheets full of data back to the browser… but the file size is usually “VERY LARGE”. To work around this, I have a helper function which will add the excel file to a zip (in memory) before sending back to the client.

In my last test, a 17.5MB document with 2 sheets of data was compressed to 3.5MB with my zip utility. There was still a ~13 second delay to generate the XLS file, but once prompted to download form the browser the file download completed in a few seconds.

jQuery Validation with ASP.NET

Over the past few weeks I’ve been working to migrate away from the Ajax Control Toolkit & various other ASP.NET built-in controls. My goal has been to get every page of our intranet portal down around 100K, with a maximum load time of < 1 second. I’ve made a lot of progress, but after cleaning up all the basic stuff (excess view state, duplicate references, compressed JS/CSS, sprites, etc…), I had to start looking for additional items…

Using Fiddler and FireBug, I started looking for resources that were increasing my download sizes and found that the ASP.NET validation controls were generating ~10K each time they were used in a module. Since my portal consists of injecting multiple user controls into a page, this number would vary page-by-page based on the number of modules that used the library on one page. Since I was using jQuery for all my scripting, I decided to implement jQuery Validation Plugin as a replacement for the built-in ASP.NET validation controls.

The idea was simple enough, but since I’m living in ASP.NET Web Form world (aka: One form per/page) this was a bit more difficult to implement.

Issue #1 – jQuery Validation by FIELDSET

As noted, the default setup of the validation plug in is to validate a FORM which isn’t possible with ASP.NET Web Forms. To get around this, I decided I was going to wrap all my forms in a FIELDSET with the class “validationGroup”. Using this class as my selection, I’m able to have multiple groups of controls wrapped in a FIELDSET validated separately.

To get started, you need to create a custom validation function that will recursively move up the elements/groups to find a FIELDSET with the class “validationGroup”, and then check all the input elements against the rules assigned.

Here is my custom validation function:

function ValidateAndSubmit(evt) {
    var isValid = true;

    // Get Validator & Settings
    var validator = $("#aspnetForm").validate();
    var settings = validator.settings;

    // Find the parent control that contains the elements to be validated
    var $group = $(evt.currentTarget).parents('.validationGroup');

    // Grab all the input elements (minus items listed below)
    $group
        .find(":input")
        .not(":submit, :reset, :image, [disabled]")
        .not(settings.ignore)
        .each(function (i, item) {
            // Don't validate items without rules
            if (!validator.objectLength($(item).rules()))
                return true;

            if (!$(item).valid())
                isValid = false;
        });

    // If any control is the group fails, prevent default actions (aka: Submit)
    if (!isValid)
        evt.preventDefault();
}

Next, I need to trigger the validation for the current FIELDSET I’m working in based on on some action… like the click of a submit button. Since the default setting is to validation on submit, you need to disable this and register your ValidateAndSubmit function call when your event is triggered.

        // Disable default/auto binding of all buttons
        $("#aspnetForm").validate({
            onsubmit: false,
            ignore: ':hidden',
            focusInvalid: true
        });

        // Bind the ASP.NET button with the ID "Search" to the "ValidateAndSubmit" custom validation function.
        $('#<%= Search.ClientID %>').click(ValidateAndSubmit);

That’s it, with those three little little steps (Custom Validate Method, Disable Default/Auto Bind, Manual bind button to custom event), you now have a working validate by FIELDSET (or any other grouping control) solution.

Issue #2 – How to validate a group of ASP.NET Check Boxes

By default, ASP.NET will render a CheckBox or a CheckBoxList with an auto generated unique name attribute. This becomes a problem when you want to validate the group, since you can’t set a common name attribute for all the elements.

Here is an example of the HTML that would be rendered by a CheckBoxList with a layout of “Flow”.

<span id="CheckBoxGroup">
  <input id="cbk1" name="cbk1" type="checkbox">Check Box 1</input>
  <input id="cbk2" name="cbk2" type="checkbox">Check Box 2</input>
  <input id="cbk3" name="cbk3" type="checkbox">Check Box 3</input>
</span>
<span class="jQueryValError"></span>

My solution to the problem required a custom validator and a custom errorPlacement function, for the jQuery validaiton plug-in.

First, I added a custom validator that looked at a group of check boxes that are inside a wrapping “Parent()” element. I then count the number of checked check boxes to make sure the limit required (e.g. Must check at least 3 check boxes) is greater than the minimum we set.

    jQuery.validator.addMethod("minChecked",
     function(value, element, param) {
        var $p = $(element).parent();
        var selected = $p.children('input[type=checkbox]:checked').length;

        if (selected >= param) {
            $p.children().removeClass('error');
            $p.siblings('.error').remove();
            return true; 
            }

        $p.children('input[type=checkbox]').addClass('error');

In order to hookup this custom event to all the controls inside of a wrapping “Parent()” control, we use jQuery’s $().each function to call the validator’s rules() function.

$(".checkBoxGroup").children('input[type=checkbox]').each(function(){ $(this).rules("add", { minChecked: 3}); });

Second, our plug-in will by default put an error message next to the control(s) that triggered the error. I over ride the default behavior, we setup a “errorPlacement” function to put one error beside the wrapping “Parent()” control.

            $("#<%= form1.ClientID %>").validate({
                errorPlacement: function(error, element){
                    if(element.rules().minChecked > 0) {
                        var $p = $(element).parent();
                        if($p.siblings().hasClass("error") == false) {
                            error.insertAfter($p);
                        }
                    }
                    else {
                        error.insertAfter(element);
                    }
                }
            });

Here is what the form looks like when you have a error with your check boxes.

jQuery Validation of ASP.NET CheckBoxes

Here is source code used to create the example above.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Contact Form Demo</title>

    <script src="styles/jQuery.js" type="text/javascript"></script>

    <script src="styles/jQuery.Validate.js" type="text/javascript"></script>

    <script src="styles/jQuery.Validate.AddOns.js" type="text/javascript"></script>

    <script type="text/javascript">
    
    jQuery.validator.addMethod("minChecked",
     function(value, element, param) {
        var $p = $(element).parent();
        var selected = $p.children('input[type=checkbox]:checked').length;

        if (selected >= param) {
            $p.children().removeClass('error');
            $p.siblings('.error').remove();
            return true; 
            }

        $p.children('input[type=checkbox]').addClass('error');

        return false;}, jQuery.validator.format("Please check at least {0} items.")); 

        $(document).ready(function(){
            $("#<%= form1.ClientID %>").validate({
                rules: {
                    <%= FirstName.UniqueID %>: { required: true },
                    <%= LastName.UniqueID %>: { required: true },
                    <%= Email.UniqueID %>: { required: true, email: true },
                    <%= Phone.UniqueID %>: { required: true, phoneUS: true }                   
                },
                errorPlacement: function(error, element){
                    if(element.rules().minChecked > 0) {
                        var $p = $(element).parent();
                        if($p.siblings().hasClass("error") == false) {
                            error.insertAfter($p);
                        }
                    }
                    else {
                        error.insertAfter(element);
                    }
                }
            });
            
            $(".checkBoxGroup").children('input[type=checkbox]').each(function(){ $(this).rules("add", { minChecked: 3}); });

            $(".myGroupRandom").children('input[type=checkbox]').each(function(){ $(this).rules("add", { minChecked: 1}); });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <fieldset>
            <ol>
                <li>
                    <label class="left">
                        First Name
                    </label>
                    <input type="text" id="FirstName" runat="server" /></li>
                <li>
                    <label class="left">
                        Last Name
                    </label>
                    <input type="text" id="LastName" runat="server" /></li>
                <li>
                    <label class="left">
                        Email
                    </label>
                    <input type="text" id="Email" runat="server" /></li>
                <li>
                    <label class="left">
                        Phone
                    </label>
                    <input type="text" id="Phone" runat="server" /></li>
                <li>
                    <label class="left">
                        Contact Method
                    </label>
                    <span class="checkBoxGroup">
                        <input type="checkbox" id="ReqEmail" runat="server" /><label>Email</label>
                        <input type="checkbox" id="ReqMail" runat="server" /><label>Mail</label>
                        <input type="checkbox" id="ReqPhone" runat="server" /><label>Phone</label>
                        <input type="checkbox" id="ReqNoContact" runat="server" /><label>No Contact</label>
                    </span></li>
                <li>
                    <label class="left">
                        New Letter Type
                    </label>
                    <span class="myGroupRandom" >
                        <input type="checkbox" id="Checkbox1" runat="server" /><label>Company News</label>
                        <input type="checkbox" id="Checkbox2" runat="server" /><label>Press Releases</label>
                        <input type="checkbox" id="Checkbox3" runat="server" /><label>Deals</label>
                        <input type="checkbox" id="Checkbox4" runat="server" /><label>Employement</label>
                    </span></li>
                <li>
                    <input type="submit" id="Submit" value="Submit" /></li>
            </ol>
        </fieldset>
    </div>
    </form>
</body>
</html>

Using jQuery to prevent “Delete” mistakes

A few days ago I was reading a web design article that talked about the “Poka-Yoke” principle. It was such a funny phrase that I had to go to Wikipedia to figure out the definition. In a nutshell, the principle refers to the process of “idiot-proofing” human interaction with a process. This term is based on the Japanese words “yokeru” avoid and “poka” mistake, this is rather funny for me since I work for a Japanese company that has clearly never heard of this principle!

This term came to mind today while helping another programmer develop a system to prevent accidental in their UI. After discussing what needed to be done, it was clear that we needed to implement some client side logic to “prevent… guide…” the user into making the correct choices.

The UI was pretty straight forward, there will be a few buttons to do various actions on rows in a table. The users will select one or more rows for the table and then click the button of the action they want to perform. Sounds simple, right… We’ll it is and it’s also really easy to select the wrong row and/or the wrong button. Training is a big help in any process, but one you leave the users alone the system will be the only thing enforcing the rules.

Here is an example of a table where rows will be selected for deletion. I used a “asp:Table” to simulate the markup for a table that will be generated with a “asp:GridView”. In my past 5 years of working with ASP.NET, this was the first time I ever used or seen this control used.

    <div class="mediumTable">
        <asp:Button ID="DeleteButton" runat="server" Text="Delete" />
        <asp:Table ID="Table1" runat="server">
            <asp:TableHeaderRow>
                <asp:TableHeaderCell>Chk</asp:TableHeaderCell>
                <asp:TableHeaderCell>Product</asp:TableHeaderCell>
                <asp:TableHeaderCell>Stock</asp:TableHeaderCell>
            </asp:TableHeaderRow>
            <asp:TableRow>
                <asp:TableCell>
                    <asp:CheckBox ID="CheckBox1" runat="server" /></asp:TableCell>
                <asp:TableCell>Apples</asp:TableCell>
                <asp:TableCell>0</asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>
                    <asp:CheckBox ID="CheckBox2" runat="server" /></asp:TableCell>
                <asp:TableCell>Oranges</asp:TableCell>
                <asp:TableCell>100</asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>
                    <asp:CheckBox ID="CheckBox3" runat="server" /></asp:TableCell>
                <asp:TableCell>Pears</asp:TableCell>
                <asp:TableCell>150</asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>
                    <asp:CheckBox ID="CheckBox4" runat="server" /></asp:TableCell>
                <asp:TableCell>Watermelon</asp:TableCell>
                <asp:TableCell>2</asp:TableCell>
            </asp:TableRow>
        </asp:Table>
    </div>
    </form>

Here is the delete “prevention” system we came up. We had a few other ideas like, but after doing a few demos of each solution we decided on the following code that will be discussed below.

        $(document).ready(function() {
            $('#<%= DeleteButton.ClientID %>').click(function() {
                // Find all selected items 
                var CheckedCheckBoxes = $('#<%= Table1.ClientID %>').find("input[type='checkbox']:checked");
                // If there are no selected items, exit 
                if (CheckedCheckBoxes.length == 0) {
                    alert("You must make a selection before you can delete an item.");
                    return false;
                }
                // If there are selected items, loop 
                CheckedCheckBoxes.each(function() {
                    var isProcessed = $(this).parent().parent().find('td:last').text();
                    // If current item is already processed, set flat to true 
                    if (jQuery.trim(isProcessed) > 0) {
                        deletedProcessed = true;
                    }
                });
                // Check flag, if true stop ALL deletes and notify user 
                if (deletedProcessed) {
                    alert("You can't delete a product with stock!");
                    return false;
                }
                else {
                    // Do a final confirmation the user wants to delete the selection
                    var answer = confirm('Are you sure you want to delete your selection?');
                    return answer
                }
            });
        }); 

Since selection box was used for a few different functions (process orders, hold orders and edit orders) we needed a way to add a little extra checking to only the delete button. Our goals with the logic above was the following.

  1. Disables “Delete” if nothing has been selected.
  2. Disables “Delete” if the user selected a item should not be deleted (e.g. Has Inventory).
  3. Prompts the user to confirm the really want to delete the item.

In addition to these ideas, we also had the following ideas…

  1. Alert row # of items that don’t meet the business rule
  2. Highlight the row in “red” that don’t meet the business rule
  3. Physically “Disable” the delete button if they select an item that don’t meet the business rules

Mistakes are human nature and by double and triple checking what they are doing we can save the users and ourselves from many emails/phone calls/visits about how the system “deleted the data all by itself”.

Mistake proofing is such an easy idea to grasp and agree with, that it’s surprising that we don’t see more of it in the systems we using everyday. I’m sure I’ve developed a few bad ones, hopefully the audience using it was small and it didn’t mitigate itself into other things. Working for a Japanese company who has developed some of the worst internal tools I’ve ever seen (0 UI design effort), I’m sure to bring up the new Japanese “Poka-Yoke” principle that next time I talk to one of the developers in Japan.

Use NPOI to populate an Excel template

So I’ve been using NPOI all week and decide to do a quick “demo” for my team today. My demo was to show how to use NPOI to populate (update) an Excel template that includes various charts.  Even though NPOI does not support creating charts from scratch, it does support updating files that already include (hence template) charts. I started by going to the Microsoft website where they have a bunch of free “pretty” templates on, randomly choosing one with a bunch of formulas and charts.  It took me about an hour to build the complete demo using very simple and easy to read code. Most of the updates or just putting values in cells, but the actual process of opening/reading/inserting/saving a new or existing file in NPOI is very easy for a novice programmers.

Development Summary ( Step-by-Step )

  1. Get a template, I grabbed mine from here ( office.microsoft.com ).
    ** I only used the first sheet and deleted all the sample data
  2. Create a new ASP.NET 3.5 Web Application projecct
  3. Download NPOI binaries and include in your project
  4. Build a UI that will be used to populate your template.
    ** This could also be populated by a data sources (db, XML, etc..)
    ** NOTE:  I used Excel to create the form using Excel formulas
  5. Add some c# code “magic” to load data into the template using NPOI

Sounds simple because it is… Here is the code to add the form contents into the Excel template.

            // Open Template
            FileStream fs = new FileStream(Server.MapPath(@"\template\Template_EventBudget.xls"), FileMode.Open, FileAccess.Read);

            // Load the template into a NPOI workbook
            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

            // Load the sheet you are going to use as a template into NPOI
            HSSFSheet sheet = templateWorkbook.GetSheet("Event Budget");

            // Insert data into template
            sheet.GetRow(1).GetCell(1).SetCellValue(EventName.Value);  // Inserting a string value into Excel
            sheet.GetRow(1).GetCell(5).SetCellValue(DateTime.Parse(EventDate.Value));  // Inserting a date value into Excel

            sheet.GetRow(5).GetCell(2).SetCellValue(Double.Parse(Roomandhallfees.Value));  // Inserting a number value into Excel
            sheet.GetRow(6).GetCell(2).SetCellValue(Double.Parse(Sitestaff.Value));
            sheet.GetRow(7).GetCell(2).SetCellValue(Double.Parse(Equipment.Value));
            sheet.GetRow(8).GetCell(2).SetCellValue(Double.Parse(Tablesandchairs.Value));
            sheet.GetRow(12).GetCell(2).SetCellValue(Double.Parse(Flowers.Value));
            sheet.GetRow(13).GetCell(2).SetCellValue(Double.Parse(Candles.Value));
            sheet.GetRow(14).GetCell(2).SetCellValue(Double.Parse(Lighting.Value));
            sheet.GetRow(15).GetCell(2).SetCellValue(Double.Parse(Balloons.Value));
            sheet.GetRow(16).GetCell(2).SetCellValue(Double.Parse(Papersupplies.Value));
            sheet.GetRow(20).GetCell(2).SetCellValue(Double.Parse(Graphicswork.Value));
            sheet.GetRow(21).GetCell(2).SetCellValue(Double.Parse(Photocopying_Printing.Value));
            sheet.GetRow(22).GetCell(2).SetCellValue(Double.Parse(Postage.Value));
            sheet.GetRow(26).GetCell(2).SetCellValue(Double.Parse(Telephone.Value));
            sheet.GetRow(27).GetCell(2).SetCellValue(Double.Parse(Transportation.Value));
            sheet.GetRow(28).GetCell(2).SetCellValue(Double.Parse(Stationerysupplies.Value));
            sheet.GetRow(29).GetCell(2).SetCellValue(Double.Parse(Faxservices.Value));
            sheet.GetRow(33).GetCell(2).SetCellValue(Double.Parse(Food.Value));
            sheet.GetRow(34).GetCell(2).SetCellValue(Double.Parse(Drinks.Value));
            sheet.GetRow(35).GetCell(2).SetCellValue(Double.Parse(Linens.Value));
            sheet.GetRow(36).GetCell(2).SetCellValue(Double.Parse(Staffandgratuities.Value));
            sheet.GetRow(40).GetCell(2).SetCellValue(Double.Parse(Performers.Value));
            sheet.GetRow(41).GetCell(2).SetCellValue(Double.Parse(Speakers.Value));
            sheet.GetRow(42).GetCell(2).SetCellValue(Double.Parse(Travel.Value));
            sheet.GetRow(43).GetCell(2).SetCellValue(Double.Parse(Hotel.Value));
            sheet.GetRow(44).GetCell(2).SetCellValue(Double.Parse(Other.Value));
            sheet.GetRow(48).GetCell(2).SetCellValue(Double.Parse(Ribbons_Plaques_Trophies.Value));
            sheet.GetRow(49).GetCell(2).SetCellValue(Double.Parse(Gifts.Value));

            // Force formulas to update with new data we added
            sheet.ForceFormulaRecalculation = true;

            // Save the NPOI workbook into a memory stream to be sent to the browser, could have saved to disk.
            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);

            // Send the memory stream to the browser
            ExportDataTableToExcel(ms, "EventExpenseReport.xls");

Here are the screen shots of the form and completed template…

Here is demo UI.

NPOI - ASP.NET Form used to populate a Excel Template

Here is a populated template.

NPOI - ASP.NET Form merged with Excel template

Here is a copy of the XLS file created in the program:  NPOI – ASP.NET Form merged with Excel template

Here is a copy of the source code for the demo:  NPOI – Visual Studio 2008 (ASP.NET 3.5) Template Merge Demo

** There was a caveat during the development process.  During the demo I found a bug with NPOI opening my XLS file and I had to download the source code and make two changes to get NPOI to open my XLS template (bug states this issue is caused when you save a XLSX file as XLS in Excel 2007). Read this post to see the fix or use the DLLs for NPOI included in my demo project.

** NPOI is a active Open Source project, the bugs found have probably already been fixed and if you download the latest code from CodePlex, you’ll probably have no problems running the demo.

Show a List of Posts for a Category on your 404 page

Today is my second day of being back online since moving hosts to GoDaddy from WebHost4Life. I would like to say the process was painless, but I had to call tech support at GoDaddy 5 times to get my hosting account setup right (Win Web vs. Windows). This undocumented but important configuration settings, will determine if you can or cannot run PHP on Windows at GoDaddy. So if you plan to sign-up for a Windows account and to run a PHP application (like WordPress), then make sure you call and order a “NON-GRID” account…

Now that things are back up and running I’ve noticed that I’m getting lots of hits on my old permalinks. I had some problems in moving things off my old host since each button click on their site would take 3-5 minutes (and time out 90% of the time). To help track what was happening, I set my 404 to go to WordPress’s “index.php” so I could track the hits and throw a pretty 404 message. After getting something setup, I decided I wanted to show a list of my posts so the user could quickly find what they are looking for. Since people only hit my Web Development posts, I inserted the logic below into my 404.php using the WordPress Editor under Appearance.

<?php 
	$out = '';

	$query = array();

	$query[] = 'category=web development';
	$query[] = 'numberposts=100';
        
	$posts_to_show = get_posts( implode( '&', $query ) );

	$out = '<ul>';

	foreach ($posts_to_show as $post_to_show) {
		$permalink = get_permalink( $post_to_show->ID );
		$out .= <<<HTML
		<li>
			<a href ="{$permalink}" title="{$post_to_show->post_title}">{$post_to_show->post_title}</a>
		</li>
HTML;
	}

	$out .= '</ul>';

    echo $out;
?>

This little snipped is based on the WP Show Post Shortcode. I basically hard coded the query category and set the number of result to 100. This results in a list of my last 100 blog posts, sorted by date posted.

If you want to see it in action, type in a invalid URL to my site and you’ll get a 404 with a list of links to my blog posts!

Note… This is not going to help with search engine rankings because it’s a 404 error page. My goal was to give the lost visitor quick access to my development posts, since I’m 99% sure they used an old link indexed by Google.

ASP.NET JSON Response to Dynamic HTML Table

Do you need a way to quickly display a HTML table of data from a JSON response, I got a solution. The key to doing all this is just a little JavaScript to parse your JSON results. By looping over the array of objects, you can build a dynamic table (no predefined columns) in just a few seconds. This solution can be used for any language, since I use .NET / jQuery most of my day, this is a ASP.NET demo.

If you just want to see a full working demo, download the sample project at the bottom of the article. It’s a VS 2008 project build-ready!

This user control was an enhancement to an existing user control whose core functionality is to take a user supplied T-SQL statement and generate a nicely formatted HTML table. The existing control has worked great for years, but since I recently moved my code from 2.0 to 3.5, I figured I’d give this module a little attention. I found pages using the module to sometimes take 15+ seconds to load because of slow database servers. I enabled caching to try and work around the problem, but users told me that some queries need to be real-time. The solution was to move from a standard GridView control to a AJAX jQuery solution that would pulled the data and convert the results to a pretty HTML table. I used Google to search for a existing solution, but everything I found used 3rd party components or required a results (defined columns) template (e.g. Name and Object[i].Name). Since my tool is designed for ad-hoc queries, I wanted the rendering logic to require no predefined facts about the data.

My demo has 3 external requirements, which are all FREE (jQuery, JavaScript JSON helper, JSON.NET).

Newtonsoft.Json.dll
jquery-1.3.2.debug.js
json.debug.js

In addition to these requirements, I added a few layout files to the demo to make the HTML table look nice. Nobody likes a ugly demo…

images / loading.gif
styles / default.css

The only thing left was the results page, rendering JS logic and the web service.

Here is the JavaScript function used to convert a JSON array of objects into an HTML table.

function CreateDynamicTable(objArray) {
    var array = JSON.parse(objArray);

    var str = '<table class="lightPro">';

    str += '<tbody><tr>';

    for (var index in array[0]) {
        str += '<th scope="col">' + index + '</th>';
    }

    str += '</tr>';

    str += '</tbody><tbody>';

    for (var i = 0; i &lt; array.length; i++) {
        str += (i % 2 == 0) ? '<tr class="alt">' : '</tr><tr>';

        for (var index in array[i]) {
            str += '<td>' + array[i][index] + '</td>';
        }

        str += '</tr>';
    }

    str += '</tbody>'

    str += '</table>';

    return str;
}

Pretty simple, right… I pass in an object array that I iterate over to grab all the row/column values. I use the first row to grab the column names and place them inside the table header.

[Update 4/30/10] – I created 2 function that are improvements on this in a new posting. Check it out to see example use and download the JS code. Much better solution than hard-coding the values. Read Now…

Here is the actual page that makes web service call and returns displays the HTML table.

<script src="scripts/jquery-1.3.2.debug.js" type="text/javascript"></script>
<script src="scripts/json.htmTable.js" type="text/javascript"></script>
<script src="scripts/json.debug.js" type="text/javascript"></script>
 
<script type="text/javascript">
        $(document).ready(function() {
            $.ajax({
                type: "POST",
                url: "DemoSvc.asmx/GetJSONTableContents",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                data: "{}",
                success: function(res) {
                    $('#<%= DynamicGridLoading.ClientID %>').hide();
                    $('#<%= DynamicGrid.ClientID %>').append(CreateDynamicTable(res.d)).fadeIn();
                }
            });
        });
</script>

<form id="form1">

<div id="DynamicGrid">
<div id="DynamicGridLoading">
            <img src="images/loading.gif" alt="" /><span> Loading Data... </span></div>
</div>

</form>

The code is pretty simple and there is no code behind, everything is done in the web service and injected via jQuery. The could have be done in a HTML page or any other language, nothing specific to ASP.NET right here.

The code for the web service is pretty basic, just be sure to look at the Service() method that is used to convert the DataTable to JSON. This is where the magic happens. This method is a wrapper to the JSON.NET library and chooses some default settings needed for the conversion.

In summary, this little switch removed the the blocking being caused by my slow SQL queries. My pages now instantly load **POOF**. During loading, they display a loading icon/text so the users know something is loading! Since caching was already being used for some queries, I change the code to cache the DataTable in the web service call.

VS2008 – ASP.NET DataTable to JSON to Dynamic HTML Table Demo

T-SQL Stored Procedure (Optional Parameters)

I use a lot of queries to drive reports and interactive search tools and the most common requirement of all my queries is optional parameters. I’ve used a ton of different methods to pull this off in the past, but I primarily use one approach when I build stored procedures that will be used with optional parameters that is efficient and easy to read.

-- //////////////////////////////////////////
-- Stored Procedure - Optional Params (NEW WAY)
-- //////////////////////////////////////////
CREATE PROCEDURE GetOrders
	@OrderNumber VARCHAR(10),
	@TrackingNumber VARCHAR(10)
AS
	SELECT * FROM SALES
	WHERE (@OrderNumber IS NULL OR @OrderNumber = '' OR ORDERNUMBER = @OrderNumber)
		AND (@TrackingNumber IS NULL OR @TrackingNumber = '' OR TRACKINGNUMBER = @TrackingNumber)



-- //////////////////////////////////////////////////
-- Stored Procedure - Optional Params - Dynamic SQL (OLD WAY)
-- //////////////////////////////////////////////////
CREATE PROCEDURE GetOrders
	@OrderNumber VARCHAR(10),
	@TrackingNumber VARCHAR(10)
AS
	DECLARE @STRSQL VARCHAR(200)
	SET @STRSQL = 'SELECT * FROM SALES WHERE 1=1 '

	IF @ROBINCODE &lt;&gt; ''
	BEGIN
		SET @STRSQL = @STRSQL + ' AND ORDERNUMBER = ''' + @OrderNumber + ''''
	END

	IF @DELTOCODE &lt;&gt; ''
	BEGIN
		SET @STRSQL = @STRSQL + ' AND TRACKINGNUMBER = ''' + @TrackingNumber + ''''
	END

EXECUTE(@STRSQL)

As you can see, the “New Way” has a lot less code and does not use dynamic T-SQL. In addition to checking for NULL parameter values, I also suggest you check for empty strings since text boxes normally return an empty string.

Now you have the choice of sending NULL or “” and the criteria will be skipped.