Use HttpModule to turn tracing on via the querystring

Sometimes I want to turn tracing on in a production application to troubleshoot issues that I can’t replicate on my development machine.  It’s easy enough to do this by turning on application-level tracing in the web.config file, like so:

  <trace enabled=”true” pageOutput=”true” />

The problem with this is that changes to the web.config file cause the app domain to recycle, which can cause users currently in the application to lose data.  So I have to schedule a time when all users have to get out, or I have to work after hours, which is a drag.

It’s easy enough to turn on tracing programmatically – Page.Trace.IsEnabled = true.  Then you could write code in the page’s Load event, turning tracing on if the page detects a particular querystring.  The problem here is that you’d have to add the code to every page that you might someday want to trace.

So I decided to create an HttpModule that would turn on tracing via the querystring.  HttpModules allow you to tap in to the HttpRequest pipeline, so your code will run at a designated point in every ASP.NET request.  You can hook up your module to run at various points in the page life cycle, such as BeginRequest, AuthenticateRequest, Error, etc.  (HttpModules are also excellent for application-level error handling). 

In order to create an HttpModule, you have to create a class that implements the IHttpModule interface.  Do this by creating your class, and then typing “Implements IHttpModule” directly under the “Public Class Whatever” line.  Once you hit enter, the two methods that your class must invoke, Init and Dispose, are automatically created for you.

Here’s what my HttpModule class looks like (be sure to import the System.Web namespace):

Option Strict On

Imports Microsoft.VisualBasic
Imports System.Web

Public Class TraceModule
    Implements IHttpModule

    Dim WithEvents app As HttpApplication = Nothing

    Public Sub Init(ByVal context As HttpApplication) Implements IHttpModule.Init
        app = context
    End Sub

    Public Sub context_BeginRequest(ByVal sender As Object, ByVal e As EventArgs) _
    Handles app.BeginRequest

        Dim c As HttpContext = HttpContext.Current
        Dim strTrace As String = c.Request.QueryString(“trace”)

        If strTrace = “on” Then
            c.Trace.IsEnabled = True
        End If

    End Sub

    Public Sub Dispose() Implements IHttpModule.Dispose
        app = Nothing
    End Sub

End Class

After you’ve created your module, you just need to add a setting in the web.config file telling ASP.NET to use your module, as shown below.  If the class is in an assembly in the bin folder, you just need to provide the class name.  If it’s in the App_Code folder, then you need to add that to the setting.

   <!– Module class in assembly called TKS.dll in bin folder –>
   <add name=”WebTraceModule” type=”TKS.TraceModule”/>
   <!– Module class in App_Code folder –>
   <add name=”WebTraceModule” type=”TraceModule, App_Code” />

Now if you want to see the trace for any page in your application, just add the querystring “?trace=on” at the end of the URL, and the trace will display in the page without making any other changes.  If you want to add some security to this, so that not just anybody can run the trace, you can require additional querystrings, or only turn tracing on for certain users or groups, or just about anything else that you can do in code.


Ordinal Numbers in T-SQL

I wanted to write a query that took numbers and turned them into ordinal numbers – re: 1 = 1st, 2 = 2nd, 3 = 3rd, etc.

I took this code from Chip Pearson’s web site. His posting was about ordinal numbers in Excel. I converted the code to a T-SQL function, and I can now use it in any other T-SQL queries I write. Here it is:

create Function dbo.OrdinalNumber
/* used to return an ordinal number */
(@Number integer)
returns varchar(6)
as begin

declare @N int
declare @Suffix char(18)
declare @Result varchar(6)

select @N = @Number%100
select @Suffix = ‘stndrdthththththth’

If ((Abs(@N) >= 10) And (Abs(@N) <= 19))
Or ((Abs(@N) % 10) = 0)
select @Result= cast(@Number as varchar) + ‘th’
select @Result = cast(@Number as varchar) + substring(@Suffix,
((Abs(@N) % 10) * 2) – 1, 2)
return @Result

To use it just feed it the number you want to convert.

select dbo.ordinalnumber(1)
will return “1st”

Using “Execute As” to Test a Stored Procedure as a Different User

In an earlier blog post, I wrote about how to use “Execute As” in a stored procedure to access a separate database. When you include “Execute As” in a stored procedure, you can pull data from a separate database without giving your users direct permissions on that database.

