Saturday, 18 December 2010

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

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

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

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

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

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

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

Wednesday, 29 September 2010

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

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:

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:

width: 14px;
height: 14px;
background-image: url('../images/writing.gif');
margin-left: 465px;
position: absolute;
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.

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.

Friday, 24 September 2010

ASP.NET: Limiting the length of a multiline textbox

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):


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

Thursday, 16 September 2010

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

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.

.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.

Friday, 3 September 2010

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

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.

Tuesday, 31 August 2010

Solved: Lenovo ThinkPad W510: High pitched squealing noise

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.

Monday, 30 August 2010

Rounded Corners

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

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.

Monday, 23 August 2010

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

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:


grdvSummaryGrid.DataSourceID = String.Empty

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

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)

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

Saturday, 21 August 2010

ASP.NET: Sorting a DataTable

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"


Here's the MSDN documentation.

Wednesday, 18 August 2010

CSS: RadioButtonList Layout

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-collapse:separate ;
.radioButtons input {
.radioButtons label {
margin-left: 25px;
display: block;
.radioButtons td{

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" >

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.

Saturday, 26 June 2010

ASP.NET: Inserting NULL Values Into SQL Server Database

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.

Monday, 21 June 2010

ASP.NET FileUpload Control Inside an UpdatePanel

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.

Monday, 14 June 2010

Syntax Highlighter Success

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!

COUNT(dbid) as TotalConnections
dbid > 0


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">

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

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

DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
dbid > 0
dbid, loginame

Total Connections

COUNT(dbid) as TotalConnections
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.

Wednesday, 9 June 2010

ASP.NET: ListView Control and Nested ListViews

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.

Monday, 7 June 2010

ASP.NET: Select DISTINCT from DataTable

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!

Friday, 4 June 2010

ASP.NET: Handing an empty Repeater control

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.



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

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()


End If

Thursday, 3 June 2010

ASP.NET: Render Control into HTML String

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.


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

return sb.ToString();


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)


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 by Scott Mitchell:

Part 1

Part 2:

Friday, 30 April 2010

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

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.

Monday, 12 April 2010

ASP.NET: Reset Selected Value of AJAX Combo Box

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

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

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.

Friday, 9 April 2010

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

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.

Monday, 22 March 2010

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

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
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.

Tuesday, 16 March 2010

ASP.NET Code Behind Intellisense Not Working

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.

Friday, 5 March 2010

Styling GridView Controls with CSS

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.

Tuesday, 2 March 2010

CSS min-height and IE6

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.

Wednesday, 27 January 2010

SQL Server 2008 Express Edition: Installing on a Domain Controller

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

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):


<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.

Tuesday, 19 January 2010

ASP.NET: Maintain Browser Scroll Position After Postback

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" >


Friday, 15 January 2010

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

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.

Thursday, 7 January 2010

ASP.NET: Search GridView and Programatically Select Row

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

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

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


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.