ASP.NET: Scrollable GridView with Fixed Headers: jQuery

Sometimes I find that it's almost impossible to achieve something which should really be incredibly straightforward. Maybe it's because I'm an idiot, or maybe it's because Microsoft have missed a trick.

In this case, I needed to be able to freeze the colum headers of a GridView and make it vertically scrollable. I tried about a dozen different methods, from pure CSS to various JavaScript hacks, and even attempting to do it server-side to try to ensure browser compatibility. I won't link to any of the solutions I tried except one, since only one of them actually worked.

As usual, it was jQuery to the rescue. A simple plugin which you can use to freeze the column headers and add a vertical scrollbar, and with minimal effort. It works as tested in Firefox 3, Chrome and Internet Explorer 8 - though sometimes the column headers are a couple of pixels out of line here or there.

Anyway, there's a nice article describing how to use it on ASP Snippets.

The only drawback I found was that I couldn't really get it to behave properly when the window was resized - but I suspect that was my poor implementation of the plugin rather than a fault with the jQuery code itself.

In conclusion: Microsoft: Get your act together and make this a feature that can be enabled via a property.

Atlassian JIRA Startup Failed: Configuring JIRA to access SQL Server 2008 Express R2

I recently upgraded my SQL Server 2008 Express Edition with Advanced Services to R2, and found that my JIRA installation stopped working (received Tomcat 404 errors).

I tried everything, but could not get it to work - I kept getting 'connection refused' errors in the JIRA logs. I hadn't modified my server.xml configuration since the upgrade, and I checked that the protocols in SQL Server Configuration Manager were enabled on Port 1433.

Something wasn't right - even with the same configuration as before I just could not get JIRA to start.

Eventually, I found the answer which worked for me:

• Run config.bat in the /bin folder of the JIRA installation folder.
• Click the Database Tab:
• Database type: SQL Server
• Hostname: localhost (Or your server name. Note: NOT "localhost\SQLExpress")
• Port: 49374 (Find this value in your registry, in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp for your SQL instance name. i.e. SQLEXPRESS. You can edit your registry by typing 'regedit' at the command line.)
• Database: jiradb
• Username: jirauser
• Password: *****
• Schema: jiraschema
• Connection Pool: 20
• Click Test Connection.

Hopefully the utility will return 'Connection successful.'. Restart the Atlassian JIRA service and you should find that you are able to access your JIRA installation.

Worked for me - I'm not sure why I needed to do this for R2.

More info in the comments attached to this forum post.

SQL Server: Columns truncated to 255 characters when using OPENROWSET or OPENDATASOURCE to import from Excel

I was using the SQL command OPENROWSET to directly import a load of text from an Excel spreadsheet to my database. The columns were of varying widths, some up to 3,000 characters.

