.Net ramblings
# Wednesday, 02 May 2007
Crystal Reports: Incorrect Log on Parameters for Dataset source
i spent hours trying to troubleshoot this.  obviously the error makes no sense because CR should not be logging on to anything in a dataset scenario.
anyway, a thousand thanks to Jason for his post on the MSDN forums with the simple answer: set the datasource to the DataTable, not the DataSet.

i can't wait to lose Crystal reports altogether and move to the microsoft reporting thingy, i haven't had time to play about with it yet but at least it will be properly programmed and it won't contain the 10 years of bugs that crystal reports have carried through each release of their software.  </RANT>

Wednesday, 02 May 2007 18:43:46 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Asp.Net

# Tuesday, 01 May 2007
SQL: format numeric to x decimal places
i found this was very hard to figure out.  i read lots of approaches to hack the data into char(5) etc to give you "23.54" using Round() etc.  but that seemed messy.  the key is to cast to type Numeric(size, decimalPlaces).  for example:
CONVERT(Numeric(10,2), (Count(ID) * 100. / @Total)) as Percentage

the above will display "23.54" (for example), including rounding, as a number.

Tuesday, 01 May 2007 12:53:15 (GMT Daylight Time, UTC+01:00)  #    Comments [3]  Database

# Monday, 30 April 2007
LINQ: upgrading May CTP projects to Orcas Beta 1
here are a few of my main findings/hurdles encountered when upgrading May CTP web projects to Orcas beta 1 Web Application Projects.  I'm targeting version 3.5 of the framework for deployment on a Server 2003 with .Net 2.0 runtime installed, i will update this article if it doesn't work out. 
  • if you had any code in the "App_Code" folder, VS will probably have set the compile action to "content", it should be changed to "compile".  otherwise you will get errors like: The type or namespace name 'xyz' could not be found (are you missing a using directive or an assembly reference?)
  • you'll have to remove any reference to the System.Query and System.Expressions, these are not part of the new LINQ spec.
  • Replace System.Data.DLinq with System.Data.Linq.
  • if you want to use any LINQ expressions, like "from x in db.Table select x" then you need to include the System.Linq namespace.  otherwise you will get errors like 'System.Data.Linq.Table<xyz>' does not contain a definition for 'Where' and no extension method 'Where' accepting a first argument of type 'System.Data.Linq.Table<xyz>' could be found (are you missing a using directive or an assembly reference?)
  • you will also need to include System.Linq.Expressions if you are using "language-level code expressions to be represented as objects in the form of expression trees".
The old LINQ assemblies are versioned 1.0.2319.19044 (May CTP) but the new ones for Beta 1 are versioned  Have a check through the referenced assemblies in your project to make sure you have the latest versions. 

Crystal reports

look out for the new crystal report assemblies, 10.5.3700.0.  a web app referencing the new versions in web.config will complain if these versions are not available on the server.  i'll update soon when i have found out how to do this.

Monday, 30 April 2007 11:50:28 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Asp.Net

# Tuesday, 17 April 2007
SQL: optional parameters in stored procedure where clause
I found this out today, if you want a stored procedure to handle optional values without limiting the result set when the parameter is NULL, you can use a CASE/WHEN statement to ignore the parameter in the where clause if it is null.  you just set ColumnX=ColumnX via the CASE statement, which will always be true, effectively resulting in (TRUE AND {remaining condition})
Procedure dbo.Select_Totals_For_Year
@Name NvarChar(200),
@Year int
select Name, Count(ID) AS [Total]
FROM Table1
WHERE Name = @Name AND
Year = (CASE WHEN @Year is null THEN Year ELSE @Year END)
group by Name
order by Name

Tuesday, 17 April 2007 14:43:30 (GMT Daylight Time, UTC+01:00)  #    Comments [6]  Database

# Friday, 13 April 2007
SQL: Display full name in "Surname, Firstname" format.
not the most elegant solution you'll ever see, but i find it very useful when i don't want to store several columns for the different parts of a person's name in a database table.
it takes the surname based on the last space in the name.  so "Paddy Joe Gonzales" will be listed as "Gonzales, Paddy Joe".
Right(FullName, PatIndex('% %', Reverse(FullName))) + ', ' + Left(FullName, Len(FullName) - PatIndex('% %', Reverse(FullName)))    

Friday, 13 April 2007 14:13:30 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Monday, 02 April 2007
How to get YYYY-MM date formats in SQL
not exactly rocket science, but i always forget this sort of thing. 
CONVERT(char(7), GetDate(), 121)

this gives a date like "2007-03".  it works because SQL formats the dates like YYYY-MM-DD etc., so you can just take the first X characters to take as much of the date as you want.

another handy one i use is to get DD/MM/YYYY format:

Convert(NVarChar(10), GetDate(), 103)

the full list of date styles is on msdn2

