atom innovation

Solved: Visual Studio 2010: ASP.NET project - can't add web reference

2012-01-17T14:41:00.002Z

I hadn't worked on a project for a while (since upgrading to Visual Studio 2010), and I needed to add a web reference to get it to compile. The normal method is just to right-click on your project name in Solution Explorer and click Add Web Reference.

But I had no option to Add Web Reference, only an option to 'Add Service Reference' which seems to be designed for WCF references.

It's a legacy project though, so I needed to add the reference in the legacy format - adding a service reference creates different components in your project than a web reference.

I got round it by:

1. Change the target framework to .NET 2.0
2. Close and reopen the solution - you should see the option to Add Web Reference.
3. Add the web reference.
4. Change the target framework back to whatever you had before.
5. Close and reopen the project.
6. Continue upon your way.

This might not work for everyone since I don't really know why the option to add web reference disappeared in the first place - but it worked for me.

Lots of screens? Lots of windows? WindowPad!

2011-12-16T10:44:00.003Z

I use three screens for work, and I usually have about 12-15 windows open at once. I'm also a bit precious about the window sizes and positions of the things I'm working with - especially when using Visual Studio or another tool with a lot of dockable windows.

I hate having to drag windows about and manually resize them. Enter WindowPad!

It's a little utility which consists of an executable file and an ini file for configuration - you just put it in your Startup folder to use.

WindowPad uses a virtual "pad" to arrange windows, where you can use the Numpad to move and resize windows to fill the appropriate sections of your screens.

Features include:
- Move windows within the current monitor or between monitors (in multi-monitor setups).
- Customize hotkeys via WindowPad.ini with AutoHotkey-like command syntax.
- Define custom commands as labels or functions in WindowPad.ahk.
- Execute WindowPad commands specified on the command-line.

This is one of the tools mentioned in my .NET tools post, but it's the best utility I've discovered in a long time.

Power tools every .NET developer should have

2011-12-09T12:49:00.003Z

Working every day within a Windows environment, you get used to your own specific way of customising your workspace.

I'm always on the look out for ways to make my laziness less of an issue by being able to do common tasks more quickly, and Scott Hanselman's Ultimate Developer and Power Users Tool List for Windows is brilliant.

There are some really useful applications on there and in particular I liked WindowPad because it helps you to quickly arrange the various Visual Studio windows on your screens rather than having to drag and resize constantly.

Visual Studio: Selecting which browser to debug with

2011-11-23T11:55:00.004Z

I love Google Chrome. It's fast to start up, fast to render pages, it knows how to use CSS and it's just generally excellent.

I'm less keen on IE though. However, since it's the most popular browser that's the one I tend to debug my web applications with. But Visual Studio kept launching Chrome instead of IE - turns out it's a simple fix:

1) Right click on a .aspx page in your solution explorer
2) Select the "browse with" context menu option
3) In the dialog you can select or add a browser. For example, if you want Firefox in the list click "add" and point to the firefox.exe filename
4) Click the "Set as Default" button to select the default browser when you run any page on the site.

Works. Life made easier.

Solution bumped from Stackoverflow.

P.S.: If you don't see a 'Browse with' option - check you're not debugging already! Stop debugging and the option will appear.

Windows Remote Desktop: The terminal server has exceeded maximum number of allowed connection, mstsc

2011-08-30T20:37:00.003+01:00

I kept getting the dreaded 'The terminal server has exceeded maximum number of allowed connection' when attempting to log onto a remote server. I must've forgotten to log out and just clicked close on the mstsc session!

The answer:

You can start a control session to the server and logoff your other connections. Please always use "LogOff" when you leave remote desktop.

Open Command Prompt.
Type: "mstsc /admin".
Remote Desktop Connection will start. Type the computer name or IP address of the computer you want to connect to in the Computer box.
Configure any other desired options, and then click Connect.

Solution from the page at this link

ASP.NET: Scrollable GridView with Fixed Headers: jQuery

2011-08-11T10:11:00.003+01:00

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

2011-07-31T21:09:00.006+01:00

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

2011-07-21T15:20:00.010+01:00

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\Microsoft\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

2011-07-19T14:35:00.003+01:00

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

2011-06-09T09:11:00.002+01:00

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

2011-05-10T16:25:00.007+01:00

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!

2011-01-28T13:35:00.007Z

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

2011-01-07T11:02:00.003Z

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

2011-01-06T15:25:00.004Z

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

2010-12-18T18:58:00.010Z

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.

ASP.NET: Web Application Spell Checking with jQuery and AtD (After the Deadline)

2010-09-29T14:41:00.013+01:00

Why did I need it?
In a recent project, we had a very large form with wizard steps and several multiline text area controls. The form was submitted to senior managers, so the requirement was raised to provide some kind of automated spell checking functionality.

Given that this is a web application, it wasn't obvious which solution to choose. You can ask you users to install browser plugins which will check spelling, but this isn't really pratical since very few people would actually do it. Or you can install a full third party application on your server and use some kind of AJAX call to check spelling.

If a spell checker does not make good suggestions to fix spelling or grammar mistakes, then it becomes pointless. So we needed something with a good comprehensive dictionary and it had to be quick.

After some rooting around and consideration of third party products and also possibly somehow piggybacking on SharePoint's spell checking service, I found a product called AtD (After the Deadline).

What is AtD?
AtD is an open source solution which you can plug into your website using the wonderful jQuery (though it also offers other spell checking methods). It seems like an odd name to me, but apparently AtD is named after a similarly titled blog in the New York Times.

It's a fantastic product and only takes a short time to set up. Using the jQuery version which I chose, it runs client side and will highlight spelling and grammar errors, and provide a good set of suggestions to fix them. It's quick, user friendly and it even works in IE6. Use this link to download the source code and view a couple of tutorials, though the tutorial I found most valuable was this one:

Demo 3

Here is Demo 3 in action:

Your browser does not support iframes.

You can use that page to try out AtD for yourself, and if you view its source you can follow the simple step by step instructions to get started.

There are tutorials on the site, but basically you either link to the source code and css, or embed it in your project, use a little bit of jQuery code to assign the spell checker to each textarea on your page, do any layout customisations you need and wham bam - it works. You don't need to add any markup to your existing page, the jQuery will do the legwork for you.

It wasn't all plain sailing…
I did have a small problem in getting up and running with AtD. What's the problem I most often come up against in web development? That's right, Internet Explorer.

Basically the issue was in getting the spell checker buttons to float to the right hand side of the textarea controls (to me, this seems the most obvious place to put them), but by default AtD will add the spell checker button to the top right hand side of the textarea using a dynamically rendered span element.

I thought it'd be easy to get the spell checker button to sit where I needed it simply by modifying the css to make the spelling button float to the right of the textbox. This worked perfectly in Firefox, but in IE when you float the button, the onclick behaviour inexplicably stops working, thus rendering the whole thing useless. That was no good.

Of course, you can edit the css used to render the AtD elements. So I played around with it for a while and ended up changing the default CSS for the spelling buttons to look like this:


.AtD_proofread_button 
{
   width: 14px;
   height: 14px;
   background-image: url('../images/writing.gif');
   margin-left: 465px;
   position: absolute;
}
.AtD_edit_button 
{
   width: 14px;
   height: 14px;
   background-image: url('../images/editing.gif');
   margin-left: 465px;
   position: absolute;
}

I removed the display:block property, added position: absolute and added a left margin which is roughly equivalent to the width of the textarea controls in my project. This meant that the buttons sat nicely alongside the textboxes, and the absence of a float property meant that IE and Firefox would render the whole thing nicely.

Conclusion
After the Deadline makes for a superbly straightforward and lightweight spell checking solution for web applications which adds real value. I'll certainly be using this in any future web project where spell checking is required.

ASP.NET: Limiting the length of a multiline textbox

2010-09-24T15:24:00.003+01:00

ASP.NET comes with a lot of nice validator controls which save you the hassle of writing a lot of JavaScript components yourself.

The TextBox control has a MaxLength property which allows you to easily restrict the length of the input. But bizarrely, a TextBox with its TextMode property set to MultiLine does not have this property.

But of course, you still need a way to limit the amount of text input or your database operations could fail.

Previously, I was using a JavaScript function attached to these textboxes with a function which was fired on keyup or lost focus which counted the number of characters and prevented overflow of a maximum set amount.

That's the long way of doing it though: you can achieve the same validation using a RegularExpressionValidator, as highlighted on this post by Raj Kaimal.

Fundamentally, you create a RegularExpressionValidator control, point it at your multiline textbox and set this as the RegEx (300 in this example obviously being the maximum number of characters allowed):

^[\s\S]{0,300}$

Et Voila! A simple and elegant solution - and no custom JavaScript required!

ASP.NET: InvalidCastException from Strongly-Typed DataSet (XSD)

2010-09-16T14:35:00.005+01:00

This one has had me tearing my hair out for months.

In most of my ASP.NET projects I would have an XSD DataSet which models my database schema and acts as the data access layer for the application. But in several situations I would get unbelievably annoying exceptions from the designer file whenever my code attempted to get a boolean or date value from the database which was null. You would receive an exception like this:

StrongTypingException was unhandled by user code.

The value for column 'abcdefgh' in table 'tblAbcdef' is null.

My first step was to open the Properties of the column in the XSD designer and make sure that the AllowDbNull property is set to true. Then in the NullValue property I tried to change the behaviour from (Throw exception) to (Empty) or (Nothing). But Visual Studio blew up in my face and informed me that my selection was invalid.

'Fine' I thought, 'I'll just check the value for DbNull before trying to set it in my code'. But when I tried to do such an IF statement, the StrongTypingException would still occur because before my expression tried to evaluate the column value, the designer would throw the StrongTypingException, thus never allowing the evaluation to take place.

I couldn't find the answer to this, until a much smarter colleague pointed out the extremely simple answer. The designer file catches a InvalidCastException and then throws a StrongTypingException. So in my own code, I just need to catch the StrongTypingException and ignore it - assuming that ignoring it is the correct thing to do in that piece of code. I had been trying to catch the InvalidCastException which of course is incorrect because the designer had replaced that with the other exception type.


    Try
        .ClientContact = dbRecord.ClientIsStudent
    Catch ex As Global.System.Data.StrongTypingException
        ' Ignore since these are generated from the typed DataSet designer
    End Try

This worked and I am delighted to finally have the answer to this problem which has been bugging me for ages!

If I had actually read the designer file code I might have spotted that it throws a different type of exception to the one I was trying to catch.

ASP.NET: Failed to access IIS metabase (IIS)

2010-09-03T11:03:00.005+01:00

When installing everything on my new machine, came across a problem in IIS when publishing my projects there. I got the error:

Failed to access IIS metabase
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code

The error directs you to a Microsoft KB which isn't particularly helpful.

I had seen this error before when using SQL Server Reporting Services but the method I used last time works for SSRS, but not in this case. After a bit of Googling I found that this is a problem with the account permissions that ASP.NET runs under in IIS, as well as a method to solve it.

1. Gave permissions to the ASPNET account using "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis –ga machinename\ASPNET".
2. Reset IIS (iisreset on command line) and that resolved the issue for ASP.NET 2.0.
3. We registered ASP.NET 1.1 with IIS as well using command "C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis –i".
4. Reset IIS again.

That did the job for me. Some other people had to go to greater lengths such as uninstalling .NET and reinstalling it, but the above steps resolved the problem in my case.

