.Net ramblings
# Monday, 04 April 2005
MS Access: using the LIKE function with a parameter
I wanted to do something like SELECT * from MyTable WHERE [Action] LIKE 'Hello%' but using a parameter instead of hard-coding the 'Hello' into the query. using VS and OleDb, the syntax for this wasn't obvious, what finally works is: SELECT * from MyTable WHERE ([Action] LIKE ? + '%')
Monday, 04 April 2005 17:51:14 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Sunday, 03 April 2005
MTB: Excellent trail in Limerick - Keeper Hill + Slieve Felim Way

hi,
i went exploring on a new route yesterday and it was by far the most exciting trail i've been on for a long time.  Map and Stats below.

Stats

  • Route length: 76k from Limerick city (Ireland)
  • Time taken: 8 hours including about 7x5-min rests, a half hour nap at the top, and about 10 camera stops.  so you can easily knock an hour and a half of this time.
  • Climb: 700m to Keeper Hill Summit
  • Terrain: About 50km of this trip is road, but it's the fast part and i think the 20k on trails is really worth it.  you could drive to newport and cut out the boring leg between limerick and newport.
  • Water needed: 2.5l energy drink was just about enough for me
  • Food needed: 3 snickers + banana + raisins

Map

Directions

The map has red arrows marking the route i took for the way up, and pink arrows for the way down. In case they don't make sense, here are the english directions:

  • From Limerick, take the N7 dublin road out as far as the roundabout on the new dual carriage way stretch. Take the second exit sign-posted newport.
  • Go straight through newport and keep on the main road and go past the entrance to Tooreenbrien woods. After that, take the next left up a small road.  (marked on map with a red arrow).
  • Keep going and turn right near the end of the road (down a hill) and you should link up with the Slieve Felim way, marked with the hill-walker signs.
  • Then just follow the hill walker signs up and across, and over the hill.
  • There is an extremely fast (and mucky when i did it) descent to Toor (a village apparently).
  • Go right on the main road for almost a kilometer. you get a good view of Keeper Hill from there. Turn left just at the handball court, following the hillwalker signs.
  • The Slieve Felim way doesn't actually take you to the summit, so follow the map to choose one of the paths that leads there.  I had to walk the last 150m because the path was very loose and stoney.
  • I choose the quickest way home once i had reached the top, and it is the fastest descent i've ever been on. the quality of the paths was pretty good when i was there, so i was able to clock 58kph. 
  • you can see with the pink arrows which route i took home.

if you have any corrections suggestions, post your comments below.


Sunday, 03 April 2005 10:23:40 (GMT Daylight Time, UTC+01:00)  #    Comments [1]  Outdoors

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

SELECT
    SuperMarketGroups.GroupName,
    SuperMarketGroups.ID, 
    ProductsInGroups.GroupProductCode, 
    ProductsInGroups.MinimumStoreRating, 
    ProductsInGroups.ProductID, 
    (IIf([ProductID] Is Null,'False','True')) AS GroupIncluded
FROM 
    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.Clear();
   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];
     dg.DataBind();
     dg.RenderControl(htw);
     response.Write(sw.ToString());
     response.End(); 
    }
   }
  }
 }
}

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:

^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{5,128}$ 

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

Background

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. 

Credits

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.

http://support.microsoft.com/?kbid=320138

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