Another way I really like to use “Execute As” is to test the permissions on my stored procedures. You may have had a time or two where a stored procedure ran great when you ran it, but when your user tried it… not so great.

Here’s how to test as that user from either Query Analyzer or SQL Server Management Studio. First, run this line of code, substituting SOMEDOMAIN and Someuser with the appropriate information.

execute as login = ‘SOMEDOMAINSomeuser’

Now run this line of code. It should return the login you just entered.


Now run your stored procedure.

When you are done testing, make sure you run this line (word, actually) of code:


This will set the login back to your login.

Simple way to renumber records in SQL Server

Recently I needed to renumber a column in a database.  Specifically, I needed to be able to take a Report Number series that started with 1 (1, 2, 3, 4) and change the start number and renumber all subsequent Report Numbers (e.g, change above to 14, 15, 16, 17).

I thought I’d probably be able to use a table variable with an identity column – hoped I wouldn’t have to use a cursor.  I did a quick Google search, to see how other people handled similar things and hopefully get some examples.

I saw the table variable solutions I expected, but then came across a post containing this really nifty bit of code that did the trick in four lines – very slick.  Here’s the example as it appeared in the post:

declare @variable int
set @variable = 0
update <tablename>
SET @variable = <columnname> = @variable + 1 

and here’s how I implemented it, along with a WHERE statement so that I only renumber the Report Numbers associated with a particular Job (@ReportNum is the new starting number, and is a parameter fed in to the stored procedure):

declare @counter int
set @counter = @ReportNum – 1
update tblReport
set @counter = ReportNumber = @counter + 1
where JobID = @JobID

Works like a charm.  I repeat – very, very slick:)

Accessing a digital camera through a dialog box in .NET

A client has asked us to write a little Windows Forms app that will take the pictures on a digital camera and transfer them to a folder on the hard drive.  No sweat, we thought – it’s very easy to transfer files and folders using the System.IO namespace – files on devices will surely function similarly.

But we searched and searched and Googled and Googled, and came up empty.  Finally we found this blog entry, which is apparently the definitive authority about using an old COM component called Windows Image Acquisition (WIA) to work with cameras and scanners:

(Important Note:  if you don’t have a device connected when you try to run the code that opens the dialog box, you will get an error that looks like this:

Exception from HRESULT: 0x80210015
Source: Interop.WIA
at WIA.CommonDialogClass.ShowSelectDevice(...) )

In the course of trying to work with WIA, we also found an MSDN entry (which has a lot of info but code samples are in C++) and a couple more posts with additional information:

Perhaps there is a more modern, .NET managed code way to do this, but until I find it, I thought I’d better have these links handy!!

Find First Day of Month, Last Day of Month, etc

Today I was writing some code where I needed to calculate the end of the month for a certain date.  As per usual, I Googled, and found various examples, some using old VB functions, some using methods of the Date object,  some very involved and using lots of string manipulation and converting of things from dates to strings and back again, etc.  I used these various examples to develop a little utility class, which is as follows:

Public Class UtilityDates

Public Shared Function GetFirstOfMonth() As Date
Return GetFirstOfMonth(DateTime.Today)
End Function

Public Shared Function GetFirstOfNextMonth() As Date
Return GetFirstOfNextMonth(DateTime.Today)
End Function

Public Shared Function GetEndOfMonth() As Date
Return GetEndOfMonth(DateTime.Today)
End Function

Public Shared Function GetEndOfLastMonth() As Date
Return GetEndOfLastMonth(DateTime.Today)
End Function

Public Shared Function GetFirstOfMonth(ByVal startDate As Date) As Date
‘remove all but one day from date to get first of month
Return startDate.AddDays(-(startDate.Day – 1))
End Function

Public Shared Function GetFirstOfNextMonth(ByVal startDate As Date) As Date
‘get first of month and then add one month
Return GetFirstOfMonth(startDate).AddMonths(1)
End Function

Public Shared Function GetEndOfMonth(ByVal startDate As Date) As Date
‘subtract one day from first of last month
Return GetFirstOfNextMonth(startDate).AddDays(-1)
End Function

Public Shared Function GetEndOfLastMonth(ByVal startDate As Date) As Date
‘subtract one day from first of the month
Return GetFirstOfMonth(startDate).AddDays(-1)
End Function