Monday, 02 April 2007 14:28:57 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Friday, 09 March 2007
Really simple and affordable web server monitoring
If you run a web server, chances are you have some form of automated monitoring system in place.  If you use MOM or another enterprise level thing then this post won't be of much relevance.  If, like me, you have simpler requirements, read on.

I have been caught out a few times with my web sites being down because Windows Server 2003 automatically installed an update and something went wrong and IIS got stopped, or like this morning at 4am, SQL 2005 SP2 failed to install and left the SQL Service offline.  i didn't find out till i got a phone call from a client.

My datacenter provide very good ping monitoring with SMS alerts etc., but this is not a complete solution because the web site may have a configuration error, and it will still respond to pings.  similarly, you can't just check for an OK HTTP status code because your error ASPX page may not be configured to send an error HTTP status code.

I have used various online web site monitoring services, with varying degrees of success / satisfaction.  My current provider are InternetVista.com and for €70 a year i get a 10 minute check for a single HTTP site, with a keyword match on the contents of the page, and an email/sms alert if the match is not found.  You can pay for extra and more frequent checks, but €70 is as much as i think the service is worth.  To have this level of checking done on 10 sites would cost a lot, so to save a few quid i wrote a very simple aspx page that does a series of tests on all the resources i want to verify on the server, e.g. SQL Server, MS Access, IIS web sites.  The aspx code is listed below, i wrote it inline rather than compiled/dll because it is easier to deploy in an existing web site without any risk of any side effects (dll collisions), it should be straight forward to understand for a c# programmer.  let me know if you have questions.  It runs in a few miliseconds on my server so i'm not worried about polling all these resources every 10 mins.

Run_Server_Tests.aspx code:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Net" %>

<script RunAt="server">