Solved: Lenovo ThinkPad W510: High pitched squealing noise

2010-08-31T15:20:00.008+01:00

I recently got a Lenovo ThinkPad W510 laptop, which is a bit of a beast (PC Advisor Review). There's an Intel Core i7 CPU running at 1.73 GHz, 3GB RAM and a nice 15.6" LED Screen. It's very well put together and feels like it will last for a long time, especially with the hard disk shock detection technology which turns off your hard drive if the chassis detects that the machine is dropped onto the ground

However, I had an issue with the laptop where it would make a constant high pitched squealing noise, almost so high pitched that only dogs would hear it, but unfortunately not quite.

I thought I could live with it but when you're sitting next to it for 7-8 hours a day and the sound is drilling into your mind, it becomes quite annoying. But fortunately, unlike many others who had the same problem, the answer proved to be achievable without making any support phone calls or having any parts replaced.

There's a very comprehensive suite of Lenovo settings which are preinstalled on the machine, and in particular there's an application called Power Manager.

Since it's a high performance machine designed for development or graphics editing and therefore has heavy battery usage by default, it gives you fine grained control over the balance between performance and power. You can slow down the CPU, dull the screen and switch off optical drives if you're running on battery power, and when you're on AC you can disable all that and let it run at full tilt.

These types of controls are nothing new, but in there was the answer to my squealing problem.

As part of Power Manager, there's a setting called 'Deep Sleep' which is some kind of CPU power management function. This was the cause. So I created a new 'power management profile' within the Power Manager applet and set Deep Sleep to off, then saved it.

After activating my new profile, the noise immediately went away! Thank goodness for that, because I had to get rid of the noise one way or another before it drove me nuts.

Rounded Corners

2010-08-30T14:35:00.007+01:00

I was asked to put some content inside a box shaped element which had rounded corners. Not square corners, it had to have rounded corners.

Unfortunately, since HTML is so 'boxy' this behaviour does not come naturally. It's not just a setting on your table or div element, and there's no particularly easy way to achieve this effect.

Until CSS3 and its border-radius property comes into play in all of the next generation browsers, creating a screen element with rounded corners needs to be done another way. More info on border-radius and browser support is available on CSS3.info.

I found that the effect can be achieved using images and divs. There's a nice article on the Webcredible site which gives you a step by step tutorial on exactly how to do it.

N.B: Being honest, you can actually use browser specific properties such as '-moz-border-radius' to display rounded corners, but I couldn't find the equivalent for IE which is no good since a large chunk of users will be using IE.

ASP.NET Error: "The DataSource does not support sorting"

2010-08-23T12:10:00.009+01:00

In one of our projects we had a GridView which was by default bound to an ObjectDataSource to allow us to Sort/Page and to apply custom filtering. But we also needed to be able to search the data which the GridView is bound to, which we achieved in code and then databound the GridView to the resulting DataTable.

Here's a code snippet:

Before


        grdvSummaryGrid.DataSourceID = String.Empty

        Dim _clientBLL As New ClientBLL()
        grdvSummaryGrid.DataSource = _clientBLL .Search(e.SearchText)
        grdvSummaryGrid.DataBind()

Using this code, a problem arose where if you sorted the GridView and then carried out a search, an error resulted when the code attempted to DataBind it to the resulting table:

"System.NotSupportedException: The data source does not support sorting"

I tried a few things and considered creating an ObjectDataSource in code with the search results and then binding the GridView to that, but the answer turned out to be simple.