End Class

Note that each method is overloaded, so you can calculate from the default of today’s date, or send in a specific date.  Of course, you could add a few more functions from here, but I thought that this was plenty for today:)

How to connect to a SQL Server Named Instance

This is something I just don’t remember, so I thought this would be a good place to post it. If you are trying to connect to a named instance of SQL Server, for example TECHKNOWSOLVETECHKNOWSOLVE2000, you might get an error in your code that says this server doesn’t exist. The quick and easy fix is to refer to the server by its port number, ie TECHKNOWSOLVE, 444. In this instance TECHKNOWSOLVE is the name of the server and the port number is 444. Note that the server name is followed by a comma, not the backslash that is used in named instances.

How do you find out what port number your named instance is using? In SQL Server 2000, use the Server Network Utility. Choose your named instance. Under “Enabled Protocols”, click on “TCP/IP”. Click on the “Properties” button. There you will see a number for “Default Port”. This is the number that will follow your server name.

I haven’t tried this with SQL Server 2005, but this article tells you how to find the port for SQL Server 2005 (and SQL Server 2000, for that matter.)

Get Max Number from Datatable using the Compute method

This is a nifty little thing I came across recently.  I wanted to get the maximum value of a number field from a datatable, and learned about the Datatable’s Compute method.  It’s really easy to use.  The method takes two arguments – an expression argument, which is the aggregate expression you want to use (Count, Max, Sum, etc), and a filter expression, which functions like a SQL WHERE clause.  If you don’t want to filter by anything, you can send Nothing for the second argument (Null in C#).

An example:

Dim expression As String
Dim filter as string
Dim maxNum As Integer

‘get maximum job number from table
expression = “Max(JobNumber)”
filter = “JobDate > 12/31/06 AND JobDate < 1/1/08”
maxNum = CInt(tblJobInfo.Compute(expression, filter))

If you don’t want to filter, it would look like this:

expression = “Max(JobNumber)”
maxNum = CInt(tblJobInfo.Compute(expression, Nothing))

Gridview cell text values all Empty Strings

I spent a good part of today butting up against this problem – puzzled and Googled and experimented and Googled some more, but could not for the life of me figure it out – I kept doing exactly what the examples said I should do.  But then I finally found the answer, and so thought I would post it in the hopes of saving someone else the same hassle.

First, what I was trying to do.  I have an updatable gridview of a set size – it always displays 5 rows, and all 4 columns for all 5 rows will need to contain data when the user saves the page.  So, on page load, I want to loop through the gridview, inspect each cell to see whether it contains a value, and set its backcolor to yellow if it doesn’t to remind the user to fill it in.

The articles I read said that there were basically two ways to see if the cell has a value.  For a regular boundfield, you just check the cell’s text property directly, like so (All these examples are in the Gridview RowDataBound event, using that event’s System.Web.UI.WebControls.GridViewRowEventArgs argument.  This event fires on every postback.):

Dim c As Integer
Dim cell As TableCell

If e.Row.RowType = DataControlRowType.DataRow Then
   For c = 0 To e.Row.Cells.Count – 1

      ‘get cell
      cell = e.Row.Cells(c)

      ‘if cell is empty, make cell yellow
      If String.IsNullOrEmpty(cell.Text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
End If

And for a named control in a Template field, you use the cell’s FindControl method to get the control, and then check the control’s text property.  So if you had a Template field with a label named lblValue, you’d do this:

Dim cell As TableCell
Dim lbl As Label

If e.Row.RowType = DataControlRowType.DataRow Then

      ‘get cell
      cell = e.Row.Cells(0)

      ‘get control
      lbl = CType(cell.FindControl(“lblValue”), Label)

      ‘if label text is empty, make cell yellow
      If String.IsNullOrEmpty(lbl.Text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
End If

Turned out my problem was that I was using Template fields, but not using named controls (at least not in the item template) – so I couldn’t use the FindControl method because I didn’t have a control name to feed it, but just checking the cell’s text property returned an empty string.  Because, I discovered, when you use a Template field without a named control, like this:

<asp:TemplateField HeaderText=”Value”>
        <asp:TextBox ID=”txtValue” runat=”server” Text='<%# Bind(“Value”) %>’>

even though you don’t specifically put the Item Template value in a control, ASP.NET does.  You can’t tell this from looking at the source code – the value just appears to be in a regular <td> cell.  But if you look at the cell in the Command Window during Debugging (as I finally thought to do), you’ll see that there is a control in the cell’s control collection – a DataBoundLiteralControl.

So I needed to do a variation of the second get-the-value-from-the-control method.  Plus I had the added difficulty that I wanted the cell background to stay yellow even while the user was editing it.  But of course, when the row was in edit mode, the cell had 3 controls, not one – a LiteralControl, a Textbox, and another LiteralControl.  And, I didn’t want to have to name every control in order to use the cell’s FindControl method – I wanted to look at every cell.

So I worked out a procedure that found the controls I needed based on their position in the cell’s control collection, rather than their name – and adjusted for rows in edit mode.  My final code ended up looking like this:

Dim c As Integer
Dim cell As TableCell
Dim td As DataBoundLiteralControl
Dim txtBox As TextBox
Dim text As String

If e.Row.RowType = DataControlRowType.DataRow Then

   For c = 0 To e.Row.Cells.Count – 1

      ‘get cell
      cell = e.Row.Cells(c)

      ‘if row is in edit mode, get textbox control’s text value
      If e.Row.RowIndex = Me.Gridview1.EditIndex Then 
          txtBox = CType(cell.Controls(1), TextBox)
          text = txtBox.Text.Trim
      Else ‘otherwise, get DataBoundLiteralControl’s text value
          td = CType(cell.Controls(0), DataBoundLiteralControl)
          text = td.Text.Trim
      End If

      ‘if text is blank, set cell backcolor to yellow
      If String.IsNullOrEmpty(text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If

End If

Formatting percentages with Javascript

My current project has several pages where I need to grab values from textboxes, calculate a percentage, and then display the percentage in a label.  So I needed to write a Javascript function to do that.

As always, I relied on Google, as I’m not a Javascript expert, and find it easier to start with other people’s code than figure out all the details myself.  So I found a nifty little bit of code that formats a decimal as a percentage using Math.round.  All well and good, except my client wants some percentages to always have two decimals, even when the calculated value is a whole number.  And, of course, Math.round rounds to the nearest integer – no remainders.

So I found another nifty little bit of code that allows you to round to however many decimal places you want, rather than just to the nearest integer.   Better, but this code will return a whole number with no decimals when the calculated value is an integer, and I need two decimal places no matter what.

And so I found another neat bit of code, which uses the modulus operator to determine whether the value is an integer – if it is, then I’ll add the “.00” manually.

Just a couple more wrinkles – in this application, when the values in the texboxes are negative numbers, they will be formatted as (100), rather than -100.  And, sometimes the client wants two decimals, and sometimes he wants no decimals.

So I put it all together as follows (a previous function – not shown – does the calculation, and sends the calculated value to the function below):

function formatPercent(value, noDecimal)
//set default flag value
var isNegNum = false;

//set default multiplier value (will produce result with 2 decimal places)
var mult = 100;

//if neg number (begins with paren)…
if (value.substring(0,1) == “(“)
//set flag to true
isNegNum = true;

//remove parens from value
value = value.replace(/(/,”);
value = value.replace(/)/,”);

//if  no decimals wanted (‘noDecimal’ param has a value), set multiplier to 1
//(will produce result with no decimals)
if (noDecimal != null) {mult = 1;}

//format percent (‘mult’ variable determines whether has 2 or 0 decimals)
var result = Number(value)*100;
result = Math.round(result*mult)/mult;

//if decimals wanted (‘noDecimal’ param doesn’t have a value) and result
//is a whole number, manually add decimals
if (noDecimal === undefined && isInt(result) == true)
result = result + “.00”

//add minus sign to neg number
if (isNegNum == true)
result = “-” + result;

//add percent sign
result = result + “%”

//return formatted percent
return result;

function isInt(num)
// get the modulus
var mod = num % 1;

//if it’s 0, then it’s an integer
if (mod == 0) {
return true;
} else {
return false;

To use this function, send in the value that you want to be formatted as the first argument.  If you want the formatting to contain two decimal places, then don’t send anything in for the second (‘noDecimal’) argument.  If you want no decimals, then send in something for this second argument (doesn’t really matter what, as the function only checks to see if the parameter is null or not – I’ve been sending the string “true”).