.Net ramblings
# Wednesday, 23 March 2005
SQL left join with a where clause produces strange results

I was doing a straight forward left join to include all the records of a table, regardless of whether they had records in the related table.  However, i added a where clause, which produced very strange results.  Some of the foreign records were not included in the results, specifically those that had no related records at all in the main table.  This was very frustrating to pin down, but i finally found out that the where clause in a left or right join should not use the 'where' syntax.

instead, it should use:

    (IIf([ProductID] Is Null,'False','True')) AS GroupIncluded
    SuperMarketGroups LEFT JOIN 
    ProductsInGroups ON
        (SuperMarketGroups.ID = ProductsInGroups.GroupID 
            AND (ProductsInGroups.ProductID Is Null OR ProductsInGroups.ProductID=?)

note that there is no 'where' clause.  the extra condition is specified with the join columns. this solved my problem perfectly.

Wednesday, 23 March 2005 16:54:28 (GMT Standard Time, UTC+00:00)  #    Comments [4]  Database

Howto: Export a dataset to Excel (c# / asp.net)

In my web applications, i occassionaly need to allow the user to export a dataset as an excel file.  I was using a control written by Prashant Nayak posted on Code Project but he released a new version which was problematic for me, so i looked at other solutions.

obinna igbokwe  from  www.dedicatedsolutions.co.uk posted a good approach which creates a DataGrid object and binds it to the dataset, and then Renders the output of the control to the HttpResponse stream.  This works very well.  I have adapted his code to C# and added an option to specify a filename for the excel file.

Here is the code:

using System;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Whatever
 /// This class provides a method to write a dataset to the HttpResponse as
 /// an excel file. 
 public class ExcelExport
  public static void ExportDataSetToExcel(DataSet ds, string filename)
   HttpResponse response = HttpContext.Current.Response;
   // first let's clean up the response.object
   response.Charset = "";
   // set the response mime type for excel
   response.ContentType = "application/vnd.ms-excel";
   response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
   // create a string writer
   using (StringWriter sw = new StringWriter())
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
     // instantiate a datagrid
     DataGrid dg = new DataGrid();
     dg.DataSource = ds.Tables[0];

Wednesday, 23 March 2005 16:47:02 (GMT Standard Time, UTC+00:00)  #    Comments [72]  Asp.Net | Database

# Tuesday, 15 March 2005
HowTo: get a random letter in C#
public static char GetRandomLowerCaseCharacter(int seed)
   return ((char) ( (short) 'a' + new Random(seed).Next(26)));

public static char GetRandomUpperCaseCharacter(int seed)
   return ((char) ( (short) 'A' + new Random(seed).Next(26)));

If you call the above methods straight after each other with the same seed, you may get the same value on a fast processor.  It is a good reason to pass in a different seed value for operations that will be done in quick succession.  E.g. use DateTime.Now.Seconds for one operation and then use Minutes or Hour or Milliseconds for the next ones.

Tuesday, 15 March 2005 18:26:27 (GMT Standard Time, UTC+00:00)  #    Comments [2]  .Net General

# Wednesday, 09 March 2005
FIX: Crystal Reports error "Logon Failed" happens when viewer control is not in form server tag.

I just spent hours trawling the web trying to find out why my web forms app was giving me the following error:

  • CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed

I am using a Dataset so there is obviously no logging on necessary for a disconnected data source.  By chance, i noticed that there was no server form around the crystal report viewer control, so i put one in, and it worked.  That has got to be the most annoying error message i have ever come across.

Wednesday, 09 March 2005 16:03:21 (GMT Standard Time, UTC+00:00)  #    Comments [2]  Asp.Net

# Tuesday, 01 March 2005
Regex for a strong password

This came in useful for ensuring that web site users have entered a strong password.  Thanks to Eli Robillard for posting this on his blog.

In this case, a strong password is defined as follows:

  • between 5 and 128 characters long
  • contains at least one digit
  • contains at least one upper case letter
  • contains at least one lower case letter.

Here is the pattern:


C# code using this pattern:

public bool IsStrongPassword(string s)
    string pattern = @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{5,128}$";
    return Regex.IsMatch(s, pattern, RegexOptions.IgnorePatternWhitespace);

Tuesday, 01 March 2005 18:46:28 (GMT Standard Time, UTC+00:00)  #    Comments [0]  .Net General

# Friday, 18 February 2005
FIX: ISAPI Filter won't load on production server IIS

i have an ISAPI filter that i use for an Asp.Net web site.  It worked great on my development PC, but when i went to install it on the production server, it wouldn't load, and there was an event log saying "The HTTP Filter DLL whatever.dll failed to load. The data is the error".

After hunting around the newsgroups, i found out that the production server didn't have version 7.1 of the MFC dlls, as would be the case on a system with VS 2003 installed, hence the reason it wouldn't work.  so i downloaded MFC71.dll and MSVCR71.dll and put them in c:\windows\system32 and registered them with regsvr32.exe, ignoring the warnings about entry points not found.  that fixed it.

To remove this dependency in the DLL altogether, i went back in to VS2003 and in the project options, i changed 'Use of MFC' to a static library, which has resulted in an increased binary size (still only 150k) which i guess means it has included all the MFC stuff in the DLL itself.

Friday, 18 February 2005 18:07:09 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Asp.Net | Windows Server

# Thursday, 03 February 2005
An ISAPI Filter to have nice URLs for your Asp.Net site


My content management system stores all the pages in a sql database, so if you go to /home.html, there is no document by that name on the server.  Instead, i have one .aspx page called content.aspx that handles every request dynamically and it outputs the content for the page.

You can partially achieve this same effect without using an ISAPI filter (see http://www.codeproject.com/aspnet/URLRewriter.asp) but you don't get the advantage of specifying a folder path as a valid url.  For example, if you request /Departments/HR/ then you will get a 404 on the server, because IIS doesn't know to pass on this request to my /content.aspx page.  Jakob Nielsen (the usability expert) says that hackable urls are important, i.e. users should be allowed to remove parts of the path of the page they are at, and arrive at a level higher up in the site.  The ISAPI filter used here allows this.

Requirements for the Filter

  • Every .html request should be forwarded to /content.aspx
  • Every default document request should be forwarded to /content.aspx
  • A request with a querystring (?) should not be redirected because it is assumed to be a dynamic page.
  • The IIS log should record the original url (i.e. /Home.html) instead of /content.aspx?Url=/home.html

Because the code is in C++ and there is a lot of messy error checking stuff going on all over the place, i won't post it here (mess up my blog). instead you can download the VS 2003 solution (20 k) with source files + executable, and examine it for yourself if you're interested.  It does work, and i have about 15 sites runnning off this filter for a long time now. 


This solution is 95% taken from David Wang's excellent post on the ISAPI-DEV ms newsgroup. I just added in support for default documents and ignoring dynamic page requests.

Thursday, 03 February 2005 12:14:02 (GMT Standard Time, UTC+00:00)  #    Comments [2]  Asp.Net

# Saturday, 22 January 2005
Howto: Disable windows automatically adding network printers and faxes

i am configuring my new web server, and i noticed that the event log keeps getting 5 or 6 system warning events about printers getting added or deleted. since i pay close attention to my log, and have no print requirements on the web server, those print logs are clutter. i noticed that windows kept adding in printers that are available on my network, even after i deleted them. after hunting around online, i found that microsoft humbly admit that the feature to automatically add network printers may be undesirable, which it is in my case. at least they let you turn it off.

Open windows explorer, Tools > Folder Options > View > Advanced Settings > turn off "Automatically search for network folders and printers", its the first option in the list.


The KB article says it only applies to XP but it applies to windows server 2003 also.

Saturday, 22 January 2005 22:28:46 (GMT Standard Time, UTC+00:00)  #    Comments [2]  General | Windows Server

# Monday, 17 January 2005
[FIX] Network bridge with Wireless + Wired connections is slow

Many thanks to James Crossley for his post on google which saved me a lot of bother.

"I have a wired network and an ad-hoc wireless network bridged on a Windows XP machine, which is also connected to broadband. The wired side ran fine but the wireless side ran very slowly (around 500kbps).
It ran at full speed when out of the bridge. The fix for this was:

  1. Run cmd (start, run, type cmd)
  2. type : "netsh bridge show adapter". Check which number is your wireless adpater.
  3. type : "netsh bridge set adapter x forcecompatmode=enable", where x is the number of your wireless adapter.

After I ran this, the network ran at full speed in the bridge."

This worked for me too.  both interfaces are running at normal speeds.

Monday, 17 January 2005 12:33:11 (GMT Standard Time, UTC+00:00)  #    Comments [2]  General | Windows Server

# Sunday, 16 January 2005
SQL - change object owner for multiple objects

Tibor Karaszi showed how to do this time saving technique on a newsgroup post. You can use the sp_changeobjectowner stored procedure in the Master database to change the owner of an object one at a time, but there is a semi-automatic way to do it.

Open query analyser, change the selected database to the on you want to work on. The following query generates a list of exec commands for all the sprocs and UDFs in the database:

SELECT 'EXEC sp_changeobjectowner ''dbo.' + ROUTINE_NAME + ''', ''thenewuser'''

Execute it, then click the top left corner of the output window, to select all the rows. Copy and paste the rows into a new query window and execute.

To change table owners, modify the query and repeat the proces:

SELECT 'EXEC sp_changeobjectowner ''dbo.' + TABLE_NAME + ''', ''thenewuser'''

Sunday, 16 January 2005 18:37:40 (GMT Standard Time, UTC+00:00)  #    Comments [2]  Database