Before setting the DataSourceID of the GridView to an empty string to allow you to set its DataSource to the resulting DataTable (you can't set both properties), you clear the sort expression.

Like this:

After (Notice the new first line)


        grdvSummaryGrid.Sort("", SortDirection.Ascending)
        grdvSummaryGrid.DataSourceID = String.Empty

        Dim _clientBLL As New ClientBLL()
        grdvSummaryGrid.DataSource = _clientBLL .Search(e.SearchText)
        grdvSummaryGrid.DataBind()

That did the job. Lots of people seem to have had this problem too, so hopefully this answer will be useful.

ASP.NET: Sorting a DataTable

2010-08-21T14:21:00.003+01:00

I was pulling out a DataTable from my database and applying some sorting to it in the Stored Procedure, which of course worked as expected.

But I needed the user to be able to pull a row out from a GridView, edit its fields and then reinsert it to the GridView, all the time preserving the sort order.

As usual, I went round the houses looking at all the usual crappy solutions that others who also forget to read the documentation come up with, until I found a simple solution that worked for me:


     dataTable.DefaultView.Sort = "date_of_birth asc"

Simples!

Here's the MSDN documentation.

CSS: RadioButtonList Layout

2010-08-18T10:01:00.008+01:00

For the most part, I love web development, but there are some things that I absolutely detest about it.

For example, something that should be straightforward such as getting horizontal radio buttons to appear in a neat and tidy way can be tough. Obviously your radiobutton labels will have different values and will therefore cause the list to have different widths - this can be messy if you have a few rows of radiobuttons.

In addition, the radiobutton label will by default wrap down to underneath the radiobutton itself which is hideous. That needs to be fixed.

You could try laying the controls out with a table or a series of fixed size divs, but you shouldn't need to. And using tables could be nasty.

CSS to the rescue! Create a class to use for your RadioButton controls and assign it.


.radioButtons {
    border:none;
    border-collapse:separate ;
}
.radioButtons input {
    float:left;
}
.radioButtons label {
    margin-left: 25px; 
    display: block; 
}
.radioButtons td{
    min-width:175px;
}

Then just lay out your control like this (an ASP RadioButtonList control is used here but you could use HTML literals too I guess):


<asp:RadioButtonList ID="rblYesNoMaybe" runat="server" RepeatDirection="Horizontal" 
CssClass="radioButtons" >
    <asp:ListItem>Yes</asp:ListItem>
    <asp:ListItem>No</asp:ListItem>
    <asp:ListItem>Maybe</asp:ListItem>
</asp:RadioButtonList>

Using this, you should find that your RadioButtons look better and that your label text doesn't wrap underneath the radiobutton input. You'll probably need to set the min-width property of the td to an arbitrary value to suit your page or site.

ASP.NET: Inserting NULL Values Into SQL Server Database

2010-06-26T13:46:00.003+01:00

I literally almost destroyed a laptop today when trying to get around the fact that empty dates in my application were being stored in SQL Server as the completely useless '1900-01-01 00:00:00.000'.

Note that SQL Server is not the issue here, it WILL insert a NULL value if it's told to. If you're seeing this 1900 date then it's coming from your application.

I'm using an XSD within my Visual Studio project, and therefore it auto-generates methods for each of the methods I define on my TableAdapters. I needed to get null dates into these methods and then into the database without the application passing the 1900 date to SQL Server.

I tried loads of different methods of checking the values, by trying to pass Nothing, SqlDateTime.Null or System.DBNull.Value where the date was empty, but to no avail, I either got cast errors or the database did the insert but continued to think we were at the dawn of the 20th century.

Finally, I figured it out, and really there are two ways of achieving this. One is to use a Typed DataSet, which allows you to use the autogenerated 'SetMyColumnNameNull()' method.

But I'm not using a typed DataSet for my database insert methods, rather I call the stored procedure methods in my BLL and pass in the values rather than a row object.

So anyway, the answer was this:

When passing your date to your method which calls your stored procedure, pass your dates as strings. Then in your code you can check them like this:



            (System.ComponentModel.DataObjectMethodType.Insert, True)> _
    Public Function InsertOrder(ByVal date_in_question as String)

Dim dateInQuestion As Global.System.Nullable(Of Date)

        ' Bust any empty dates
        If date_in_question <> "" Then
            dateInQuestion = CDate(date_in_question)
        End If

        ' Do the Insert
        Dim orderId As Integer = Adapter.db_Insert_Update_Order(dateInQuestion)

Return orderId

Now when the date is passed to the stored procedure's method, if no date is set then an empty date is passed and the database inserts a NULL value.

You might want a more robust way of parsing your dates etc etc, but this worked for me.

Hope I've provided enough detail.

ASP.NET FileUpload Control Inside an UpdatePanel

2010-06-21T15:56:00.006+01:00

I put a FileUpload control inside an UpdatePanel and ran into some problems, as many others did.

Since the UpdatePanel does not do a full postback by nature (and therefore only partially refreshes the screen), the FileUpload control does not work straight away.

To get it to work you need to add a PostBackTrigger which points at your Upload button. Here's a Microsoft example on that very topic.

Using a PostBackTrigger will cause a full postback to fire when a file is uploaded and will cause your control to work... in most cases.

However I found an odd issue where the file upload did not work the first time (the FileUpload control's HasFile property was always False) but it would upload successfully the second time.

I had to root around in a few places and eventually found the answer on StackOverflow's forums.

Turns out the answer is to define this in your Page_Load event:


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Page.Form.Enctype = "multipart/form-data"

End Sub

After I added this, the FileUpload control started to work every time.

Syntax Highlighter Success

2010-06-14T10:11:00.007+01:00

Finally, I managed to get SyntaxHighlighter working.

I had tried a few things previously, but it seems that some of the tutorials I had seen were based on older versions. The joy arrived when I found this post.

Now I can post code snippets in a way which is actually readable!


SELECT  
    COUNT(dbid) as TotalConnections 
FROM 
    sys.sysprocesses 
WHERE  
    dbid > 0

Hurrah!

I'm still using Stanley Shilov's utility to escape my HTML so that I can post using these simple tags:


<pre class="brush: html">
</pre>

SQL Server 2005: Determining the Total Number of Open/Active Connections

2010-06-14T09:46:00.006+01:00

Sometimes applications can become unresponsive or generate SQL Server timeout exceptions (System.Data.SQLClient.SQLException: Timeout Expired) and sometimes you just need to find out how much traffic is hitting your database.

You can find out a few things by using these commands:

Total Number of Connections by Database


SELECT  
    DB_NAME(dbid) as DBName,  
    COUNT(dbid) as NumberOfConnections, 
    loginame as LoginName 
FROM 
    sys.sysprocesses 
WHERE  
    dbid > 0 
GROUP BY  
    dbid, loginame

Total Connections


SELECT  
    COUNT(dbid) as TotalConnections 
FROM 
    sys.sysprocesses 
WHERE  
    dbid > 0

Detailed description of all running processes


sp_who2 'Active'

NB: You probably need to be logged in with SA privileges to run these commands.

ASP.NET: ListView Control and Nested ListViews

2010-06-09T23:13:00.004+01:00

I have grown to love (in a purely sensible way) the ASP.NET ListView control. It's very flexible and gives you a lot of control over how you display your data on your site.

I needed to display on a summary screen not only the orders belonging to a specific customer, but the products belonging to each order. So I built a ListView to display the orders, then nested another ListView inside it which displays the products belonging to that order.

After some rooting around on the net, I found this excellent tutorial for nesting ListViews that I'll definitely be revisiting in future.

ASP.NET: Select DISTINCT from DataTable

2010-06-07T17:10:00.007+01:00

I needed to filter a GridView using a bunch of DropDownLists which were placed in its header. As you filter the GridView, the drop down lists had to update according to the filtered data in the GridView (i.e. they should not display filter options for data that's not visible in the table).

In order for this to work, I had to get a distinct list of values for each column with which to populate the drop down lists. This required getting a distinct list of values from a DataTable, and this behaviour does not come naturally to a DataTable since it's a representation of a SQL Database table rather than an actual database table which you can run SQL against.

I found a nice tutorial on this, which I'm linking to here for future reference, in both VB.NET and C#.NET versions.

I had to pass the DataSource my GridView was pointing at into a method which bound the DropDownLists using the function demonstrated in Microsoft's tutorials above.

Implemented, hooked into my GridView and Bob's My Uncle.

Very nice!

ASP.NET: Handing an empty Repeater control

2010-06-04T21:20:00.007+01:00

The ASP.NET Repeater control is very useful and gives you lots of control over your output when compared to other controls such as the GridView. However, that comes at a cost of having to wire up all of your own events, sorting and paging etc.

Additionally, another drawback is that for a reason I can't explain, there is no EmptyDataTemplate or EmptyDataText property in a repeater which you can use when there are no items to display. Rumour abounded that ASP.NET 2.0 would include such a template, but it was not included.

Several people have tried different methods of handling this situation, but I found one which I think works nicely. I have a user control with a repeater in it, and on the Page_PreRender event I check that the Repeater's DataSource has rows in it. If not, then a label is made visible that tells the user no rows are present. If it does have data, then the Repeater is displayed.

Code

Label:


<asp:Label ID="LabelNoProducts" runat="server" Text="You haven't added any products yet" visible="false" ></asp:Label>

Event: (User Control PreRender)


    Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
        Dim ds As DataTable = RepeaterProductsList.DataSource

        If ds.Rows.Count = 0 Then
            RepeaterProductsList.Visible = False
            LabelNoProducts.Visible = True
        Else
            RepeaterProductsList.Visible = True
            LabelNoProducts.Visible = False
        End If
    End Sub

Et voila. Each time your user control is loaded, it checks if there are rows in the repeater, and lets the user know if not.

ASP.NET: Read Contents of Text File

2010-06-04T15:23:00.003+01:00

In my Web Application Project, I wanted to read some CSS styles in from a file to dynamically style a system generated email.

It was pretty easy, but I thought I should record my code for future:


Dim emailCss As String = vbNullString

        Dim fileName As String = HttpContext.Current.Server.MapPath("~/utilities/Email.css")

        If File.Exists(fileName) Then
            Dim ioFile As New StreamReader(fileName)

            emailCss = ioFile.ReadToEnd()

            ioFile.Close()

        End If

ASP.NET: Render Control into HTML String

2010-06-03T10:56:00.011+01:00

Occasionally there is a need to get string representation of ASP.NET control in other words - render it into string instead of let it be rendered on the page. For example, you may want to grab the HTML rendering of an ASP.NET server control and attach it to an email.

The following method renders control into HTML string.

Namespaces used:


using System.Text;
using System.IO;
using System.Web.UI;

Below is RenderControl method implementation, which receives any control and returns its HTML string representation.

C#


public string RenderControl(Control ctrl) 
{
    StringBuilder sb = new StringBuilder();
    StringWriter tw = new StringWriter(sb);
    HtmlTextWriter hw = new HtmlTextWriter(tw);

    ctrl.RenderControl(hw);
    return sb.ToString();
}

VB.NET


Public Function RenderControl(ByVal ctrl As Control) As String
        Dim sb As New StringBuilder()
        Dim sw As New StringWriter(sb)
        Dim htw As New HtmlTextWriter(sw)

        ctrl.RenderControl(htw)

        Return sb.ToString()

End Function

There are a few drawbacks to this approach, in that any LinkButtons or other controls which depend on being inside an HTML form will fail unless you include the form in the email body too.

See these two (rather old) posts on 4GuysFromRolla.com by Scott Mitchell:

Part 1
http://www.4guysfromrolla.com/articles/091102-1.aspx

Part 2:
http://www.4guysfromrolla.com/articles/102203-1.aspx

VB.NET: Use a Ternary Operator to check variable before assignment

2010-04-30T12:50:00.004+01:00

It's a nice thing to be able to quickly check a value before assigning it to a variable, perhaps in a situation where you want to avoid a NullReferenceException.

If you want to do this, you can use a Ternary Operator which allows you to do the whole thing on one line:

Dim s As String
s = If(Session("mySessionVar") Is Nothing, String.Empty, Session("mySessionVar").ToString)

For more detail, refer to this blog post.

ASP.NET: Reset Selected Value of AJAX Combo Box

2010-04-12T16:48:00.008+01:00

I have been banging my head off my desk for hours and hours, trying to find a way to reset the value of an ASP.NET Control Toolkit Combo Box.

Fundamentally it's a brilliant control and lends some superb ease of use to an application when applied properly, but for some reason it's lacking a straightforward and functional way to reset its value to a default of something like:

<asp:ListItem Value="0"> -- Select -- </asp:ListItem>

I tried various combination of these, plus messing about with the AppendDataBoundItems property and the ViewState setting:


ComboBoxSelector.SelectedIndex = -1
ComboBoxSelector.SelectedIndex = 0
ComboBoxSelector.SelectedIndex.ClearSelection()
ComboBoxSelector.SelectedIndex.Dispose()
ComboBoxSelector.Items.Clear()

Those are not important, the thing to note is that using any of the above will not actually reset the value of the Combo Box.

To reset it, you have to loop through the hidden controls that are created at runtime, and reset those to the default value:


Dim ctl As Control
For Each ctl In ComboBoxAllKeywordCategories.Controls
If TypeOf ctl Is HiddenField Then
CType(ctl, HiddenField).Value = "0"
End If
Next

This works, and resets the Combo back to the default value you want. I no longer feel the need to put my fist through my monitor, so, result.

ASP.NET: How to Group Data in the GridView Control

2010-04-09T10:51:00.008+01:00

One of my clients was not happy with a GridView control that repeated a category name next to every product inside that category. He asked if we could group the table to display the category name only once rather than displaying it for every product, e.g. something like:

'Crap', I thought. 'This doesn't come easily to the GridView control.'

But after some research into several different methods, I found a good one that worked well for me. It was created by a developer called 'dotNetSoldier', and the control is called the ZNet Extended GridView.

You just download the DLL file and drop it into your bin folder, add the control to your Toolbox and drag it onto your page. Then follow the instructions on his blog to get started.

I'd rather not have had to add another reference in my project or use a third party control, but I couldn't get the traditional GridView to behave in this way.

SQL Server: Deleting duplicates using a Common Table Expression (CTE)

2010-03-22T09:55:00.005Z

In a recent project I had to figure out how to delete a series of duplicates from a tablet to prevent a report becoming bloated with them.

There's more than one way to skin a cat, but I think I have found a pretty efficient way of deleting dupes. Of course it would be preferable to avoid creating the duplicate rows in the first place, but sometimes you just have to delete them after they have been created.

A recent development in the SQL Server world is that of Common Table Expressions (CTEs). These can be used to help you delete duplicates using a single command, rather than using a temporary table or too many joins.

The CTE is formed by selecting the table into a temporary result set which you can then instantly relate back to the original table to find which rows to delete. You could use syntax similar to the following:

WITH Duplicates_CTE
(FirstName, LastName, Email, Phone, id)
AS (
SELECT FirstName, LastName, Email, Phone, MIN(PersonId) id
FROM tblPerson GROUP BY FirstName, LastName, Email, Phone
)
DELETE FROM tblPerson WHERE PersonId NOT IN (
SELECT id FROM Duplicates_CTE
)

The first select statement gets a distinct result set (without duplicate rows) from the problem table by using the MIN function and a GROUP BY clause. Then you can join back to the original table from the CTE result set and delete every row that does not correlate to the CTE - which means that you're left without any duplicate rows.

Et voila, simples! No duplicates and it's all done in one command.

ASP.NET Code Behind Intellisense Not Working

2010-03-16T20:55:00.003Z

This has been infuriating.

So many times when developing web applications using Visual Studio 2008 Enterprise Edition or Visual Studio Web Developer Express 2008, the intellisense in the code behind would stop working, and when the project is deployed any reference to page controls would result in a null reference exception.

There are quite a few things to try:

Close Visual Studio (Or VSWDE) and delete the intellisense files which are normally stored in your Windows profile.
Open the IDE again and rebuild the project.
Check that your aspx page is properly wired up to your code behind in the page directive.
Delete the project dll file in the bin folder and rebuild your project.
This is the one that worked for me: Check that there is a designer file associated with that page. If there is not, then in Visual Studio, right click on the project in Solution Explorer and click Convert to Web Application. That should regenerate the designer file for you.

If your intellisense isn't working and your code behind just can't seem to reference the controls, there are a lot of things that could be wrong. But if you try all of those, then hopefully you'll be back coding again instead of angrily trying to figure out what's wrong.

Styling GridView Controls with CSS

2010-03-05T10:57:00.003Z

Doing a lot of ASP.NET development usually means that GridView controls will be a regular feature in your applications, unless you prefer to go for the manual approach or use a repeater.

One of the biggest pains I've had in the past with GridViews is styling them consistently. But it wasn't enough of a pain to motivate me to spend the time styling them with CSS, until I started an entirely new application which would feature dozens of them.

So I located this tutorial which was absolutely perfect - just what I was looking for. It has a screen shot of the end result, good instructions and easily duplicated code and CSS that will get you on your way to having a consistent look and feel for all your GridView controls throughout your application.

CSS min-height and IE6

2010-03-02T11:27:00.005Z

I've been working on a new application which has to be fully compatible with IE6 and IE8. Being quite a fan of using min-height to avoid nasty squished pages, I noticed that the min-height CSS property is rendered properly in IE8, but NOT in IE6 (shock).

A nice, easy workaround I found is this:

content {
min-height: 630px;
height: auto !important;
height: 630px;
}

This works in IE6, without messing up the formatting in IE8 and other browsers.

SQL Server 2008 Express Edition: Installing on a Domain Controller

2010-01-27T21:46:00.005Z

If you try to install SQL Server 2008 Express Edition on a machine which acts as a Domain Controller using the packaged Web Platform installer, it will fail and you will see an error in the installation log.

This is because it will attempt to install using Network Service as the default account, which is not allowed on a domain controller. If you look in the logs, you will see an error such as:

Network Service or Local Service account is not permitted for the SQL Server service on a domain controller

If you experience this issue, you need to do a customised installation. This blog post highlights the issues involved and has links to the appropriate installlers to get you up and running.

NOTE: Microsoft do not recommend installing SQL Server on a Domain Controller, so you need to think about your performance requirements and the potential issues such an installation may cause.

ASP.NET Error: System.Web.HttpException: Maximum request length exceeded

2010-01-27T09:57:00.003Z

On several applications I have developed, the users need to be able to upload binary documents (discussed in other posts).

After a while, I kept getting the error:

System.Web.HttpException: Maximum request length exceeded

Easy fix. The problem is that by default, the maximum request size is 4mb. So when a user tries to upload a file which is larger than that, an exception occurs. The resolution is to increase the maximum size of the request, so add the attribute below to web.config (you can use a maxRequestLength up to "2097151" (2 GB) for the .NET Framework 2.0):

<configuration>

<system.web>
<httpruntime maxrequestlength="10240">

This is also explained quite nicely but without much background in a Knowledge Base article on the Microsoft Support site.

Remember, if you are imposing a maximum file size for upload you should notify the user of this restriction and handle any attempt to upload a larger file, or they might see errors again.

ASP.NET: Maintain Browser Scroll Position After Postback

2010-01-19T16:32:00.003Z

In many applications, the screen can have many controls which cause postback events. And if the screen has any vertical scrollbars, users will typically object if the scroll position is not maintained (fair enough) and they have to scroll back down to find where they were and also to spot which changes the postback caused.

I had been mucking about with JavaScript solutions to this, which actually did work. But the easiest way is to follow one of these methods:

1. Set it programmatically

Page.MaintainScrollPositionOnPostBack = true;

2. In the page declaration:

<%@ Page MaintainScrollPositionOnPostback="true" %>

3. Or as a global setting in the web.config file, modify the pages section to add the property:

<pages maintainScrollPositionOnPostBack="true" >

Done!

ASP.NET Error: Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control

2010-01-15T16:07:00.003Z

If you are using controls which are bound in a cascading parent-child relationship, you might see the error:

Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control

I kept getting this, but then after reading this tutorial, I realised that I had been sorting a GridView in my Page_Load event, regardless of whether the page request was a PostBack or not.

This was causing the databinding events to get borked up, and hence the error. So I added a simple IF statement around the sorting command to only do so if the page request was not a postback. Et Voila! Success!

Lesson: If you see the above error, check that you are not attempting to bind to a dependent dropdown list a value from its container’s data context when the latter was not bound to the container.

In English: check where in the page lifecycle your binding/sorting takes place otherwise you will unhook your controls from their binding and break your code.

ASP.NET: Search GridView and Programatically Select Row

2010-01-07T11:26:00.013Z

On the main page of one of my apps, I have a large GridView containing a lot of rows. They're paged, but the number of pages can run to ten or twenty.

So the users needed a quick way to locate a specific item within the GridView rather than having to manually search it each time. I added an AJAX Combo Box to the page, which displays an autocomplete selector for the items which also display in the GridView. On it's SelectedIndexChanged event, I needed it to automatically select the corresponding row on the GridView.

I searched the net to find the best way to do it, but there were several problems with the solutions people had found. There's no point dwelling on those at length, but the main issue was that if you loop through a GridView's DataKeys, you only loop through the DataKeys for that specific page, not the pages which are not currently displayed. The suggested code looked like this:

For n As Integer = 0 To myGridView.DataKeys.Count - 1
If myGridView.DataKeys(n).Value = myKeyObj Then
myGridView.SelectedIndex = n
End If
Next

That's fine if you only have one page on your GridView, but is not a lot of use when you have enabled paging and you have a requirement to search the entire data set.

A helpful person on the ASP.NET forums posted some code which allows you to loop through the pages of the GridView, so I modified it slightly and ended up with the following:

Dim i As Integer, DataSetIndex As Integer
Dim SelectedRowIndex As Integer
Dim dv As DataView = ObjectDataSourceClients.Select
Dim dt As DataTable = dv.ToTable

For i = 0 To dt.Rows.Count - 1
If dt.Rows(i)("Client_ID") = ComboBoxClientSearch.SelectedValue Then
DataSetIndex = i
Exit For
End If
Next

GridViewAllClients.PageIndex = DataSetIndex \ GridViewAllClients.PageSize
SelectedRowIndex = DataSetIndex - (GridViewAllClients.PageSize * GridViewAllClients.PageIndex)
GridViewAllClients.SelectedIndex = SelectedRowIndex

GridViewAllClients.DataBind()

Originally, as per the poster's code I had tried to just access the DataSet directly by using:

Dim ds As DataSet = GridViewAllClients.DataSource

This didn't work and I kept getting null reference exceptions because the GridView's DataSource is not persisted after a PostBack by the ViewState.

So in this case, I had to access the ObjectDataSource that the GridView was using, fire its Select method and convert the result to a DataTable which I could search.

That worked nicely, so I posted my findings back to the forum.

NOTE: The above code depends on the GridView and its DataSource being sorted the same way. So if you programatically sort your GridView after it's bound and then attempt to search using the above method, it won't work because the rows in the GridView and its DataSource will be out of sync.

ASP.NET: Retreive ID of inserted record

2009-12-24T14:23:00.009Z

I needed to be able to access the returned identifier value from an Insert operation on my ASP page, so that I could use the value in other controls. You can do this by adding an event handler to your ObjectDataSource:

Protected Sub ObjectDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) Handles ObjectDataSource1.Inserted
Dim returnvalue As Int32
returnvalue = Convert.ToInt32(e.ReturnValue)
' Do what you need to do with the ID here
Session.Add("CustomerID", returnvalue)

When your ObjectDataSource fires its Insert method, the value that the underlying BLL method returns is available for you to use in the above method. You can then set a session variable with this ID or do something else with it.

Just make sure that your BLL is returning the ID properly, or this won't work.

Job done!

ASP.NET: Uploading Documents to SQL Server and Retrieving them

2009-12-23T15:48:00.006Z

I've just completed a new function within one of my applications that allows users to upload binary files (such as Word, Excel, PDF, Powerpoint and images) and store them within SQL Server itself, rather than loading the files onto the file system of the web server. This reduces the complexity of the system and means that a backup will take care of all user data, including their documents.

There are several steps in getting it set up, none of which are particularly complicated but the documentation I found online was a bit sketchy at best.

Aside from the database components, the whole lot was done in Visual Studio Express 2008 with Visual Basic.

SQL Server Database Layer
The requirement was to be able to upload documents and associate them with individual customers. In order for the browser to know what type of file is being returned, you have to store the MIME type as well as the other bits and bobs:

CREATE TABLE [dbo].[Client_Document](
[Client_Document_ID] [int] IDENTITY(1,1) NOT NULL,
[Filename] [varchar](128) NOT NULL,
[Description] [varchar](2048) NULL,
[Binary_Document] [varbinary] (MAX) NOT NULL,
[MIME_Type] [varchar](50) NULL,
[Upload_Date] [datetime] NULL,
[Uploaded_By] [varchar](255) NULL,
[User_ID] [varchar](7) NULL,
[Lob_ID] [numeric](38,0) NOT NULL

The actual file is in the column [Binary_Document] [varbinary] (MAX). Previously, to store files in the database itself, you were encouraged to use the old 'Image' data type, but since SQL Server 2005 this has been deprecated. Instead, use varbinary(MAX) which automatically scales depending on the size of the file inserted (up to 2Gb - you may want to check the file size in code to keep it to a sensible level).

Then you just need the usual CRUD stored procedures to INSERT, UPDATE, DELETE.

Data Access Layer (DAL)
First, add your TableAdapter to your XSD file, then add your methods which tie up to your stored procedures. Check that the data type of the Binary_Document column has been set to System.Byte().

Business Logic Layer (BLL)
Create a BLL for the Client Document which defines an adapter based on the Client_Document datatable. Add your BLL methods which map to the methods defined in your DAL. You can add some validation in here or just call the DAL methods directly, passing the arguments which have come from the calling ASP page.

Presentation Layer
On my ASP page, I have a GridView which displays the current documents associated with the selected customer, as well as a FormView which allows new documents to be added, as well as allowing updates to existing document details (or to overwrite the file with a new version).

Passing the documents into the database and retrieving them is pretty much the same as anything else you've done, except that you need to capture the binary stream of the selected file and pass it into the database. The file selector control comes for free from the .NET framework:

<asp:FileUpload ID="FileUploadClientDocument" runat="server" Width="320px" />

Then, when you call your methods to save or update you can check the attributes of the selected file, extract its filename, size and MIME type, and save them into the database using your BLL methods, e.g:

' If a file has been selected, load it into the database with its details
If FileUploadDocument.HasFile = True Then
Dim fileBytes(FileUploadDocument.PostedFile.InputStream.Length) As Byte
FileUploadDocument.PostedFile.InputStream.Read(fileBytes, 0, fileBytes.Length)

' Display the uploaded file's details
UploadDetails.Text = String.Format( _
"Uploaded file: {0}<br />" & _
"File size (in bytes): {1:N0}<br />" & _
"Content-type: {2}", _
FileUploadDocument.FileName, _
FileUploadDocument.FileBytes.Length, _
FileUploadDocument.PostedFile.ContentType)

' Insert
clientDocumentInfo.AddClientDocument(cloverClientId, FileUploadDocument.FileName, strDescription, fileBytes, FileUploadDocument.PostedFile.ContentType, strAddedBy, strAddedBySid, Session("lobId").ToString)

Make sure that you not only define the file's length (line 2), but actually use the Read method of the InputStream to add the data to the variable (I forgot to do this and got files in the database which were the right size but completely blank!).

UploadDetails is just a label which displays the attributes of the file which has been loaded (size, MIME etc).

Getting the files back is just a case of retreiving the file from the database using the stored proc, then setting the Response to match the MIME type and the file's binary stream.

In this case, clicking on the filename in the GridView fires the below code and returns the file to the browser:

If e.CommandName.CompareTo("GetClientDocument") = 0 Then
' Since we are not using the Select command, we have to get the selected Client Document ID from the command arguments.
Dim btn As LinkButton = DirectCast(e.CommandSource, LinkButton)
Dim gvr As GridViewRow = DirectCast(btn.NamingContainer, GridViewRow)
' Get the selected document ID
Dim clientDocumentId As Integer = GridViewClientDocuments.DataKeys(gvr.RowIndex)("Client_Document_ID").ToString()
' Invoke the DocumentBLL.AddDocument method to get the file from the database.
Dim clientDocumentInfo As New ClientDocumentBLL()
Dim clientDocuments As cleartrak.cleartrak.Client_DocumentDataTable
Dim clientDocument As cleartrak.cleartrak.Client_DocumentRow

' Get a table with the appropriate row in it
clientDocuments = clientDocumentInfo.GetClientDocumentById(clientDocumentId)
clientDocument = clientDocuments(0)
' Set the response to the MIME type of the document and its binary contents.
Response.ContentType = clientDocument.MIME_Type.ToString
Response.BinaryWrite(clientDocument.Binary_Document)
End If

That's it - the files are now saved in the database and can be retrieved by a single click.

Dell Studio 17: Resuming itself from Hibernate

2009-12-18T11:29:00.005Z

I use my laptop for development, so I often have ten to fifteen programs open at once, each with various documents open within them. That, combined with the fact that development tools can often be heavy on CPU and memory means that restarting my PC is a pain in the neck.

So rather than restart or leave it on and waste power, I like to put it into Hibernate mode where it will quickly resume where I left off - no need to reload everything.

The problem was that if I left it overnight, I'd often come downstairs the next morning to find the Windows welcome screen staring back at me - the laptop had woken itself from Hibernate. This is not ideal, because it might cause it to overheat and its a monumental waste of power.

I could not figure out why this was happening so contacted Dell support to see what they could do. They were very keen to help, but didn't offer many useful suggestions. They suggested I flash the BIOS, which I did. No luck.

Then I devoted some time to googling the issue and discovered the problem.

The setting I had selected in Windows Update appeared to be the sensible option, for Windows to 'Automatically download and install updates for me'. Given the swiss cheese nature of Windows security, I thought this would afford me the best protection.

But it turns out that with this setting chosen, Windows Update was causing the system to wake from Hibernate to look for updates. How stupid is that? If I wanted it to do that, I would deliberately select such a setting if it was available. And surely if you did want it to resume to download updates, you would want it to automatically go back into Hibernate? But oh no.

There was no indication in the Windows Update settings that the system would resume from Hibernate

Anyway, I now have it set to automatically download updates, but instead of installing them automatically it will ask me to select which ones to install. At the cost of being able to hibernate my machine, I have to ensure that I remember to periodically install the available updates when they download.

Not the best, Microsoft, not the best.

Review: Dell Studio 17

2009-12-18T11:19:00.008Z

Being an I.T. boy, if you believe in stereotypes you might think I'd have loads of computers everywhere, whirring away, lights flashing. But since my PC blew up last year, I've only had my work laptop to use.

So the time came when I got sick of typing my password into the encryption program, then waiting five hours for it to boot up and then to get logged into the laptop, only to forget why I switched it on in the first place. Grudgingly at first, I decided that now was the time to get a new computer all of my own.

It took me ages to choose a laptop. I wanted a fairly high spec machine, since I'll be using it for software development as well as some image editing, and I didn't want to have to replace it after just a year. Plus, if I decide to buy a PC game or two I'd like it if it could run it (even in lower resolutions).

After hours poring over which machine to choose, it came down to a short list between the Sony Vaio VGN-AW11M/H, the HP HDX18 and the Dell Studio 17.

I was tempted by the Sony, but the Vaio brand comes at a premium and the reviews said it was too slow to be a proper desktop replacement machine (plus, it's just a big black slab of a thing). The HP machine was very nice to look at and very quick, but too expensive (a rip off, actually), and in the end I decided an 18 inch screen was a little too large anyway. So I spec'd up my machine at Dell:

CPU: Intel Core 2 Duo P8400 (2.26GHz, 1066MHz FSB, 3MB cache)
Display: 17in Widescreen WUXGA with Truelife - CCFL (1900x1200)
Memory: 4096MB (2x2048) 800MHz DDR2 Dual Channel
Storage: 500GB Serial ATA (5.400RPM) Dual Hard Drive (2x 250GB)
Graphics: 256 MB ATI Mobility RADEON HD 3650
Optical Drive: Slot loading Blu-ray Disc (DVD+/-RW + BD-ROM) Drive
Network: WiFi (802.11 a/b/g/n), Bluetooth, built in Vodafone mobile broadband
Other: Backlit keyboard, 2.0 megapixel webcam

I plumped for the spec above, and managed to get 16% off.

The spec is very similar to that of the HP HDX18, but cost me about £200 less. When it arrived on Friday, I was immediately impressed by it. To be honest, I wasn't expecting to like the appearance of the machine too much since Dell's previous offerings have been as dull as a day locked in a room with John Major. But it's nice. The very solid build quality, the nice plastics, the backlit keyboard, a glossy screen and a decent pattern on the lid come together to form a handsome machine.

The first thing I did was turn the dreaded User Account Control off, and delete all the crap which Dell preinstalled on the machine. And so far, I'm more than happy with the performance.

Most of the other Vista machines I've seen (none have been particularly high spec, to be fair) have shown signs of poor performance when loading up the windows sidebar, and some of the property screens on Control Panel, among other things. But this machine doesn't do that - it loads pretty much everything nice and quickly, fast enough to avoid an irritating wait for windows to render. One benchmark test I had concocted was to see how fast it could convert a 70*40cm GIMP XCF image I'd been working on from RGB to Grayscale. On my work machine (Dell D610, 1.8Ghz CPU, 2 GB RAM), it took about twenty seconds, but on the Studio 17 it took about half that.

I paid a bit more and got the upgraded screen (the 1900x1200 WUXGA with Truelife one), and I was not disappointed. It's incredible. Being an extremely high resolution panel, everything on the screen is small, so you can fit a lot on it. Some people hate that, but I like it since I have loads of windows open at once. When playing a video or looking at photos, it really comes into its own, and if you were considering a Studio 17, then I'd upgrade the screen every day of the week. If you don't like the small text of the high resolution screens, you can increase the DPI settings to make everything a bit bigger (but this kind of defeats the purpose of having the high res panel!).

The battery life is alright, but I didn't expect much from a machine of this size. I think I'm getting about an hour and a half from the battery, which is acceptable to me since it'll be plugged in most of the time anyway.

It's heavy though. Heavier than Michelle McManus leaving McDonald's on a JCB. So it's not really all that portable, but I think if you get a decent messenger bag style case, you'd be able to carry it about without any problems. The weight doesn't really bother me since I don't intend to travel around with my laptop too much, just when travelling sometimes and maybe the odd client visit.

My only gripe with it is that there's too much light leakage from underneath the keys, it gets a bit irritating when you're working on the laptop when its on a desk. But the backlit keyboard is still worth the extra cash.

So in summary, it's a fast, well built machine with a stunning screen and nice features. Fortunately, with my wallet being several hundred pounds lighter, I'd recommend it to others.

Debugging CSS in Internet Explorer: Firebug Lite

2009-12-07T12:15:00.003Z

Firefox is absolutely brilliant for checking your CSS styles and layout when you've installed Firebug.

It allows you to see where styles have come from in your CSS, and to switch them on or off, or to adjust them to see what changes you need to make to sit your site nicely.

But when trying to figure out why things look the way they do in the abysmal Internet Explorer, there isn't really an equivalent which is as good. You can try the IE Developer Toolbar which is ok, but like the browser it supports it's very clunky and pretty annoying.

I'd been waiting for a Firebug equivalent for IE for ages, and then I discovered that there is a version of Firebug itself which is designed for IE, Safari and Opera: Firebug Lite.

All you do is stick a reference to a little remote js in your page, load it up and you have most of the functions of the full, Firefox version of Firebug!

Nice.

ASP.NET: Validate email address input using a Regular Expression

2009-11-27T12:14:00.003Z

A nice way to validate an email address which has been entered, using a Regular Expression and absolutely no additional code:

Add a texbox for the input:
<asp:TextBox ID="TextBoxEmail" runat="server" Text='<%# Bind("Email") %>' Width="200px" MaxLength="255"></asp:TextBox>

Then, add a RegularExpressionValidator:
<asp:RegularExpressionValidator ID="regexpName" runat="server"
ErrorMessage="Enter a valid email address" ControlToValidate="TextBoxEmail" ValidationExpression="^([0-9a-zA-Z]([-\.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$" />

Job done - no code, no fuss. It's pretty robust, too.

ASP.NET: Setting the maximum number of characters in a multiline text box

2009-11-26T16:37:00.010Z

Bizarrely, ASP.NET includes an out of the box setting which allows you to set the maximum number of characters on an ordinary text box, but not on a multiline text box.

Now there may be a good reason for this, and if there is I would love to be enlightened.

There is no less need for a multiline textbox control to be properly validated than any other text box - perhaps it is even more important since the user might have spent ages typing something in, only to be rebuked for exceeding the maximum number of characters and to be punished by losing what they'd typed.

A friendly character on the ASP.NET forums offered this simple JavaScript solution:

<script language="javascript">

function Count(text,long)
{

   var maxlength = new Number(long); // Change number to your max length.

   if (text.value.length > maxlength){

      text.value = text.value.substring(0,maxlength);

      alert(" Only " + long + " chars");

   }

}
</script>

Then just add these to the multiline textbox's markup: onKeyUp="Count(this,200)" onChange="Count(this,200)".

ASP.NET: Adding Error Handling With Email Notifications

2009-11-25T10:23:00.012Z

If you're developing a new application, the temptation is often to get it completed and deployed in the shortest time possible. And sometimes that may come at the expense of proper error handling. So with the best will in the world, sometimes problems do occur in a Production database and the users see an error.

And with no error handling in your application, that can be a pretty poor experience for the user which can make you look a bit unprofessional and can potentially expose your application to security issues if your settings allow the user to see the Exception Details Yellow Screen of Death. So after I completed a deployment of a major release, I decided it was time to implement some error handling and to enable my application to automatically email me with details of any errors which occurred in Production.

One of the things think is brilliant about .NET is the documentation. It's terrific. There are tutorials on every subject, and error handling within ASP.NET is very well covered. So basically, the steps to implement are as follows:

1. Add a custom error page with the appropriate look and feel
Rather than me plagiarise the tutorials on the official ASP.NET web site, here's a link to a tutorial which explains the concepts involved and will get you up and running with a custom error page which, depending on the type of error, shows a nice message to your users, rather than the Yellow Screen of Death (YSOD):

2. Add a Global.asax file to enable application-wide error handling
If you followed the tutorial above, when an error occurs the user sees a nicely formatted error message which is in keeping with the rest of your application. But nothing is really done about the error. It's equally important that you are notified of the error, and that you can investigate it with a view to resolving it.

So the next step is to enable your ASP.NET application to catch any errors, application wide. This is done by adding a Global.asax file which has event handlers for various different application wide events, including trapping errors. The next tutorial from the ASP.NET website explains the concepts involved and helps you to get an error handler set up. It also shows you how to automatically notify you via email when an error occurs.

Remember, when pasting any code from the example into your project that you need to import System.Net.Mail at the top of your Global.asax page in order to identify the appropriate classes for sending mail. You also need to add the appropriate mail server settings to your web.config file (you might need to specify a port number and username and password):

<system.net>
<mailSettings>
<smtp>
<network host="mailhost.*****.net" />
</smtp>
</mailSettings>
</system.net>

So now if you've done things by the book, you should be set up to be automatically notified of the details of any errors which occur, and the users should see a nice friendly screen rather than the horrible YSOD.

Finally, I'm a big fan of using XML for configuration settings where possible, rather than hard-coding them into the application. So you can add some keys to your web.config file which contain the settings you want to use for your email notifications. You could add something like this to appSettings inside your web.config:


<add key="ErrorToAddress" value="support@example.com"/>
<add key="ErrorFromAddress" value="aspApp@ example.com"/>
<add key="ErrorEmailSubject" value=" aspApp: An error has been logged"/>

Then, in your Global.asax file, replace the hard coded values with a call to extract these settings from your web.config:

' Get the appropriate values from the web.config file
Dim ToAddress As String = ConfigurationManager.AppSettings("ErrorToAddress")
Dim FromAddress As String = ConfigurationManager.AppSettings("ErrorFromAddress")
Dim Subject As String = ConfigurationManager.AppSettings("ErrorEmailSubject")

This all worked for me, and now my application is more robust, the users don't see any horrible error messages and if any problems occur in Production I'll know about it pretty quickly.

Hopefully, this might help you to consolidate the necessary parts of what's required to implement some error handling within your application too. But of course, this is no replacement for using proper try-catch error handling where appropriate.

After Upgrading to .NET Framework 3.5: ASP.NET Reconfiguration and Reporting Services 403 error

2009-11-20T11:53:00.006Z

Due to the issues I had with the .NET Framework and SQL Server Integration Services, I had to roll back the version to 3.5 instead of 3.5 SP1.

When the upgrade from 3.0 to 3.5 was completed, I had to do a few things to get IIS working again (I was getting errors when attempting to browse to my ASP.NET web sites, and a 403 forbidden error when browsing to a Reporting Services page):

- Re-register ASP.NET (aspnet_regiis -i from the .NET Framework directory).
- Enable ASP.NET in the Web Service Extensions section of IIS Manager.
- Check that each application was set to use the correct version of ASP.NET
- Check that each application was still set to the correct Directory Security settings.

At this point, the ASP.NET web sites started working again but Reporting Services was still giving me a 403 forbidden error.

To get this resolved, I had to delete the ReportServer Virtual Directory in IIS, and then recreate it using the Reporting Services Configuration manager.

All working again!

I Lost My iPhone!

2009-11-20T10:50:00.002Z

Devastated.

I was at the Kasabian concert at the SECC last week (which was absolutely amazing). At the last song which of course was LSF, I got a text saying that the wife was waiting outside for me to give me a lift.

I took the phone out to text her back since otherwise she would have no idea how long she would be waiting, and someone knocked my arm hard. My much prized iPhone was launched into the blackness of the undulating crowd in front at what seemed like warp speed.

Immediately I went after it and created a space in the crowd with my flapping arms and panicked expressions. A few people around me noticed that I was looking for my phone and joined in the search, but it was never looking good.

The concert finished and after a few more minutes of scanning the floor for a stricken and stamped on iPhone, we were ushered out of the arena by the SECC staff. I reported it to the Control Room at the SECC, the police and O2.

Over the few days that followed, I realised how much I have grown to depend on my iPhone. Not only for the phone calls and text messages, but for the instant access to my email, Facebook and being able to find out the answer to just about any question I could think of just by firing up Safari. It's hugely sad, I know.

Thankfully, the phone is insured via my Lloyds TSB bank account. They have a deal with Lifestyle Services Group, and I've been using them to insure my phones for years. In the past when I had to make a claim, I'd just phone them up, tell them what happened, pay the excess and a new phone would be with me a couple of days later.

Not this time. What a palava.

Everything is now done via mail and fax, which seems like a step back to me. I had to prove that I had reported it to the Police, O2 and them within 48 hours (thankfully I had) and provide proof of purchase. So the claim form is now with them and I'm hoping that I will be receiving a nice new, shiny iPhone sometime soon. If not, I'm gonna be in trouble.

The lesson for me? Don't take it to concerts, sign up to MobileMe (if I had done this, I might've been able to find the phone via the GPS tracker if it wasn't broken) and keep backing everything up.

Though I do have to admit, I have quite enjoyed using the ancient backup phone which has the ability to make and receive calls, and to send and receive texts - and that's it. It's a simpler way to be, no personal data to worry about, no time spent on checking Facebook or emails, and no notion of entertainment on the go.

That said, give me an iPhone any day. Please, Lifestyle Services Group, give me an iPhone.

Existing SSIS Packages Generating New Errors (.NET Framework)

2009-11-20T09:42:00.006Z

I had been using an SSIS package to load in data from a remote database using the Sybase Adaptive Server Enterprise driver on my own machine and on two 64 bit servers.

The load was working fine, but then it stopped working suddenly complaining of error code 0x80131937:

* Error 0xc0209029: Data Flow Task: The "component "Source - Query" (1)" failed because error code 0x80131937 occurred, and the error row disposition on "output column "WhseCode" (12)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

But the weird thing was that the exact same DTSX package stopped working on my PC and on only one of the two servers - it kept working on one of them.

So I tried everything:

- Stopping the package from failing on error to see what rows would be loaded into the database - every row loaded but every column was set to NULL.
- Rolled Sybase driver back to ASE ODBC 12.5 - this actually worked, but I could not roll the drivers back on the server machines so I had to find another answer.
- I tried several different query formats, but it failed every time.

Then, I stumbled across this thread and the good advice on the .NET framework versions proved to be the answer.

On my local workstation machine and the server which stopped working, I had .NET 3.5 SP1 installed. But on the server which was still working, the .NET framework was 3.5 without SP1.

Basically, I removed all versions of the .NET Framework, reapplied .NET 2.0, 2.0 SP1, 3.0 and 3.5 in order and tested the package between each installation.

Now it's working and the data is loading in just as it should.

I can't believe that .NET 3.5 SP1 would just break ADO like this. I don't have a problem with compatibility issues between versions of .NET, but some explanation or notification would be useful.

Oh well, the things you learn.

The Horrors of Internet Explorer 6: Detecting a Visitor's Web Browser

2009-11-17T12:02:00.014Z

I tend to keep up with updates to browsers, so I left Internet Explorer 6 behind in a cloud of dust a long time ago. It's horrible, and craps all over CSS in a way which beggars belief.

So much so, that several online campaigns have been set up to try to get rid of Internet Explorer 6. After all, it is eight year old technology - and a LOT has changed since its release in August 2001.

So when I discovered that my own website looks absolutely horrible through IE6, I realised that I had to accommodate this somehow.

The market share of IE6 has been dropping steadily throughout 2009, and at the time of writing, accounts for roughly ten percent of browsers on the web.

Rather than try to accomodate such a terrible piece of browser technology, I have decided just to display a message to IE6 users telling them that the site doesn't work for their browser and that maybe it's time for an upgrade.

Now I realise that a lot of people would object to this, shouting claims of lost custom due to IE6 users just closing the site, and that not everyone can upgrade from IE6 (standardised corporate PC builds etc).

But I am willing to sacrifice that potential ten percent of visitors to save myself from a complete site redesign which would be out of date in a year when the last IE6 users finally upgrade to something a bit more robust. I also think that some of those ten percent would have another browser on their system, which they may just use to access sites which don't show up properly in IE6 (there must be a few).

Anyway, the point is that there is a really simple way to detect which version of IE is being used, and display custom messages to the user or to show/hide certain parts of a web page. This excellent blog post highlights how it can be done in detail, but basically detecting IE6 can be done using the following simple conditional comment (which is only parsed by Internet Explorer browsers):



Now, a custom message can be displayed and problematic sections of the site can be hidden for IE6 users.

Simples!

Firefox: This address uses a network port which is normally used for purposes other than Web browsing.

2009-11-09T10:33:00.005Z

When browsing to some sites using Firefox, I got the message:

"This address uses a network port which is normally used for purposes other than Web browsing. Firefox has cancelled the request for your protection"

When you see this message, Firefox completely blocks you from accessing the site you have requested. Since people often need to access non-standard ports for development purposes, this is stupid - the kind of 'nanny' behaviour you would expect from Microsoft, not a company who are normally very 'developer friendly'.

There is of course, a way around it:

1. Open Firefox
2. Type about:config in the address field
3. Right click anywhere on the screen
4. Click new > string
5. Enter preference name as: network.security.ports.banned.override
6. Enter string value as: 2049 (or whatever port number you want)

When you attempt to visit the site again, Firefox will allow it since you have added an exception for the selected port.

IIS, ASP.NET: Could not load file or assembly 'System.Core, Version=3.5.0.0, Culture=neutral' or one of its dependencies'

2009-10-23T16:28:00.005+01:00

If you receive this error when trying to access a newly deployed application on IIS:

"Could not load file or assembly 'System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified"

Then check that you have the .NET framework version 3.5 installed. This was the LAST thing I checked after about a million other things - I wish I had checked that first!

I'll know for the next time though!

IIS: "Page cannot be found" error when browsing aspx pages in Windows Server 2003 with IIS 6.0

2009-10-23T15:44:00.004+01:00

I was configuring a new application, and had re-registered the .NET framework.

I kept getting a page cannot be found message when I tried to browse aspx pages on Windows Server 2003 with IIS version 6.0 and .NET framework 3.5.

Apparently it's because in Windows 2003, all the webservice extensions are "Prohibited" by default to ensure security. These settings were restored to default when I re-registered the .NET framework.

To resolve this, I did the following:

1. Opened IIS Manager (from a command shell, type inetmgr and press enter).
2. Expanded the appropriate nodes in the IIS to locate the "Webservice Extensions" Node
3. There was a list of "prohibited" extensions in the right.
4. Clicked on ASP.NET and "allow" it

Worked! Well, I was now back to where I was before I started mucking about with .NET.

This article applies to Windows Server 2003, IIS 6.0 environment.

Microsoft Outlook: Attachment field does not appear in header

2009-10-23T09:42:00.004+01:00

Being a heavy user of Outlook 2007, I found it really annoying when writing an email and adding an attachment when sometimes it would appear in the body of the email, rather than where it should be; on the attachments bar at the header of the message:

I thought it was a bug so I kept restarting Outlook but it had no effect. When I created a new message and added an attachment, it would behave the way I wanted.

Then I figured out that it's something really straightforward. If the message is in Rich Text Format the attachments will appear in the body of the message (which looks messy), but if you change it back to HTML or Plain Text format they will go back to the attachment bar in the header - where they should be!

Simples!

Simple Auto Refresh of ASP.NET Page

2009-10-22T10:55:00.002+01:00

I have a 'log viewer' page in one of my projects which I need to refresh every ten seconds.

There are lots of ways to achieve this using JavaScript and nasty code behind bodge jobs but there's a much easier way that only requires one line of code.

<HEAD>
<title>Auto-Refresh</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">

<meta http-equiv="refresh" content="10">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>

Clearly, content="10" means the interval in seconds.

Postbacks Not Working in ASP.NET

2009-10-20T12:19:00.004+01:00

I was deploying an application to a Development server, and noticed that when I tested anything which caused a postback, the page would simply refresh and the postback would not work.

When accessing the site on the server itself using the localhost address, it worked fine.

Bizarre.

Then I checked if it was working in Firefox, and lo and behold - it was!

Even more bizarre.

But apparently, it's something to do with validators which are specific to IE. It detects the browser, then if it's IE it sends specific JavaScript down to cause validation of postbacks.

The solution? Copying the aspnet_client folder into the root of our IIS installation's Default Web Site and restarting IIS. Once I closed my IE session and accessed the site again, the postbacks started working.

SQL Server Agent: Error When Running a DTSX Package to load Excel

2009-10-16T10:30:00.014+01:00

When deploying a project onto a 64 bit Windows Server machine, I noticed an issue with scheduling SQL Server Agent to run a DTSX package to load an Excel file. The error is a bit like this:

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered"

I could run the package directly using SQL Server Management Studio without any problems, so it's obvious that the issue is being introduced by SQL Server Agent. After rooting around on the net looking for the answer, it seems that the agent is trying to run the package in 64 bit mode.

There are no 64 bit Excel drivers, so the package fails. For information on the impact that 64 bit architecture has on SSIS, refer to this blog post by Deniz Arkan, the SQL Server Integration Services Program Manager at Microsoft, and this Microsoft article on the subject.

Many forum posts suggest that the package should be run via the Agent using the 32 bit version of DTEXEC rather than the 64 bit version.

By default, a 64-bit computer that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt. The 32-bit version runs because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. (Typically, the 32-bit directory path is :\Program Files(x86)\Microsoft SQL Server\90\DTS\Binn, while the 64-bit directory path is :\Program Files\Microsoft SQL Server\90\DTS\Binn.)

So if you have both 32 bit and 64 bit versions installed, you have to explicitly point to the 32 bit version of DTEXEC on the command line.

Alternatively, to run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box. This depends on your having the 32 bit runtime tools installed.

For a step by step description of how to run a package from an Agent job in 32 bit, refer to this Microsoft KB article.

But after all this reading, I bottled it. I can't be bothered having to get the 32 bit version of the runtime installed on the Dev, UAT and Production boxes so I decided to get the files I needed converted into a flat file format. This won't suit everyone as not everybody can get their input files changed to a different format, but for me it's the best route.

Anyway, clearly I am by no means an expert on this subject, but I reckon that Microsoft are trying to discourage people from using 32 bit architecture on 64 bit machines by making this kind of thing difficult to implement, and by not providing 64 bit drivers for their Office products.

Locating Duplicates in Excel

2009-10-08T11:50:00.002+01:00

If you work with large Excel files regularly, it can be a pain in the neck to find duplicates in a column of data.

You could export the file to a text file and load it into a database, and use a query to find the duplicates - but that's probably the sledgehammer approach.

The problem is that Excel doesn't appear to offer a straightforward way of identifying duplicates, but it can be done using Conditional Formatting.

Rather than reinvent the wheel and write a detailed how-to here, it's a good idea to link to the definitive authority on the subject: Microsoft. So here's a link to an article which shows you how to locate duplicates in a matter of seconds. Hope it's useful.

ASP.NET: "It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level"

2009-10-07T11:05:00.002+01:00

You may get this error when trying to browse an asp.net application.

The debug information shows that "This error can be caused by a virtual directory not being configured as an application in IIS."

When you create an new web application using visual studio.net, it automatically creates the virtual directory and configures it as an application. However, if you manually create the virtual directory and it is not configured as an application, then you will not be able to browse the application and may get the above error. The debug information you get as mentioned above, is applicable to this scenario.

To resolve it, Right Click on the virtual directory - select properties and then click on "Create" next to the "Application" Label and the textbox. It will automatically create the "application" using the virtual directory's name. Now the application can be accessed.

This solution 'borrowed' from this blog.

IIS 5.1: "MMC has detected an error in a snap-in"

2009-10-07T11:02:00.004+01:00

When using IIS Manager on a Windows Server Enterprise Edition box, the error message "MMC has detected an error in a snap-in. It is recommended that you shut down and restart MMC." sometimes appears.

Googled around for a bit, and this seems like a bug that Microsoft know about, but don't care about. Odd! Suppose that's nothing new though:

http://support.microsoft.com/kb/915519

Visual Web Developer 2008: Failed to load dataset because of the following error: Unable to find connection

2009-09-03T16:03:00.011+01:00

Somehow, I ended up with two connection strings in my ASP.NET project.

I don't know where the second one came from (I suppose it could've been user error), but VSWD called it connectionString1 and without my permission or instruction, modified a whole load of other files in my project to suit. So I just took the sledgehammer approach and removed the second connection string and did a project-wide find and replace on all occurrences of connectionString1.

I thought I was ok until I got to the point where I needed to make some changes to the DAL of the application, and the following error appeared:

The error message isn't exactly self-explanatory and VSWD wasn't exactly helpful.

So I started panicking, sweat coming from my brow at the prospect of having to spend hours at figuring out the source of this problem, feeling stupid because I felt that I had caused this problem by messing about with settings I didn't understand.

I could open the DAL .xsd file in Source mode, but could not open it in Design mode. Hmm... seems to be some issue with all of the stuff VSWD does in the background to render the visual portrayal of your DAL...

Anyway, lo and behold the answer came nice and easily. In my DAL's .xsd file, I had two references to exactly the same connection string. So when trying to load it up it got confused, spat the dummy and didn't bother telling me what the problem was.

So if you see this problem in your project, check to make sure that

1) You don't have multiple connection strings in your project where you don't explicitly need them.
2) You don't have duplicate references to your connection strings in your DAL's .xsd file.

I think that error can be caused by other things, but check those two things first and hopefully it'll help.

ASP.NET: Server Application Unavailable

2009-08-31T10:16:00.005+01:00

If you get this error when you try to access your ASP.NET application:

... it could be caused by a huge number of things.

It happened to me, and to get more information I went to the Event Viewer where some errors had showed up. Specifically these two errors:

and:

This page is quite useful in explaining the potential problems and gives a few ideas for resolution. I went through a lot of them but the answer ended up being very simple.

The ASPNET user account somehow became locked out, so all I had to do was go into Control Panel > Administrative Tools > Computer Management > Local Users and Groups and look at the properties for the ASPNET account.

The account was marked as locked out, so I uncleared the checkbox and restarted IIS with an iisreset at the command line. Then I tried to access my application and it worked!

It was so straightforward, but I still have no idea why the account became locked out in the first place!

Apple Tablet: Will Steve's New Toy do the Job?

2009-08-27T16:56:00.003+01:00

Many people apparently believe that Apple are about to announce a new gadget, the Apple Tablet.

Since it's such a work of art, I always thought it would be cool to take an iPod Touch and enlarge it by several times. It looks a bit like the Tablet (if it actually appears) will look just like that.
I really hope that they do release such a gadget, because aside from an iPhone I've never owned a Mac. It looks like it could be the perfect first buy; possibly portable, durable and running Mac OS X, with a user experience that beats the hell out of Windows (not that difficult).

Of course, it's going to be expensive and demand will undoubtedly reach fever pitch,
so I better start saving!

Posting Code in Blog Posts

2009-08-13T10:33:00.003+01:00

It's a nightmare trying to post any code in Blog posts. The tags confuse Blogger (fair enough) so if it happens, it won't let you post the code.

I tried to install SyntaxHiglighter from Google, but it requires you to host its code on a webserver somewhere and I couldn't get it to work.

Stanley Shilov came up with a nice alternative online tool which you can paste your code into, and it will parse it and replace any characters which will confuse your blogging engine with HTML tags which it'll love. You can access the tool here, and it needs zero set up and zero hassle.

The drawback is that it doesn't offer any syntax highlighting, but it's still useful and I'll certainly be using it in future!

ASP.NET: Adding a simple Ajax Web Service with AutoCompleteExtender in Visual Studio 2008 Express

2009-08-13T09:59:00.006+01:00

I'd been really struggling to get any kind of Ajax functionality in my ASP.NET project. The first problem was that I couldn't be sure if I had enabled the Ajax extensions in my project, so to verify it I decided to try to create a new 'ASP.NET Ajax Enabled Web Site' template in Visual Studio.

But I couldn't see that option, and wondered if I had the wrong version of the .NET framework installed. No - that was fine, I had 3.5. So it must be something else. Until I discovered that Visual Studio 2008 Express automatically adds the Ajax extensions to any new ASP.NET project you create in it. So that's good.

But when I went to add an AutoCompleteExtender to my ASPX page, I couldn't see the control in my Toolbox. This was odd. But EVENTUALLY I discovered that I was missing the AjaxControlToolkit.dll from my project's Bin folder. This comes as part of the Ajax Control Toolkit, which you can download from CodePlex:

So I downloaded the Binary package (to avoid all the extra crap that comes with it) and dropped the AjaxControlToolkit.dll into the Bin folder of my Project. Then I right-clicked on the Toolbox in VS2008 and selected Choose Items. Selecting the DLL file now meant that after a couple of seconds of chewing it over, the Ajax controls I needed appeared in the Toolbox.

Now I had to see if I could get an extremely simple Ajax control set up on my page, just to see if it was working. So I dragged a new texbox onto my page, gave it a name and then dragged a ScriptManager control onto the page. Next, I had to add an AutoCompleteExtender control which would target the text box I chose, and Ajax enable it.

<cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1" ServiceMethod="HelloWorld" ServicePath="WebService.asmx" TargetControlID="TextBoxAjax"> </cc1:AutoCompleteExtender>

So now I have an Ajax enabled control on my page, and finally I needed to set up a web service to feed it something. So I created a new web service called WebService.asmx and added the following code to it:

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class WebService
Inherits System.Web.Services.WebService

<WebMethod()> _
Public Function HelloWorld(ByVal testStr As String) As String()
Dim returnString(2) As String

returnString(0) = "one"
returnString(1) = "two"
returnString(2) = "three"

Return returnString
End Function

End Class

It creates a WebService.asmx.vb file too, which actually contains the code above. So all the HelloWorld function does is return a string array to the Ajax textbox.

I built and started the app, and voila - when I type something into the box, the values 'One','Two','Three' appear below. It's not smart at all at this point, since the web service just returns those values no matter what is typed into the text box. But it's square one, and it's a good place to start.

Next step, add some proper code to the Web Service which returns the correct autocomplete values based on what's typed.

Solution: IIS - You Are Not Authorised To View This Page

2009-08-04T11:35:00.002+01:00

If you're setting up a new site on IIS, and occasionally (usually after you lock your workstation and log back in, or if you leave it for a long period of time) when you attempt to view a site on your localhost or on another remote machine using IIS - you might see the page displayed which says:

"You are not authorized to view this page (Error 401.1)"

If you're allowing anonymous access to your site using the IIS account, a temporary workaround is to run an iisreset at the command line. But a longer term (and less irritating) solution is to go into the Directory Security properties of your Virtual Directory in IIS configuration and uncheck the box which says "Allow IIS to control password".

Then do an iisreset and the error shouldn't appear again, unless there are deeper problems with your security set up.

Solution: Blank Report Manager (SQL Server 2005 Reporting Services)

2009-07-30T10:59:00.002+01:00

If, after installing Reporting Services you see a blank screen when trying to open Report Manager, you can try the below methods to get you moving.

If you have an Administrator account on the local machine:
· Enable Integrated Windows Authentication in the Reports (and ReportServer) virtual directories in IIS, and disable Anonymous Access.
· Restart IIS (iisreset on command line)
· Try to open Report Manager. If your account is recognised as an Administrator, you should see more content in Report Manager.

If you still can't see full content in the Report Manager, then you can try this:
· Add the local anonymous IIS account (IUSR_MACHINENAME) to the Administrators group for the local machine (this is just temporary, read on).
· Restart IIS (iisreset on command line)
· Try to open Report Manager - you should now see full content.
· Go to the Properties tab, and click New Role Assignment. Grant the IUSR_MACHINENAME account the appropriate privileges to access the Report Manager (be careful not to allow too much access since this means your users could change/delete reports).
· Remove the IIS account from the Administrators group for the local machine.
· Enable Anonymous Access for the Reporting Services virtual directories in IIS, and disable Integrated Windows Authentication.
· Restart IIS
· You should now be able to see the appropriate content within Report Manager.

Hope this helps! It worked for me, and I had been going mad with frustration

SQL Server 2005 Installation: Issues and Resolution (Errors 29559 and 29515)

2009-07-29T12:33:00.003+01:00

I was attempting to install SQL Server 2005 Developer Edition with the following components, and experienced some issues:

· Database Services
· Analysis Services
· Reporting Services
· Integration Services

I thought it might be useful to share my findings in case a Google search brings up these errors and the resolutions I found, since I eventually managed to get everything installed.

Some people might have opposing views on the steps I took, but it works now and that's the main thing.

SQL Server 2005 Error 29559
Full Error Text (From Windows Event Viewer): "Product: Microsoft SQL Server 2005 -- Error 29559. SQL Server Setup failed to modify security permissions on service MSSQLServerADHelper for user V024755. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive."
Background: When attempting to install SQL Server, the following error appeared near the end of the installation when it was attempting to configure Database Services. Was connected to the installation machine over Remote Desktop (mstsc). Had initiall selected to install all of the desired SQL Server 2005 components, with all except database services installing succcessfully in this installation.
Workaround: Logged onto machine directly rather than over mstsc. Checked that domain account was a member of the Adminstrators group. Removed all traces of SQL Server 2000 files from C:\Program Files\Microsoft SQL Server\80. Modified file permissions on all files and folders under that location to allow full control to the appropriate domain account.
Result: The above error did not reappear, the installation proceeded past this point though another error was encountered.
SQL Server 2005 Error 29515
Full Error Text (From Windows Event Viewer): "Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online."
Background: After setup had proceeded past the point of error 29559 above, the installer was attempting to start the database engine service when the above error occurred. The suggestion was that the problem was caused by the fact that some SQL Server 2005 components (Integration Services, Visual Studio 2005 IDE etc) had installed successfully in the very first installation, so there was some kind of conflict caused.
Workaround: Removed all traces of all SQL Server (2000 and 2005) installations to begin from a 'clean slate'.
NOTE: Everything except Microsoft SQL Server Setup Support Files - you need this to complete 2005 uninstallation. If this does not appear in Add/Remove Programs, you can reinstall it by running SqlSupport.msi, located in the installation folders.
- Removed everything related to SQL Server which was visible in Add/Remove Programs dialog.
- Followed uninstall steps for SQL Server 2000 from Microsoft website.
- Followed uninstall steps for SQL Server 2005 from Microsoft website.
- Deleted C:\Program Files\Microsoft SQL Server and all its contents (You may experience file lock issues when attempting to delete, in this case you can use Process Explorer to identify which process has a lock on the file and kill it, at your own risk!).- Restarted machine
- Restarted installation, to install Database Services only. This was done with the goal of minimising risk by introducing installation of other components.
Result: Installation was successful of Database Services.
- Started installation of remaining components (Analysis Services, Reporting Services, Integration Services), without Database Services.
Result: Installation of remaining components was successful.

Conclusion
· Remove all instances of SQL Server 2000 before you do any installation of SQL Server 2005 whatsoever, do not attempt to upgrade if at all possible.
· Ensure you have administrator privileges on the machine where SQL Server is being installed.
· Install Database Services first, before any other components.
· Once installation of Database Services is successful, then install remaining components.

Windows Remote Desktop (mstsc) can't connect: quits unexpectedly

2009-07-07T11:51:00.006+01:00

I'm in London and I've been trying to establish a remote connection to another machine which I use for development.

I needed to access it so that I could work remotely while away, but when connecting using Remote Desktop (mstsc on the command line) I could see the log in dialog and enter my details, but as soon as I pressed 'OK' it just quit. No error messages, nothing!

I tried using the IP address, and the fully qualified hostname but no cigar. What drove me even more mad was that I previously tested that I could connect to the machine when the laptop and desktop were physically right next to each other.

But as soon as I moved away from the same location, I found I was unable to connect.

I thought it was something to do with trying to connect to it from another IP subnet, but I managed to connect remotely to a third machine which was on the same subnet, and from there I still could not connect to the target machine.

I thought it was odd that I could connect to one machine, but not the other. So I did a search of the Microsoft support website, and it turns out that the problem is a dodgy NVIDIA display driver! Apparently it doesn't like people connecting remotely, so it just decides to kill the connection with no error and no warning.

See this support article: http://support.microsoft.com/kb/886212/en-us which suggests rolling back your NVIDIA driver.

But in the short term, if you have an NVIDIA graphics card and if this problem has been driving you mad and you are unable to roll the driver back, hopefully this will help. I think the problem is due to the multiple monitor functionality that comes with the NVIDIA driver software. So if you have that enabled, then this might help.

My resolution was:

- Get the machine restarted
- It *should* let you log in since there is no session to restore
- Disable the nView functionality

For me, that meant Bob was my Uncle. But it remains to be seen if I can disconnect and then reconnect to my session on the remote PC.

SQL Server Job Error: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

2009-06-26T11:53:00.006+01:00

If you deploy some DTSX packages to SQL Server, and create an Agent job to run them, you might see this error:

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

The workaround is probably simple. When deploying to your SQL Server and you're on the Specify Target SQL Server page of the wizard, select the 'Rely on server storage for encryption':

That should probably do it. If not, check out this thread on MSDN.

SQL Server Integration Services: Setting Input Filenames Dynamically from a SQL Server Table

2009-06-25T10:50:00.004+01:00

Being a recent convert from SQL Server 2000, I was used to the DTS framework for importing files to the database. From recent blog posts, you will see that it's been a learning curve in getting completely up to speed with SQL Server 2005's Integration Services.

When loading files into a database, I obviously found it important to dynamically set the file path so that values were not hard coded, otherwise it became a maintenance nightmare. Especially when promoting the packages to different environments.

I decided that for my application, the best way to dynamically set these properties was by creating a database table with the details on each file which was to be imported. Then I would extract the filename into the DTSX at runtime and use that to import the file. This way, the filenames would be picked up according to whatever environment the DTSX is running in, and there would be no hard-coded filenames to deal with.

So I thought I'd share a quick description of how I went about it, as much as a reminder for myself in future as for others who might stumble across this blog.

The table which contains the import file details is set up as follows:

CREATE TABLE [Import_File_Specifications](
[ImportFile] [nvarchar] (100) NOT NULL ,
[FileName] [nvarchar] (255) NULL ,
[FileExtension] [nvarchar] (30) NULL ,
[Location] [nvarchar] (255) NULL ,
CONSTRAINT [PK_Import_File_Specifications] PRIMARY KEY CLUSTERED ( [ImportFile] )
ON [PRIMARY] )
ON [PRIMARY]
GO

So I loaded the file specifications into there, and at runtime the DTSX would run some SQL against the table to build the filename based on the folder location, filename and file extension.

All I did was (this is assuming you're importing a flat file - tab delimited, csv etc):

1. Create a Package Variable to contain the filename of type String (right click on the Control Flow canvas and click 'Variables') .

2. Create an Execute SQL Task at the beginning of the DTSX package which flows into the rest of the process with an 'on success' connector.

3. On the General tab of the SQLStatement setting in the SQL Task, I put:

select Location + FileName + FileExtension as FileName from Import_File_Specifications where ImportFile = 'xxxx'"

'xxxx' is the unique ImportFile key for the file you want to use). Set ResultSet equal to 'Single Row' and SQLSourceType to 'Direct Input'.

4. The next step is to set the Package Variable equal to the filename which comes from the SQL Task. So on the Result Set of the SQL Task, press Add and select Result Name to equal FileName (from the SQL Statement you just entered), and select the Package Variable you created at the beginning.

5. Now, when the DTSX runs the SQL Task, it should set the Package Variable to equal the filename which comes from the database. The final step is to set the ConnectionString of the file's connection manager equal to the Package Variable which contains the filename. Select the connection manager, and on its properties which appear on the right hand side, click the '...' button which appears when you click the 'Expressions' field.

6. When the Property Expressions Editor appears, Press the '...' button to add a new expression property and select 'ConnectionString' from the drop down on the left. Then choose your package variable from the drop down on the right hand side.

That's it! When you test your DTSX package, if you've set up everything correctly then the SQL Task should retrieve the filename from the database, set the Package Variable equal to it and pass that into the Connection Manager.

For an Excel file, you set the 'ExcelFilePath' at runtime rather than the 'ConnectionString' property because the ConnectionString tells the package to use an OLE connection to the file. Note that you must set your PackageVariable to have a default value otherwise the package will not compile.

I admit that this post could have done with more detail, and probably some screen shots but as I said, it's just really a note to remind me of this technique in future. Others have also found different ways of doing this which they might argue are better, but the way I have this set up it works for my application and will allow for different input filenames depending on whether the app is running in DEV, UAT or Production.

Tech Review: Apple iPhone 3GS

2009-06-25T10:06:00.004+01:00

It had been a long time since I possessed what could be called a 'good phone'. This is because I always found myself willing to sacrifice having a decent handset for the sake of a free xbox 360, PS3 or Wii with my phone contract.

I finally got out of contract with Orange, and was dying to get a new phone with unlimited net access because it was becoming more important that I have instant access to email on the move. The only two competitors were the Palm Pre...

...and the new iPhone.
Both have a touch screen, both have unlimited data access, both can be used as an iPod (the Pre tricks iTunes into thinking it's an iPod), both have GPS and Wi-Fi and just about everything else you can imagine (see here for a full side-by-side comparison).

But initially, when comparing the two online I couldn't help but be more impressed with the Pre. It looks very sleek, and as if a lot of thought has gone into designing both the hardware and the WebOS operating system on it. It has brilliant multitasking capabilities, lots of ways to intelligently organise your data and do cool stuff with GPS and social networking sites.

That, in addition to the fact that I've never liked the look of the iPhone, meant that for the past couple of months the Pre had been at the top of my wish list.

At the recent WWDC conference in San Fransisco Apple announced the iPhone 3GS, which is basically a pimped out version of the iPhone 3G handset, with a few new features and a faster user experience. It appealed, but still sat second in my wish list when compared with the Pre.

Palm have released the Pre in the US already, but for some reason they have not released it in the UK. I think this is because they haven't completed the compatibility with our slightly different GSM networks.

In the incredibly competitive smartphone market where the Pre is undoubtedly Palm's 'do or die' handset, this is an unbelievable planning error.

There must be literally thousands of people like me, who wanted a new phone and couldn't decide between the Pre and the iPhone. These people would like to see what the Pre can do, but are not willing to hang around for months until Palm get their act together and set a release date. So I lost patience, and bought a new iPhone 3GS last week.

It was disappointing not to have the choice between the two, but if Palm want to compete with Apple, they needed to play the game.

And Palm's move should have been to release the Pre in the UK before the iPhone 3GS, or at least a matter of days or weeks after. Now of course I'm not saying that Palm will fail because I personally did not buy a Pre, but surely it's damaging to their prospective Pre sales that since launch, Apple have sold 2 million iPhone 3GS in the European market alone.

Maybe they underestimated the importance of the European consumer electronics market. But they shouldn't, have because it could have made the difference between the Pre becoming their saviour, or the iPhone becoming their downfall.

The point of this post was really to review the iPhone. And I must say, I absolutely love it. It's a complete pleasure to use, and the hype is correct - the App Store is the killer. So many cool little applications to download, and a large portion of them are completely free. It's not perfect though, the email client is a little overly simplistic and the battery life is horrible. But I don't regret for one second buying the iPhone, and by the time the Pre finally hits the UK shores I will be content that I shouldn't have hung about for months with my old Nokia 6300 and chunky Orange contract in tow.

Implementing Error Handling in SSIS

2009-06-23T11:34:00.000+01:00

I've been trying to get to grips with SQL Server Integration Services, as opposed to the old DTS technology in SQL Server 2000. It's a task.

One of the things I've found difficult is to implement error handling, and after poring over much rubbish on the net, I found two useful blogs on the subject. Both of them have reusable code which is easily added to existing DTSX packages.

Step one is to read this blog by Jamie Thomson at SSIS Junkie.

Then, this additional blog adds a lot more detail and allows you to store errors in a database table which is very useful if your application has a lot of flat file data inputs (such as a reconciliation).

Now I just need to add error handling which will detect if the file is not there at all, and log that too!

SQL Server Reporting Services and IIS: Failed to access IIS metabase - Guaranteed Fix

2009-06-23T10:47:00.001+01:00

I spent ages trying to get SQL Server 2005 Reporting Services working, and kept getting the error "Failed to access IIS metabase" from IIS.

After spending ages trawling through documentation, Microsoft KB articles, messing about with security settings and adding Windows and domain users to the IIS metabase, I found the answer in this post which worked perfectly and took less than a minute. If you're having a similar problem, this will solve it:

My MCP: Failed to access IIS metabase.

To summarise his post, you run this command:

%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

What's the lesson? Ignore Microsoft and listen to the blogosphere.

Also: if you're just getting started with Reporting Services, this installation tutorial is the business, and this one is superb for showing you how to build and deploy reports.