The problem I experienced was that for some columns the output of the statement was being truncated to 255 characters - but not for all of them. I couldn't understand why this would happen and tried various combinations of OPENROWSET and OPENDATASOURCE, but to no avail.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

    INSERT INTO tblRichTextFields (Requester,feet,approval) 
    SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="C:\RichtextFields.xls";Extended Properties=''Excel 12.0''')...[Sheet1$]

GO

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 0
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

The only thing I could think of was that it was something to do with the Excel cell formats. I compared the columns which were being truncated to the column which was not, but they were the same. I tried changing them all to Text to see if that made any difference. Still truncated.

I couldn't find much more on Google, nor in my brain - but eventually I did find a fix which proved me right - it was some underhanded 'guesswork' on Microsoft's part - surprise, surprise. See this quote:

"Your data may be truncated to 255 characters if the first 8 records for the field(s) being truncated contain 255 or fewer characters. The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column."

I changed the Registry Setting which controls the 'Excel format guessing' behaviour from 'on' to 'off', logged out and back in - and Hey Presto! it worked.

Setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Mi­­crosoft\Jet\4.0\Engines\Excel­\­TypeGuessRows

Change the value from 8 hex to 0

I don't think this fix will work in every situation and I haven't considered every possible implication of changing this registry setting, but if your text is being truncated like this - this is worth a try. Take a backup of your registry before changing it and it's not my fault if you Michael Berk it up!

Here's some detailed info on the Microsoft website.

97 Things Every Programmer Should Know

Opinions are often divided on the best approach to programming and software development, but there's a really nice book with 97 points which are pretty tough to argue with.

It's by a bloke called Kevlin Henney, and it's called 97 Things Every Programmer Should Know: Collective Wisdom from the Experts. There are some really excellent points in there, and you can see the content of the book on O'Reilly's commons wiki.

It covers topics such as techinical debt, the importance of good quality user stories (test plans) and good approaches to test driven development.

SQL Server: DateDiff with Weekdays

I needed a simple way to work out the number of weekdays between two dates for a report (not worried about holidays).

The native datediff function in T-SQL works with calendar days, so I needed a custom function. I lifted this off a forum and modified it a bit:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnDateDiffWeekdays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[fnDateDiffWeekdays]
GO

/*
=============================================
Description: Calculate datediff for weekdays
Usage:   select dbo.fnDateDiffWeekdays('2011-05-09 09:04:22.593','2011-06-09 09:04:22.593')
=============================================
*/

CREATE function dbo.fnDateDiffWeekdays
(
 @fromdate datetime,
 @todate datetime
)
RETURNS int
AS 
begin
 declare @procdate datetime, @enddate datetime
 declare @weekdays int
 set @procdate = @fromdate 
 set @weekdays = 0

while (@procdate < @todate) 
begin
 if (datepart(dw, @procdate + 1) <> 1) and (datepart(dw, @procdate + 1) <> 7)
 
  set @weekdays = @weekdays + 1
  set @procdate = dateadd(d, 1, @procdate)
 
end
---
if @todate is null 
set @weekdays = null

return @weekdays
end

Worked fine in my testing!

ASP.NET: Regular Expression client side date validation using "d MMMM yyyy" format

In my apps, I like to use the AJAX CalendarExtender control to allow the user to select dates from the calendar picker. I also like to allow the display of the full date format since I believe it to be more readable, for example

31 December 2012

The problem is, I find it really tough to validate this date format and even when I tried to switch to other date formats and change the localization settings in web.config, I still had no real success. I tried CompareValidators, CustomValidators and various combinations of RegularExpressionValidator but simply could not get it to work.

Eventually, I found a regular expression which pretty much does the trick. I don't believe it’s over-engineering the solution, because I could not find another solution which works.

Basically I added the regular expression to my web.config file so that it was only listed in one place, should I need to change it. Then I created a little shared function which returns the RegEx, and on Page_Load I set the ValidationExpression property of the RegularExpressionValidator controls to the result of the function, and therefore to the RegEx.

Here's the code:
Page Markup

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<asp:TextBox ID="txtStartDate" runat="server" AutoPostBack="true" ></asp:TextBox>
<asp:CalendarExtender ID="calStartDate" runat="server" TargetControlID="txtStartDate" Format="dd MMMM yyyy"></asp:CalendarExtender>

<asp:RegularExpressionValidator ID="reValStartDate" runat="server" ControlToValidate="txtStartDate"
         ErrorMessage="Enter a valid date" ></asp:RegularExpressionValidator>

Code Behind

Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
reValStartDate.ValidationExpression = UIUtils.DateValidatorRegEx()
End If
End Sub

''' <summary>
''' Return the regular expression used to validate dates
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function DateValidatorRegEx() As String
Return ConfigurationManager.AppSettings("DateValidatorRegEx")
End Function

Web.Config

<add key="DateValidatorRegEx" value="^(?:((31 (January|March|May|July|August|October|December))|((([0-2]\d)|30) (January|March|April|May|June|July|August|September|October|November|December))|(([01]\d|2[0-8]) February))|(29 February(?=-((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))) ((1[6-9]|[2-9]\d)\d{2})$"/>

This solution isn't perfect, because the above validator does not allow for leap years (i.e. 29 February 2008 was a valid date), and I don't really like having to explicitly bind the ValidationExpression property of every date validation control in the code behind. I think it would have been better to set the ValidationExpression in the markup and directly reference the function result, but I couldn't get that to work. Apparently you can use a CodeExpressionBuilder to set server side control properties using code blocks, but I felt that would be over-engineering the solution.

Anyway, I hope this is of use to someone. I'll certainly be using this method in future projects.

HTC Desire HD turns itself off: Solved!

I recently bought an HTC Desire HD to replace my iPhone 3GS.
It's an excellent phone in many ways, but has one or two flaws. When on a business trip I set my alarm for the next morning and left it switched on and plugged into the charger. I had never used the Alarm Clock app before.

Since I had been caught out recently by the iOS alarm clock bug on January 1 and 2, I didn't entirely trust that the alarm would go off.

My mistrust was well placed, and it was lucky I set the bedside alarm too, because the phone alarm didn't go off. The phone's power had cut out.

When I turned it back on, much to my dismay the phone kept turning itself off at random every 20-30 minutes and for no apparent reason. I couldn't figure out what was going on and thought maybe it was something to do with a loose connection in the phone or the battery.

I was about to send the phone back under warranty, and thought I should really spend some time to see if I can find the answer before going to the trouble of being phoneless for a few days while it gets fixed.

So I eventually stumbled across the answer on AndroidForums (The link takes you to the main page because I found the post last night and since haven't been able to locate it).

1. Turn phone off and remove the battery, SIM, and memory card.
2. Leave the phone for a minute and put the memory card and battery back in (not the SIM).
3. Turn back on, allow to boot and then switch off again.
4. Take the battery out again, wait a moment and put back in with SIM too. Turn on.

Apparently this type of reboot clears out some files that a normal restart wont, and causes certain programs that are set to start (and that could be the cause of the issue) to be disabled.

I'm not sure if the Alarm Clock was the problem, but it seems a coincidence that this happened after the very first time I had set an alarm on the phone.

Anyway, I found that not only did this simple fix solve my issue and thus prevent me from having to send the phone back, but the Desire HD started working at lightning speed after this!

Result!

SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component

In trying to use SSIS for some ETL, I wanted to be able to pass variables about in my packages to use in updating the status of tasks in a SQL Server database.

I got my package to set a variable to the result passed OUT of a stored procedure, but I could not get it to work when trying to pass variables IN to a stored procedure. I kept getting errors such as "The query failed to parse. Syntax error, permission violation, or other nonspecific error".

I spent hours trying to get it to work.

It would not work.

It drove me nuts.

Then I found this post on Rafael Salas' blog which, ten minutes after reading, led me to the answer to my problem and a working DTSX package.

Basically all that's involved is creating another package variable to contain the SQL you want to use, then evaluating it as an expression which allows you to use your other variables in the SQL and to execute the stored procedure as desired.

There's nothing quite like seeing those DTSX package tasks lighting up green as it runs successfully!

SQL Server Business Intelligence Development Studio 2005: Variables Window Invisible

When trying to create a DTSX package in SQL Server Business Intelligence Development Studio 2005, I was looking to add a variable to assign to the result of a stored procedure.

When I clicked the SSIS menu option and chose Variables or right clicked on the DTSX designer surface and chose the same option, nothing at all happened. It quickly drove me nuts and no matter what I did, I could not get the damned Variables window to appear!

Then finally I opened the Window menu and clicked Auto Hide All. When I did this, the Variables window appeared next to the Toolbox and stayed visible when I reopened my project!

Who knows why this happened, but if you happen to be struggling with this obscure issue then hopefully this solution will work for you.

D-Link ShareCenter Pulse and Microsoft Sync Toy: Automatically sync files and folders in Windows

Since I had no decent way of backing up my files and photos other than using an Internet based service, I recently bought a NAS box, namely the D-Link ShareCenter Pulse DNS-320 for £69.99 from Amazon (the price seems to have gone up since I bought it). See this review.


My first impressions after a few days are that it's a brilliant unit which comes with an iTunes server, uPnP server for streaming to PS3, Xbox 360 etc and also a built in BitTorrent client for downloading files without the need for a power hungry PC.

It doesn't come with drives (which you'd expect at that price) so I put a 500GB SATA disk in which is more than enough for my needs just now. It's very quick to set up, and within an hour or so I had it ready to accept my files and it was streaming my iTunes and AV library on the LAN to the PC, PS3 and 360.

But the problem I had was that I wanted to back up certain folders on my PC to the NAS without having to copy things manually. Ideally I wanted a utility that would monitor certain folders and if their contents changed, the changes would be mirrored between my PC and the NAS box. After some Googling around, I found a nice straightforward solution which does exactly what I needed: Microsoft SyncToy.

It's free, and is a very straightforward utility that you can use to sync folders automatically. You just pick the folders you want to sync, decide whether changes should be reflected on both devices or just one way, and off you go. It will mirror the two folders and any changes you make to one will be reflected in the other.


So now if I copy photographs onto my PC, as long as the NAS box is up and running the pictures will be mirrored to the folder on the NAS.