Chuck's Code Blog

Assorted solutions and musings

Hierarchal Grid with jQuery Data Table

For anyone that may be reading this on my WordPress code blog site I wish to first apologize for the really bad code formatting in this article. I have the worse time getting code to display with correct formatting on wordpress. Perhaps someday correcting this will become a priority for me…

So I had the challenge of creating a hierarchal grid using jQuery Data Table in an MVC web application. The grid had to have the following features \ attributes and look like the mock-up image below:

  • The detail grid has been indented to indicate greater contrast from transaction rows.
  • The row with client “Brand New” has an arrow that indicates there is submission data; therefore the row can be clicked on.
  • The row with client “LiveScan1” has a ‘se’ triangle that indicates the submission data is displayed below and to the right (south east).
  • The row with client “Modified” does not have an arrow, this indicates there is no submission data; therefore the row cannot be clicked on. (on hover the cursor would not be a pointer (hand)).
 Hierarchal grid

Mock-up for hierarchal grid using jQuery Data Tables. (Click on image to enlarge it.)


JavaScript \ jQuery

<asp:Content ID="Content3" ContentPlaceHolderID="HeadContent" runat="server"> 
<script src="<%= Url.Content("../../Scripts/jquery.cmttooltip.js")%>" type="text/javascript"></script>
<script src="<%= Url.Content("../../Scripts/globalize.js")%>" type="text/javascript"></script>
<script src="<%= Url.Content("../../Scripts/cultures/globalize.cultures.js")%>" type="text/javascript"></script>
    <% if (false) { %>
        <link href="~/Content/WebFramework/Site.css" rel="stylesheet" type="text/css" runat="server" Visible="false" />
        <link href="~/Content/Site.css" rel="stylesheet" type="text/css" runat="server" Visible="false" />
        <link href="~/Content/WebFramework/smoothness/jquery-ui-1.8.11.custom.css" rel="stylesheet" type="text/css" runat="server" Visible="false"/>
        <link href="~/Content/WebFramework/DataTables/dataTables.custom.css" rel="stylesheet" type="text/css" runat="server" Visible="false" />
    <% } %> 

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"> 

<script type="text/javascript">