/* Server Monitoring Script:
* - test SQL databases by running an sql string against an SQL connection string
* - test Access databases by running an sql string against a JET connection string
* - test web sites by Regex matching a search string against the contents of a HttpWebRequest

enum TestType {Sql_Server, Ms_Access, Http_Request } // different types of supported requests

/// <summary>
/// Container class to represent a 'test' object for a resource on the server.
/// </summary>
class TestObject
public TestType Type; // e.g. Sql_Server.
public string TestString; // e.g. connection string for a database. or URI for http request.
public string TestParam; // e.g. sql string for a database. or search string for a http request.

public TestObject(TestType type, string testString, string testParam)
this.Type = type;
this.TestString = testString;
this.TestParam = testParam;

void Page_Load(object sender, EventArgs e)
List<TestObject> tests = new List<TestObject>();

tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB1;Integrated Security=True", "select top 10 * from Table1"));
tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB2;Integrated Security=True", "select top 10 * from Table1"));
tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB3;Integrated Security=True", "select top 10 * from Table1"));
tests.Add(new TestObject(TestType.Sql_Server, @"Data Source=.\SQLEXPRESS;Initial Catalog=DB4;Integrated Security=True", "select top 10 * from Table1"));

tests.Add(new TestObject(TestType.Ms_Access, @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\Database\DB5.mdb;Persist Security Info=True", "select top 10 * from Table1"));
tests.Add(new TestObject(TestType.Ms_Access, @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\Database\DB6.mdb;Persist Security Info=True", "select top 10 * from Table1"));

tests.Add(new TestObject(TestType.Http_Request, "http://mysite1.ie/", "Site 1"));
tests.Add(new TestObject(TestType.Http_Request, "http://mysite2.ie/", "Site 2"));
tests.Add(new TestObject(TestType.Http_Request, "https://mysite3.ie/", "Site 3"));
tests.Add(new TestObject(TestType.Http_Request, "https://mysite4.ie/", "Site 4"));
tests.Add(new TestObject(TestType.Http_Request, "https://mysite5.ie/", "Site 5"));

int numCompleted = 0;
int numFailed = 0;

// write the HTML header. (a result is flushed to the client after each test finishes.)
<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01 Transitional//EN' 'http://www.w3.org/TR/html4/loose.dtd'>
<title>Server Test</title>
<meta name='ROBOTS' content='NOINDEX,NOFOLLOW'>
<link rel='stylesheet' type='text/css' href='ServerTestStyles.css' />

foreach(TestObject test in tests)
case TestType.Sql_Server:
runQuerySql(test.TestParam, test.TestString);
case TestType.Ms_Access:
runQueryOleDb(test.TestParam, test.TestString);
case TestType.Http_Request:
string pageContents = new WebClient().DownloadString(test.TestString);
if(!Regex.IsMatch(pageContents, test.TestParam, RegexOptions.IgnoreCase))
throw new Exception("Search string not found: " + test.TestParam);
throw new Exception("Test type not handled " + test.Type);
Flush(String.Format("<span class='pass'>Pass</span> &nbsp; <span class='type'>{0}</span> &nbsp; {1} <hr />", test.Type, test.TestString));
catch(Exception ex)
Flush(String.Format("<span class='fail'>Fail</span> &nbsp; {1} <span class='type'>{0}</span><BR><span class='error'>{2}</span><hr />", test.Type, test.TestString, ex.Message));
if(numFailed > 0)
Flush(String.Format("<h1>{0} errors occured</h1>", numFailed));
Flush(String.Format("<h1>All Good!</h1>", numFailed)); // if you use this page with an automated monitoring service, look for "All Good" in the page contents. otherwise an error occured

/// <summary>
/// Method to run an sql string against an sql database
/// </summary>
public static DataSet runQuerySql(string sql, string connString)
SqlConnection conn = new SqlConnection(connString);
DataSet ds = new DataSet();
SqlDataAdapter dba = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(sql, conn);

dba.SelectCommand = cmd;
dba.Fill(ds, "Table");
return (ds);
catch(Exception e)
throw e;

/// <summary>
/// Method to run an sql string against an Access database
/// </summary>
public static DataSet runQueryOleDb(string sql, string connString)
OleDbConnection conn = new OleDbConnection(connString);
DataSet ds = new DataSet();
OleDbDataAdapter dba = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand(sql, conn);

dba.SelectCommand = cmd;
dba.Fill(ds, "Table");
return (ds);
catch(Exception e)
throw e;

/// <summary>
/// Flush output to the browser (useful to indicate which tests are causing any delay)
/// </summary>
/// <param name="output"></param>
private void Flush(string output)


ServerTestStyles.css:  (just to make the output more legible)

font-size: 90%;
font-family: Calibri, Helvetica, Sans-Serif;
padding: .5em;

color: #87ceeb;
background-color: #87ceeb;
margin: .3em 0 .3em 0;
padding: 0;
height: 1px;

color: Blue;
font-weight: bold;
color: Red;
font-weight: bold;
color: purple;
font-weight: bold;
color: Red;
font-size: small;

I have configured the test in InternetVista to search for "All Good" in the url for the test page.  If this isn't present, i'll get an SMS/email alert and i can go and see what exactly is wrong.  It should be fairly easy to add other test types if you have different resources you need to check on.

Friday, 09 March 2007 17:43:24 (GMT Standard Time, UTC+00:00)  #    Comments [3]  .Net General | Asp.Net | Database | General

# Friday, 02 March 2007
FIX: VS 2005 crashes randomly when using Crystal Reports
Here is the event log crash entry:

Faulting application devenv.exe, version 8.0.50727.762, time stamp 0x45716759, faulting module craxddrt.dll_unloaded, version,
time stamp 0x43068582, exception code 0xc0000005, fault offset 0x0d26e30f, process id 0x1180, application start time 0x01c75cae13960a41.

it would crash completely randomly, even if i had closed all crystal reports and was working in a style sheet or other such harmless file. As i later found out, it was because the solution opened by default with the last crystal report i was working on, and this seemed to start some crystal report ActiveX thing that craps out on Vista.  sometimes it would take 2 minutes and sometimes 5 minutes, but it would always crash, even if i closed the report straight away.  The only way to get rid of it was to load up VS, close the report immediately and safely close VS before it got a chance to crash!  then next time it opens, there is no crystal report and the ActiveX control never loads.  it works ok now, although this is just one of a long list of complaints i have with VS 2005.  i think i'm allergic to crystal reports.

Friday, 02 March 2007 09:58:51 (GMT Standard Time, UTC+00:00)  #    Comments [5]  .Net General | Asp.Net

# Thursday, 01 March 2007
Problem with protected files on Micro SD card from Nokia 6233
i bought a 2gb SD card to replace the 64Mb one that came with my nokia 6233.  since the applications and games are stored on the 64mb card, i tried to copy them across to the new card, using my harddrive to help with the transfer.  problem is some of the JAR files are protected and cannot be read from the disk, i tried running as admin, xcopy, but access denied every time.  eventually i found Winimage which can create a 'virtual hard drive' from any disk (Micro SD in this case) and can then extract the files to the hard drive, bypassing the protection.  i then copied the files to the 2gb card keeping an identical folder structure.  seems to work nicely so far.

Thursday, 01 March 2007 13:42:02 (GMT Standard Time, UTC+00:00)  #    Comments [1]  General

# Wednesday, 28 February 2007
SQL Control Flow in stored procedures
coming from the c# world of strict control flow and built-in bail-out for unhandled exceptions, i was caught out by something in SQL 2005 recently.
many of my stored procedures take this form:
    update sometable set whatever = @whatever
    exec SP_History 'joe bloggs', 'order taken', '€500'
i was assuming that if the update statement failed, the subsequent SP_History SP would not be executed.  however i was wrong, and i now have the following code where i want the SP to stop executing if something goes wrong:
if @@ERROR <> 0 
    RETURN @@ERROR    -- bail out
the exception is still caught by .net, any handling code you may have will be unaffected there by the return statement.

Wednesday, 28 February 2007 15:54:09 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Database