/* Initialize the jQuery DataTable */ 
var oTable;           
oTable = $('#grid').dataTable({                 
"bJQueryUI": true, 
"sPaginationType": "full_numbers", 
"bFilter": true, 
"bAutoWidth": true, 
"aaSorting": [[2, 'asc']], 
"aoColumnDefs": [{ "bSortable": false, "aTargets": [0]}], 
"aoColumns": [null, null, null, null, null, null, null], 
"oLanguage": {                     
"sProcessing": "<%=.sProcessing%>", 
"sLengthMenu": "<%= Resources.sLengthMenu%>", 
"sZeroRecords": "<%= Resources.sZeroRecords%>", 
"sInfo": "<%= Resources.sInfo%>", 
"sInfoEmpty": "<%= Resources.sInfoEmpty%>", 
"sInfoFiltered": "<%= Resources.sInfoFiltered%>", 
"sInfoPostFix": "<%= Resources.sInfoPostFix%>", 
"sSearch": "<%= Resources.sSearch%>", 
"sUrl": "", 
"oPaginate": { 
"sFirst": "<%= Resources.sFirst%>", 
"sPrevious": "<%= Resources.sPrevious%>", 
"sNext": "<%=CrossMatch.StoreForward.Globalization.Resources.sNext %>", 
"sLast": "<%=CrossMatch.StoreForward.Globalization.Resources.sLast %>"
/* 'open' or 'close' an information sub-grid when a row is clicked on */ 
$('#grid tbody tr').click(function() {

              var rowstate = $(this).attr("rowstate");
              var relval = $(this).attr("rel");

              // If submissions are available, then: 
if (relval != "") {
                    var that = this;

                    // Check if row (subgrid) is marked 'closed'
                    if (rowstate == "0") {
                        // If closed, mark it 'open'; 
$(this).attr("rowstate", "1");
                        // Set the icon to show open state; 
$('#grid tbody tr >td[rel="' + relval + '"]').children().each(function(index, element) {
                        // Open the subgrid here.  // oTable.fnOpen(this, fnFormatDetails(), "info_row");
                    } else {
// If grid is marked 'open' - mark it 'closed' 
$(this).attr("rowstate", "0");
                        // Change the row icon back to closed.
                        $('#grid tbody tr >td[rel="' + relval + '"]').children().each(function(index, element) {

                        // Close the subgrid here. 
                $('#subgrid tbody tr >td[rel]').cmtttooltip(
{ tooltipid: "clientdetailpopup", mouseovercallback: onmouseovercallback, mouseoutcallback: onmouseoutcallback });


        var submissionsByTransactionId = new Array();
        function showSubmissionsList(element) {
            var transactionId = $(element).attr('rel') + '';
            if (submissionsByTransactionId[transactionId]) {
                oTable.fnOpen(element, fnFormatDetails(submissionsByTransactionId[transactionId], transactionId), "info_row");
            } else {
                var now = Date();
                var geturl = '/Transactions/Home/Get/' + transactionId + "?now=" + now;
                    type: 'GET',
                    url: geturl,
                    success: function(submissions) {
                        if (submissions) {                           
                            submissionsByTransactionId[transactionId] = submissions;
                            oTable.fnOpen($('#grid tbody tr[rel="' + transactionId + '"]').get(0),
fnFormatDetails(submissionsByTransactionId[transactionId], transactionId), "info_row");

        /* Function that builds the subgrid table for the row details */
        function fnFormatDetails(submissions, transactionId) {
            var sOut = '';
            sOut += '<table id="subgrid" cellspacing="0" border="1" style="padding-left:50px; margin-left:27px; padding-top:0; margin-top:0; width:98%;">';
            sOut += '<thead><tr>';
            sOut += '<th class="ui-state-default" rowspan="1" colspan="1"><span></span></th>';
            sOut += '<th class="ui-state-default" rowspan="1" colspan="1"><%= Resources.Destination %></th>';
            sOut += '<th class="ui-state-default" rowspan="1" colspan="1"><%= Resources.SubmissionStatus %></th>';
            sOut += '<th class="ui-state-default" rowspan="1" colspan="1"><%= Resources.StatusDate %></th>';
            sOut += '<th class="ui-state-default" rowspan="1" colspan="1"><%= Resources.ResponseCount %></th>';
            sOut += '</tr></thead>';
            sOut += '<tbody>';
            for (var i = 0; i < submissions.length; i++) {
                var milli = submissions[i].StatusDate.replace(/\/Date\((-?\d+)\)\//, '$1');
                var tmpdate = new Date();
                Globalize.culture("en");    // Sets culture to English (default)                
                sOut += '<tr rel="' + submissions[i].SubmissionId + '">';
                sOut += '   <td style="border-color:#D3D3D3 !important; border: 1px; border-style: solid;"  align="center" class="minimize" rel="' + submissions[i].SubmissionId + '">';
                sOut += '        <a href="javascript:void(0)" onclick="return gotopage (\'<%= Url.Action("SubmissionDetails","Home")%>/' + submissions[i].SubmissionId + '\')">';
                sOut += '            <div class="icon-edit">';
                sOut += '                <%=CrossMatch.StoreForward.Globalization.Resources.Edit%>';
                sOut += '            </div>';
                sOut += '        </a>';
                sOut += '    </td>';
                sOut += '<td style="border-color:#D3D3D3 !important; border: 1px; border-style: solid;">' + submissions[i].Destination + '</td>';
                sOut += '<td style="border-color:#D3D3D3 !important; border: 1px; border-style: solid;">' + submissions[i].Status + '</td>';
                sOut += '<td style="border-color:#D3D3D3 !important; border: 1px; border-style: solid;">' + Globalize.format(tmpdate, "d") + " " + Globalize.format(tmpdate, "T") + '</td>';
                sOut += '<td style="border-color:#D3D3D3 !important; border: 1px; border-style: solid;">' + (submissions[i].Responses ? submissions[i].Responses.length : 0) + '</td>';
                sOut += '</tr>';
            sOut += '</tbody>';
            sOut += '</table>';
            return sOut;


   <h2><%= CrossMatch.StoreForward.Globalization.Resources.Transactions %></h2>
        <div id="tabs-1" class="ex_highlight_row">  <%-- INBOUND --%>
            <table id="grid" style="border: 0;">    <%-- INBOUND --%>
                        <th class="minimize"></th>
                        <th><%= Resources.TCN %></th>
                        <th><%= Resources.Name %></th>
                        <th><%= Resources.Client %></th>
                        <th><%= Resources.ORI %></th>
                        <th><%= Resources.DAI %></th>
                        <th><%= Resources.SubmissionCount %></th>
                    <% foreach (var item in Model.Transactions)
                       { %>
                        <tr rowstate="0" rel="<%= (item.Submissions.Count > 0 ? item.TransactionId : "") %>">
                            <td class="minimize, ui-state-default" rel="<%= item.TransactionId %>">
                                <%if (item.Submissions.Count > 0)
                                  { // If submissions exist for this transaction, display a row icon.
                                %><span></span><% } %>
                            <td><%= Html.Encode(item.TCN)%></td>
                            <td><%= Html.Encode(item.Name) %></td>
                            <td><%= Html.Encode(item.ClientName) %></td>
                            <td><%= Html.Encode(item.ORI) %></td>
                            <td><%= Html.Encode(item.DAI) %></td>
                            <td><%= Html.Encode(item.Submissions.Count.ToString()) %></td>
                    <% } %>
        </div> . . .


public JsonResult Get(int id)
var submissionmodels = GetSubmissionsByTransactionId(id);
return Json(submissionmodels, JsonRequestBehavior.AllowGet);

February 16, 2012 Posted by | jQuery, MVC | Leave a comment

Suppress Those “CssClass not defined” issues in MVC.

I found a hack to suppress those CssClass not defined issues in MVC! The hack is to wrap duplicate link references (they already exist in our MasterPage) in a False evaluator (if loop) up in our “content3” area as shown below. Note that runat=”serverVisible=”false” attribute key \ value pairs have been added to the end of each link.

    <% if (false) { %>
        <link href="~/Content/WebFramework/Site.css" rel="stylesheet" type="text/css" 
runat="server" Visible="false" />

        <link href="~/Content/Site.css" rel="stylesheet" type="text/css" 
runat="server" Visible="false" />

        <link href="~/Content/WebFramework/smoothness/jquery-ui-1.8.11.custom.css" 
rel="stylesheet" type="text/css" runat="server" Visible="false"/>

        <link href="~/Content/WebFramework/DataTables/dataTables.custom.css" 
rel="stylesheet" type="text/css" runat="server" Visible="false" />

    <% } %>

I’ve also seen where people have created a SupressWarnings.css that have empty classes defined so technically they are defined even though practically they do nothing from a CSS point of view.

January 6, 2012 Posted by | CSS, Error Solutions, MVC | , , , , , | Leave a comment

Using OVER() with Aggregate Functions

I was recently asked about a T-SQL function that I hadn’t heard about, so I did some digging around and found out that the OVER() function was added to Microsoft SQL 2005.  It gives you the ability to add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying a partition for each function. The way it works is similar to joining an aggregated copy of a SELECT to itself. For example, consider the following:

SELECT customerID, productID, orderDate, orderAmount
FROM Orders

customerID  productID   orderDate               orderAmount
———– ———– ———————– ———————
1           1           2007-01-01 00:00:00.000 20.00
1           2           2007-01-02 00:00:00.000 30.00
1           2           2007-01-05 00:00:00.000 23.00
1           3           2007-01-04 00:00:00.000 18.00
2           1           2007-01-03 00:00:00.000 74.00
2           1           2007-01-06 00:00:00.000 34.00
2           2           2007-01-08 00:00:00.000 10.00

(7 row(s) affected)

You can now easily return the total orderAmount per customer as an additional column in this SELECT, simply by adding an aggregate SUM() function with an OVER() clause:

SELECT customerID,  productID, orderDate, orderAmount,
      SUM(orderAmount) OVER (Partition by CustomerID) AS Total
FROM Orders

customerID  productID   orderDate               orderAmount   Total
----------- ----------- ----------------------- ------------- ---------
1           1           2007-01-01 00:00:00.000 20.00         91.00
1           2           2007-01-02 00:00:00.000 30.00         91.00
1           2           2007-01-05 00:00:00.000 23.00         91.00
1           3           2007-01-04 00:00:00.000 18.00         91.00
2           1           2007-01-03 00:00:00.000 74.00         118.00
2           1           2007-01-06 00:00:00.000 34.00         118.00
2           2           2007-01-08 00:00:00.000 10.00         118.00

(7 row(s) affected)

The previous SQL is essentially shorthand for:

    o.customerID, o.productID, o.orderDate, o.orderAmount, t.Total
    Orders o
    SELECT customerID, SUM(orderAmount) AS Total
    FROM Orders
    GROUP BY customerID
  t ON t.customerID = o.customerID

since the two return the same results.

Note that the total returned using SUM(..) OVER (..) is not the total for the entire table, just for the scope of the SELECT where it is used. For example, if you add a filter to the SELECT to return only rows for ProductID 2, the totals will reflect that criteria as well:

SELECT customerID,  productID, orderDate, orderAmount,
      SUM(orderAmount) OVER (Partition by CustomerID) AS Total
FROM Orders
WHERE productID = 2

customerID  productID   orderDate               orderAmount   Total
----------- ----------- ----------------------- ------------  ------------
1           2           2007-01-02 00:00:00.000 30.00         53.00
1           2           2007-01-05 00:00:00.000 23.00         53.00
2           2           2007-01-08 00:00:00.000 10.00         10.00

(3 row(s) affected)

That is a nice advantage over the old way of linking to a derived table, since in that case you’d need to repeat the criteria for both the primary (outer) SELECT and also the derived table.

Typically, SUM(..) OVER(..) is most useful for calculating a percentage of a total for each row. For example, for each Order we can calculate the percentage of that order’s orderAmount compared to the customer’s total orderAmount:

SELECT customerID,  productID, orderDate, orderAmount,
       orderAmount / SUM(orderAmount) OVER (Partition by CustomerID) AS Pct
FROM Orders

customerID  productID   orderDate               orderAmount   Pct
----------- ----------- ----------------------- ------------  -------
1           1           2007-01-01 00:00:00.000 20.00         0.2197
1           2           2007-01-02 00:00:00.000 30.00         0.3296
1           2           2007-01-05 00:00:00.000 23.00         0.2527
1           3           2007-01-04 00:00:00.000 18.00         0.1978
2           1           2007-01-03 00:00:00.000 74.00         0.6271
2           1           2007-01-06 00:00:00.000 34.00         0.2881
2           2           2007-01-08 00:00:00.000 10.00         0.0847

(7 row(s) affected)

Of course, be sure that you don’t encounter any divide by zero errors by using a CASE if necessary.

While I’ve made many references to using the SUM() function, of course this technique works with any of the other aggregate functions as well, such as MIN() or AVG(). For example, you could return only Orders where the orderAmount is below the average for the product that was ordered using this:

	SELECT customerId, productID, orderDate, orderAmount,
		AVG(orderAmount) OVER (partition BY productID) AS ProductAvg
	FROM orders
) x
WHERE x.orderAmount < x.productAvg

customerId  productID   orderDate               orderAmount   ProductAvg
----------- ----------- ----------------------- ------------- -----------
1           1           2007-01-01 00:00:00.000 20.00         42.6666
2           1           2007-01-06 00:00:00.000 34.00         42.6666
2           2           2007-01-08 00:00:00.000 10.00         21.00

(3 row(s) affected)

It is my understanding that some SQL implementations allow you to use SUM(..) OVER (..) to calculate running totals for a SELECT, but unfortunately that does not appear to be possible using SQL Server 2005. However, there are other ways to accomplish this in T-SQL if you really need to; my general recommendation is to do this at your presentation layer if those totals are not needed for further processing at the database.

January 6, 2012 Posted by | Uncategorized | , , | Leave a comment

New Twitter “Tweet Button”

New Twitter “Tweet Button” For those of you who have been dreaming of an easier way for your readers to share your posts on Twitter, that day has come. WordPress has added an official Tweet Button as an option for all blogs.  How it works: When one of your readers hits the Tweet Button, they will be shown a popup that includes a shortened link to your post. Readers can add in a quick message, and then hit “Tweet” to send the post to their … Read More

August 18, 2010 Posted by | Uncategorized | Leave a comment

Error 1402 When Installing MS Virtual Server 2005

While installing Microsoft Virtual Server 2005 I encountered the following error:

Error 1402. Could not open key:
Verify that you have sufficient access to that key,
or contact your support personnel.

To fix the issue, I updated the parent folder HKLM\Software\Classes
permissions. When I looked at the permisions on this key it had only one entry: “Everyone” with Full Control. I added the SYSTEM account with Full Control and this fixed the error.

The specific steps I took to succesfully resolve this error are as follows:

  1. Launch the registry editor (Start > Run > Regedit);
  2. Navigate to HKLM\Software\Classes and right click Classes to get the context menu;
  3. Select Permissions… from the menu, you will see Permissions for Classes dialog box;
  4. Click Add and a Select Users or Groups dialog box will pop up;
  5. In the Enter the object names to select text box type SYSTEM;
  6. The Check Names button should become enabled – click it;
  7. SYSTEM should become underlined indicating it was recognized as valid;
  8. Click Ok and with SYSTEM highlighted be sure Full Control is checked;
  9. Click Ok, close the Registry Editor, and retry the Virtual Server installation again.

I hope this post has helped you.

May 6, 2010 Posted by | Error Solutions | , | Leave a comment

Wake On LAN (WOL)

In the process of learning about the new Wake on Wireless technologies, I found myself adding some Wake On LAN (WOL) functionality to my kiosk application, I thought I would perhaps post some of that code and info here.  One of these days I will sit down and do a proper publication, but for now WordPress is still new to me and I haven’t the time that I would like to do this correctly.

Certain network cards also support a security feature called “SecureON password”. This feature allows users to set a Magic Packet password. The password is 6 bytes like the MAC address. Still, only a few NIC and router manufacturers seem to support such security features.

What do I need to use WOL?

• A network card that can support WOL with its cable to the motherboard properly installed.

• In the BIOS Power Management, you must enable the LAN Wakeup option. The option might also be named:

o Wake On LAN,                                                     o Resume on LAN,
o MAC Resume From S3/S4,                            o Resume on LAN/PME#,
o MACPME Power Up Control,                        o Wake on LAN from S5,
o Power On By Onboard LAN,                          o Wake Up On LAN,
o Power Up By Onboard LAN,                          o Wake Up by Onboard LAN,
o Resume by LAN,                                                 o WOL (PME#) From Soft-Off
o Resume By WOL,

• Then take a look at your network card settings, (right click mouse on “My Computer” icon on your desktop, select Manage -> “Device Manager”) in “Device Manager” open the properties of your “Network Card” and select “Power Management” tab. Wakeup should be also enabled as shown below.


Power Management Settings


Wake from Shutdown = On; Wake-Up Capabilities = Magic Packet.

Some network cards support additional options to wake up from shutdown.

After completing these settings, check that the “Good Connection” light (typically green led) on the back of the network card is lit when the machine is off.

Most implementations send the “magic packet” via UDP over either port 7 or port 9 .  So you’ll have to make sure that port is forwarded by your firewall/router to the IP broadcast address.


Magic packet

The magic packet is a broadcast frame containing anywhere within its payload: 6 bytes of ones (resulting in hexadecimal FF FF FF FF FF FF), followed by sixteen repetitions of the target computer’s MAC address.

Since the magic packet is only scanned for the string above, and not actually parsed by a full protocol stack, it may be sent as a broadcast packet of any network- and transport-layer protocol. It is typically sent as a UDP datagram to port 0, 7 or 9, or, in former times, as an IPX packet.



using System;
using System.Net;
using System.Net.Sockets;
namespace WakeOnLan
    class Program
        static void Main(string[] args)
            byte[] mac = new byte[] {0x00, 0x0F, 0x1F, 0x20, 0x2D, 0x35};
        /// <summary>
        /// Sends a Wake-On-Lan packet to the specified MAC address.
        /// </summary>
        /// <param name="mac">Physical MAC address to send WOL packet to.</param>
        private static void WakeUp(byte[] mac)
            // WOL packet is sent over UDP
            UdpClient client = new UdpClient();
            client.Connect(IPAddress.Broadcast, 40000);
            // WOL packet contains a 6-bytes trailer and
            // 16 times a 6-bytes sequence containing the MAC address.
            byte[] packet = new byte[17 * 6];
            // Trailer of 6 times 0xFF.
            for (int i = 0; i < 6; i++)
                packet[i] = 0xFF;
            // Body of magic packet contains 16 times the MAC address.
            for (int i = 1; i <= 16; i++)
                for (int j = 0; j < 6; j++)
                    packet[i * 6 + j] = mac[j];
            // Submit WOL packet.
            client.Send(packet, packet.Length);

November 11, 2009 Posted by | UMPC | , | 2 Comments

Button Map for the Samsung Q1-Ultra UMPC.

I’m working on a home automation project that involves amongst other things creating a software application that operates on Windows XP Tablet Edition running on a touchscreen-enabled Samsung Q1-Ultra Ultra Mobile Personal Computer (or UMPC).

One of the requirements of this application is that the UMPC will operate in a kiosk mode – okay simple enough, [perhaps I’ll post some information on accomplishing this by trapping certain key combinations and using the Windows Group Policy Editor if anyone is interested] but for this article the question is, “How do I disable some of the hardware buttons on the computer?”

We still want some of the hardware buttons to be available to the user.  Buttons such as “Vol-“, “Vol+”, as well as the traditional keyboard characters will be very useful, so we don’t want to disable them.  Other buttons will come in quite handy for purposes other than their intended uses, for example the arrow up and arrow down buttons on the user pad to the right will provide the user with handy Volume Up and Volume Down buttons (the Vol- and Vol+ buttons are used to control the volume locally on the UMPC where as these arrow up and arrow down buttons will send a control code to the AMX brain that will in-turn adjust the actual source volume for the various routed zones.

The first step of this task after determining the list of which hardware buttons we need to control or disable, is to identify how the system addresses these hardware keys.  I wrote a simple application that hooked into the low-level keyboard handler and trapped all values that came through.  From there I was able to create a table that shows for each hardware button what the BIOS key value is in both Hex and Decimal as well as the constants defined for those values in the Windows API WinUser.DLL library file and the .NET Windows.Forms.Key Keys enumerator.  Both of these are necessary because I wanted to trap some key codes in a dll and utilize some of the keys within the managed C# application.

Below I have provided the table showing the button mappings and the buttons’ relative positions on the UMPC.

Q1-U_Left Q1-U_Right

Button BIOS (Hex) BIOS (Dec) Windows (Hex) WinUser DLL Constant .Windows.Forms.Key
▲ Up
0x58 088 0xFF VK_X Keys.X
▼ Down 0x59 089 0xFF VK_Y Keys.Y
◄ Left
0x56 086 0xFF VK_V Keys.V
► Right 0x57 087 0xFF VK_W Keys.W
Internet 0x32 050 0xAC VK_2 Keys.D2
Menu 0x54 084 0xFF VK_T Keys.T
UDF 0x6E 110 0xFF VK_DECIMAL Keys.Decimal
Q \ AV 0x74 116 0xFF VK_F5 Keys.F5
Shutter 0x64 100 0xFF VK_NUMPAD4 Keys.NumPad4

One of the biggest concerns was the Internet button.  In a kiosk-type application that does not use the Internet, this button is a huge security hole in that people could launch Internet explorer and in the address bar use “File///…” and access anything within the UMPC.  It was a relief to me when I discovered that Samsung had used a standard keyboard key for the number 2 (above the Q and W on a QWERTY keyboard).  This meant there is a way to trap for that keystroke and prohibit it from launching the (in this case) Internet Explorer application.

November 4, 2009 Posted by | UMPC | , , , , | Leave a comment