A Toolbar of Your Favorite Menu Items? Interesting…

Originally posted at SOAPitStop.com – Sept 2, 2009

Remember that little idea that Office had a while ago that would hide infrequently-used menu items?  Wasn’t that a great idea?  For me, it was the very first thing I turned off after installing Office.  But I do understand what they were going after.  When applications do so much, every user is probably just using a subset of the whole application’s features.

The application that I’m writing is kind of getting like that.  A few versions ago, I created a toolbar on the side that was planned to be context-sensitive, so it would show actions based on what data was shown and available – kind of how Microsoft is now doing with the task pane.  Eventually, I may create or convert the toolbar to a task pane.  But as the application was growing, I had the same thought the Office designers had: each user probably only cares about 5 or 6 menu items at a time and those items should be as readily available as possible.  So instead of making personalized menus, I decided to create a Favorites toolbar.  This is similar to Microsoft programs where you can add toolbars and put menu items on them.

Because the application is in flux and because I am lazy, I didn’t want to go through the effort of creating a “Customize Toolbar” dialog.  I also didn’t want to have an extra dialog for “Add To Favorites”.  So what I did was allow menu items to be dragged onto the toolbar.  The proof-of-concept started as most do, just to see how it would work.  I got it going in under 150 lines of code, even less considering whitespace and definitions and all.

To quickly summarize the technique, I started by putting a toolbar container on the form, adding a toolstrip to hold the favorites, and adding a menu to hold the draggable items.

Then I added the code to allow the dragging of the menu items:

    Private Sub Menu_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) _
        Handles mnuFirst.MouseMove, mnuSecond.MouseMove, mnuThird.MouseMove, mnuFourth.MouseMove, _
        mnu2ndLevel1.MouseMove, mnu2ndLevel2.MouseMove, mnu2ndLevel3.MouseMove

        Dim item As ToolStripMenuItem

        If e.Button = Windows.Forms.MouseButtons.Left Then
            item = CType(sender, ToolStripMenuItem)
            item.DoDragDrop(item, DragDropEffects.Copy)
        End If

    End Sub

Then the code to drop the items (the toolstrip needs to have AllowDrop set to True):

    Private Sub toolFavorites_DragEnter(ByVal sender As Object, ByVal e As DragEventArgs) _
        Handles toolFavorites.DragEnter

        If e.AllowedEffect = DragDropEffects.Copy AndAlso e.Data.GetDataPresent(GetType(ToolStripItem)) Then
            e.Effect = DragDropEffects.Copy
        End If

    End Sub

    Private Sub toolFavorites_DragDrop(ByVal sender As Object, ByVal e As DragEventArgs) _
        Handles toolFavorites.DragDrop

        Dim droppedItem As ToolStripItem

        droppedItem = CType(e.Data.GetData(GetType(ToolStripItem)), ToolStripItem)
        AddToFavorites(droppedItem)

    End Sub

    Private Sub AddToFavorites(ByVal item As ToolStripItem)
        Dim newItem As ToolStripButton

        newItem = New ToolStripButton(item.Text, item.Image)
        newItem.Tag = item
        AddHandler newItem.MouseDown, AddressOf FavoritesContext
        AddHandler newItem.Click, AddressOf FavoritesClick
        AddHandler item.EnabledChanged, AddressOf OnMenuEnabledChanged

        toolFavorites.Items.Add(newItem)

    End Sub

Then the code to route the click of the favorites to the real menu item

    Private Sub FavoritesClick(ByVal s As Object, ByVal e As EventArgs)
        Dim item As ToolStripItem

        item = CType(CType(s, ToolStripItem).Tag, ToolStripMenuItem)
        item.PerformClick()

    End Sub

That was really it.  Of course, then I had to persist the favorites in My.Settings and provide a way of removing the favorite menu item, resulting in the above-referenced AddHandler statement for FavoritesContext and a couple other methods for running through the menu items on the form load and close.  Then we need to disable the favorite button when the linked menu item is disabled, leading to the AddHandler for OnMenuEnabledChanged.  It just keeps growing.

Dating Advice

No, I certainly can’t help you get dates or have successful dates.  But I can offer a couple of functions that might help you work with dates in your code.  I suck at dates (all types).  Every time I want to calculate dates, I need to print a calendar from Outlook and count the days.  It’s pretty ironic that one of my previous projects was all about schedules.  And one of my current projects deals with delivery schedules as well.  So I can’t escape it.

I always would get frustrated because every month was different.  Every month started on a different day and had a different number of days.  It felt impossible to get all the different combinations.  But recently, I had a moment of clarity and realized some basic facts about a month.  Things like:

  • No month has less than 28 days
  • This guarantees every month will have 4 weeks
  • This guarantees there are no less than 4 and no more than 5 of every weekday in a month
  • The only weekdays that will have 5 occurrences will be the days in excess of 28.  These days can be accounted for at the beginning or end of the month – it doesn’t matter.
  • By extension, there are a minimum of 20 workdays in a month (Mon-Fri)
  • And, any additional workdays will be those in excess of 28 that are between Monday and Friday

Earlier attempts to figure out the number of workdays in a month resulted in a brute force loop that would run through every day from 1 to 31 and if the DayOfWeek was Mon-Fri, increment a counter.  Now, with these new guidelines, I can start at 20 and only deal with 0-3 excess days.  Like with this function:

Shared Function WorkdaysInMonth(ByVal d As Date) As Integer
    Dim daysInMonth As Integer
    Dim extraWeekDays As Integer

    daysInMonth = New Date(d.Year, d.Month, 1).AddMonths(1).AddDays(-1).Day

    For i As Integer = 1 To daysInMonth - 28
        Select Case New Date(d.Year, d.Month, i).DayOfWeek
            Case DayOfWeek.Monday, DayOfWeek.Tuesday, _
                DayOfWeek.Wednesday, DayOfWeek.Thursday, DayOfWeek.Friday

                extraWeekDays += 1

        End Select

    Next

    Return 20 + extraWeekDays

End Function

Using the other rules for weekdays, we know we only need to deal with the exceptions, to find out whether there are 5 weekdays in a month:

Private Shared Function NumberOfWeekdaysInMonth(ByVal weekday As DayOfWeek, ByVal referenceDate As Date) As Integer
    Dim firstDay As DayOfWeek
    Dim lastDay As DayOfWeek

    firstDay = New Date(referenceDate.Year, referenceDate.Month, 1).DayOfWeek
    lastDay = New Date(referenceDate.Year, referenceDate.Month, 1).AddMonths(1).AddDays(-1).DayOfWeek

    If New Date(referenceDate.Year, referenceDate.Month, 1).AddMonths(1).AddDays(-1).Day = 28 Then
        Return 4

    ElseIf lastDay >= firstDay AndAlso weekday >= firstDay AndAlso weekday <= lastDay Then
        Return 5

    ElseIf lastDay < firstDay AndAlso weekday >= firstDay - 7 AndAlso weekday <= lastDay Then
        Return 5

    ElseIf lastDay < firstDay AndAlso weekday >= firstDay AndAlso weekday <= lastDay + 7 Then
        Return 5

    Else
        Return 4

    End If

End Function

That one got a bit hairy because is the extra days started at the end of the week with a high DayOfWeek value, and ended early in the week with a low DayOfWeek value, we had to compensate at each end.  That’s the reasons for all the different IF conditions.  There’s also a specific condition for February’s 28 days.

Finally, a function to determine the first, second, third, fourth, or fifth weekday in a month.  Useful when calculating holidays like Labor Day and Thanksgiving.

Private Shared Function NthDayOfMonth(ByVal index As Integer, ByVal weekDay As DayOfWeek, _
    ByVal referenceDate As Date) As Date

    Dim firstDay As DayOfWeek
    Dim dayOfMonth As Integer

    firstDay = New Date(referenceDate.Year, referenceDate.Month, 1).DayOfWeek
    dayOfMonth = (7 * (index - 1)) + _
        CInt(IIf(firstDay > weekDay, 7 + weekDay - firstDay, weekDay - firstDay))

    Return New Date(referenceDate.Year, referenceDate.Month, dayOfMonth + 1)

End Function

Autosize. No, not the control, the text.

Originally posted to SOAPitStop, Nov 16, 2008.

It’s nice that .NET controls have an auto-size property so you don’t have to worry about overflow and all.  But what about cases where you have a fixed layout?  Well, that’s simple, you turn autosize off and fix the control to the size you need.

That’s half the story.  What about the text that’s inside it?  Now you know I’m talking to marketing people when I say that there are times you want the text to be as big as possible within that control.  But you can’t just set the font to a huge size, because sometimes you’ll have more text to display and the font size must regrettably be reduced.

To accommodate this, I made a quick method that brute-forces the correct font size in the control.  basically, stepping down the size of the font until it fits.  I know loops like this are cheap, poor programming, and I did give consideration to doing some hard math to calculate the proper font size based on the initial size, but sometimes not getting hung up on performance can be liberating.

    Private Sub ResizeText(ByVal c As Control)
        Dim currentSize As Size
        Dim currentFont As Font

        currentFont = c.Font

        Do
            currentSize = TextRenderer.MeasureText(c.Text, currentFont, _
                c.Size, TextFormatFlags.WordBreak)

            If currentSize.Width > (c.Width - c.Margin.Horizontal) _
                OrElse currentSize.Height > (c.Height - c.Margin.Vertical) Then

                currentFont = New Font(currentFont.FontFamily, _
                    CSng(currentFont.Size - 0.5), currentFont.Style, currentFont.Unit)
            Else
                Exit Do

            End If

        Loop While currentFont.Size >= 1

        c.Font = currentFont

    End Sub

/span

Casting Upwards

When binding business objects to a datagrid, often you have a need to display some information that is not directly exposed by the object itself.  Maybe it’s a calculated value, maybe it’s something nested deeper in the object.  When faced with this issue, there are a few different action paths you can take.  You can add extra read-only properties to your business object to support the extra view information.  You can create a new class that inherits from the class you are displaying and put the extra properties in there.  Or you can handle the CellFormatting event in the datagrid and change the displayed values manually.  One of the downsides of using a new derived class with extra properties is that you can’t cast a base class to it.  You could cast down to the base class, but no casting up.

Here is a technique that is closest to the second option listed above and side-steps the upcasting problem.  I dislike the first option because it clutters the business object with UI-specific code.  Going with option 2 is only slightly better, while you can populate the correct display-specific object and return it from your business logic layer, either you have to have a method that return the derived type, or you will have to cast it to its correct type in the UI.  Even then, your business layer still contains UI logic.

So, keeping things separated, the derived display-specific class should be defined in the UI layer.  This means it will have extra read-only properties for use with databinding.  The business layer will return the basic object(s), so it will be up to us to convert these to UI-friendly versions.  There are two problems with converting the object: not all the object state may be exposed via public properties, and those properties may contain logic.  It would be best to copy the object by its internal state – private variables.

On first thought, working with the private variables means the code must be inside the source object and the destination object.  This would be tedious to do, passing in the destination object, then sending the source object’s private variables to the destination so the destination object can manipulate its own private variables.  Yuck.  However, using Reflection, the job gets a whole lot easier.

Here’s a small class with a method to convert one class to another by copying its private and public fields.  The properties are intentionally excluded since they may contain logic that modifies the internal state.  You should use this technique with care and know exactly what it does and does not do.  Basically, it copies values from one instance of a class to another.  This is fine for simple classes, but it’s not going to resolve references for you.

Consider ClassA with a private field of type ClassB.  ClassB maintains a private variable with a reference to ClassA, so that it can manipulate all of its "parent’s" state and logic.  If you use this technique to cast ClassA to ClassAA, because you want an extra property to display some info from ClassB, you’re in for some fun results if you change some data in ClassAA.  This is because ClassB still has a reference to ClassA, not ClassAA.

Public Class UpCaster
    Shared Sub CastUp(ByVal sourceObj As Object, ByVal destinationObj As Object)
        Dim values As New Dictionary(Of String, Object)
        Dim props() As Reflection.FieldInfo 

        props = sourceObj.GetType.GetFields(Reflection.BindingFlags.NonPublic _
            Or Reflection.BindingFlags.Static _
            Or Reflection.BindingFlags.Instance _
            Or Reflection.BindingFlags.Public) 

        For Each p As Reflection.FieldInfo In props
            values.Add(p.Name, p.GetValue(sourceObj))
        Next 

        props = destinationObj.GetType.GetFields(Reflection.BindingFlags.NonPublic _
            Or Reflection.BindingFlags.Static _
            Or Reflection.BindingFlags.Instance _
            Or Reflection.BindingFlags.Public) 

        For Each p As Reflection.FieldInfo In props
            If values.ContainsKey(p.Name) Then p.SetValue(destinationObj, values(p.Name))
        Next 

    End Sub 

End Class

Too Many Items In Combo Box: When One Is Just One Too Many

I got to troubleshoot a dumb error message today.  The error was "Too many items in combo box."  The situation was anything but.  I was only adding one item.

So I got it working and I wanted to find out why it happened in the first place.  The error it should have returned was "Value cannot be NULL" because that was the root of the problem.  So here’s a distilled piece of code to illustrate the problem.  Create a form and put a combo box on it.  The code for the form should look like:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ 
        Handles MyBase.Load

        Dim d As New DisplayItem
        ComboBox1.Items.Add(d)

    End Sub

End Class

Public Class DisplayItem
    Public Name As String

    Public Overrides Function ToString() As String
        Return Name
    End Function

End Class

The problem is the combo box is trying to display DisplayItem.Name, because that is what the ToString says to do, but the value of Name is Nothing.  You can fix this by setting the value of Name to String.Empty or something else.  The odd thing is that you can also fix the problem by commenting out the ToString override.  To figure this out, I fired up Reflector and went to see what was going on behind the scenes.

This particular situation is basically bypassing all the safe value checks done when adding an item to a list control.  I suppose Microsoft should add a test case for this scenario, but really, if the programmer is attentive, this shouldn’t happen.  I, naturally, happen to be inattentive.

Behind the scenes of the Add method of the Items collection, the first check is in the AddInternal method.  Since we’re passing in an instance of DisplayItem, it passes that check.  The next step is in the NativeAdd method.  At this point, we’ve done our NULL checks and it is assumed we can convert the object to a string.  This method now calls GetItemText.

GetItemText parses the properties of the object passed in and gets the string value.  If the DisplayMember property is not set, the control uses the ToString value of the object itself.  Because we overrode ToString, the control trusts us and returns the value from ToString, the Name value.  This turns out to be Nothing – Oops!  We’ve already passed the check for Nothing, so this sends bad data to the Win32 API, bubbling a failure error code back to NativeAdd.  If NativeAdd gets anything but a success, it always returns the message “Too Many Items In Combo Box”.  But the real reason is that you snuck a Nothing past the initial validation.

Interestingly, if the DisplayMember is set, and the value of the property is Nothing, it is handled properly in GetItemText.  If we converted Name to a private field and made a public property, then set the DisplayMember of  ComboBox1, it would work.  If your display member is another object that overrides the ToString function, you can get around that check as well and return Nothing, causing a failure.

The simple solution for this error message is to avoid NULL values.  The bottom line is to have .ToString always return a string, never Nothing.

IsInRole While Disconnected; No Longer IsInHole

In an application I am continuing to write, during startup, the user’s security is determined by the user’s security groups and permissions are granted within the program based on the group membership.  This has worked fine.  Then one day in an airport I wanted to work on some documentation and I quickly discovered that I could not run the program.  I could not run the program because I was not on the VPN and the Active Directory (AD) groups could not be enumerated to check my permissions.  At the time, I was kind of grateful I couldn’t do any work and didn’t give it much more thought. Our application is not designed to be run in a disconnected fashion.  You have to be on the VPN to get to the database server and use it.  Lately, I revisited the problem and decided to resolve it.

In my particular instance, I had a database running locally, but I was missing an Active Directory server to read from.  I had my cached credentials, shouldn’t that be enough?  Well, it is, if you do it the hard way.

Everyone should know that a user or a group is just a name.  There is an ID behind that group or user, which allows you to rename the group/user without breaking anything.  Behind the scenes, Windows always uses the ID.  The name is just for your benefit.

Using the excellent tool WhoAmI, I saw the following (don’t chide me for running as Administrator):

C:\>whoami /groups /sid

[Group  1] = "700CB\Domain Users"  S-1-5-21-2454202000-1896829455-2950045386-513
[Group  2] = "Everyone"  S-1-1-0
[Group  3] = "DA3\Debugger Users"  S-1-5-21-854245398-1547161642-725345543-1007
[Group  4] = "DA3\Offer Remote Assistance Helpers"  S-1-5-21-854245398-1547161642-725345543-1004
[Group  5] = "BUILTIN\Users"  S-1-5-32-545
[Group  6] = "BUILTIN\Administrators"  S-1-5-32-544
[Group  7] = "NT AUTHORITY\INTERACTIVE"  S-1-5-4
[Group  8] = "NT AUTHORITY\Authenticated Users"  S-1-5-11
[Group  9] = "LOCAL"  S-1-2-0
[Group 10] = "700CB\Domain Admins"  S-1-5-21-2454202000-1896829455-2950045386-512

When disconnected form the LAN (you might need to reboot while disconnected to clear the cache), it looks like:

C:\>whoami /groups /sid

[Group  1] = ""  S-1-5-21-2454202000-1896829455-2950045386-513
[Group  2] = "Everyone"  S-1-1-0
[Group  3] = "DA3\Debugger Users"  S-1-5-21-854245398-1547161642-725345543-1007
[Group  4] = "DA3\Offer Remote Assistance Helpers"  S-1-5-21-854245398-1547161642-725345543-1004
[Group  5] = "BUILTIN\Users"  S-1-5-32-545
[Group  6] = "BUILTIN\Administrators"  S-1-5-32-544
[Group  7] = "NT AUTHORITY\INTERACTIVE"  S-1-5-4
[Group  8] = "NT AUTHORITY\Authenticated Users"  S-1-5-11
[Group  9] = "LOCAL"  S-1-2-0
[Group 10] = ""  S-1-5-21-2454202000-1896829455-2950045386-512

My local group names were resolved to their names, but my domain group names couldn’t resolve because AD was unreachable.  My cached profile still had the SIDs though.

So, if I could do an IsInRole check using the SID instead of the domain group name, I’d be golden.  And this is just what I did.

Imports System.Security.Principal
Imports System.Threading

Dim sid As New SecurityIdentifier("S-1-5-21-1859785585-1835888107-1082013118-1025")
Dim p As WindowsPrincipal = CType(Thread.CurrentPrincipal, WindowsPrincipal)

MsgBox(p.IsInRole(sid))

So I took the SID for the groups I was testing for and tested for them instead.  Obviously, if an admin deleted and recreated the group thinking nobody would notice, it would be hell to troubleshoot, so if you’re not fully in control of your environment, you might want to steer clear.  Maybe do the SID after checking IsNetworkAvailable to reduce the exposure to failure?

But for me, it works like a champ, and now I can work in airports.  Hmmm.  Why did I figure this out again?

Columns Autosize: Listview in List View

Here’s another little snip of code I couldn’t find online when I needed it.  Geez, when I became a programmer, I wasn’t thinking I’d have to actually figure things out on my own.  That’s a lot of work.

Anyway, the problem I faced was when I had a Listview control on a form and I changed views from anything to List view, the columns of the items were really small, so I’d get ellipses’ after all the entries.  Sure, I could just set the column with to some obnoxious amount like 500, but that’s a waste of space.  So after searching and seeing a bunch of postings about using a Win32 API call to autoresize the column, then getting disappointed because it was for VB 4/5/6, I just hacked through it.

The Details view has an autosize feature, but apparently they didn’t extend it to the List view.  But we can still make use of it.  Why not switch to detail, set the autosize of column 0 to true, then measure how wide it makes the column, then use that as the column width in List view?  That’s a dumb idea.  Who would do something like that.  Oh, what do you know, it works.

Dim maxSize As Integer

lstItems.View = View.Details
lstItems.Columns(0).AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent)
maxSize = lstItems.Columns(0).Width

lstItems.View = View.List
lstItems.Columns(0).Width = maxSize
lstItems.Refresh()

Yay.

A Totally Non-Random Post

Originally posted to SOAPitStop.com, Jun 25, 2008.

I am sure this is covered somewhere on the great big Internet, but it’s new to me.  And it sucked until I discovered what the problem was.  Basically, I had random values being generated that were anything but random.  “Oh, you forgot to set a seed value to something pseudo-random like now.millisecond.”  No.  That didn’t help.  Check out this code and see what the results are:

    Private Sub UnRandom()
        Dim r As New Random(Now.Millisecond)

        Dim winCount, winCount2 As Integer

        For i As Integer = 1 To 100
            If Rndm() > 50 Then winCount += 1
        Next

        For i As Integer = 1 To 100
            If r.Next(1, 100) > 50 Then winCount2 += 1
        Next

        MsgBox(winCount & ":" & winCount2)

    End Sub

    Private Function Rndm() As Integer
        Dim r As New Random(Now.Millisecond)

        Return r.Next(1, 100)
    End Function

Assuming a 50/50 chance of win, you would expect that a Random.Next would return a random value.  Seemingly not when it gets continually instantiated like in the Rndm function.  So this apparently is not a case where you want all of your variables to be contained in their own methods.  You’d probably want to have one single random number generator for your whole application.

In Log We Trust (or why did you have to make it so difficult?)

I guess this might be kind of a belated rant… some will know what i’m talking about. At a previous job, we had a need to log stuff: errors, debug info, etc. We were given an API by the upper programmers and it was, shall we say, less than excellent. It was based on some MS application block, which had dependancies on a bunch of other application blocks and made your config files massive. But, boy was it configurable. Blah. The most difficult part was writing out the signature of the method and the parameters. We couldn’t consider doing the values passed in yet. Every time we changed the method signature, we had to alter all the logging statements. We usually ignored logging until late in development when the methods would stabilize.

So I proposed a more simple way. I mean, Reflection is supposed to know all this stuff, right? Why not have it interrogate the current method and pass it and its parameters somewhere to be logged, or at least generated. It was declined. I didn’t take it personally, but inside I was using an evil villain voice: "you fools!"

So when I left, I took my ideas with me and quickly built this into my new application suite. It’s not as wicked-cool as having a config file that can switch between text file and sql logging, in fact, it has NO config file. The simplicity of the logging is the key.

So let’s say you want to log an error. This is all you write:

Try

Catch ex As Exception
    Logging.LogError(ex, System.Reflection.MethodInfo.GetCurrentMethod)

End Try

And it’s the same for every method. That right there is worth its weight in code. But what about when you have parameters? You would need to capture them too. The code is almost unbearable at that point, right?

Try

Catch ex As Exception
    Dim errorParms As New System.Collections.Specialized.StringCollection
    With errorParms
        .Add(parm1)
        .Add(parm2.ToString)
    End With
    Logging.LogError(ex, System.Reflection.MethodInfo.GetCurrentMethod, errorParms)
End Try

So what’s the error log look like? It’s tab-delimited and has the basics of what you need:

DATE USER METHOD ERROR MESSAGE

4/16/2008 9:04:30 PM 700CB\anachostic WindowsApplication1.Form1.LogWithoutParameters(parm1,parm2) This is a generated error.

4/16/2008 9:04:30 PM 700CB\anachostic WindowsApplication1.Form1.LogWithParameters(parm1=This is parm 1,parm2=12345) This is a generated error.

Have I sold it yet? Or at least given you a starting point for your own logging "framework"? Ahem. Please, it’s just a simple class. Here it is for your plunder:

Imports System.Reflection
Imports System.Collections.Specialized

Public Class Logging

    Public Shared LOGFILE_NAME As String

    Shared Sub New()
        LOGFILE_NAME = "c:\" & My.Application.Info.ProductName & "-Errors.log"

    End Sub

    Shared Sub LogError(ByVal ex As Exception, ByVal method As MethodBase)
        LogError(ex, method, Nothing)
    End Sub

    Shared Sub LogError(ByVal ex As Exception, ByVal method As MethodBase, _
        ByVal parameterValues As StringCollection)

        Dim sb As New System.Text.StringBuilder

        Try
            With sb
                .Append(Now.ToString)
                .Append(vbTab)
                .Append(My.User.CurrentPrincipal.Identity.Name)
                .Append(vbTab)

                If method IsNot Nothing Then
                    .Append(method.ReflectedType.FullName & "." & method.Name)
                    .Append("(")


                    For i As Integer = 0 To method.GetParameters.Length - 1
                        .Append(method.GetParameters(i).Name)
                        If parameterValues IsNot Nothing _
                            AndAlso i < parameterValues.Count Then
                            .Append("=" & parameterValues(i).Replace(vbCrLf, "<CR>"))
                        End If

                        .Append(",")

                    Next

                    If sb.ToString.EndsWith(",") Then sb.Length -= 1

                    .Append(")")

                End If

                .Append(vbTab)

                If ex IsNot Nothing Then
                    .Append(ex.Message.Replace(vbCrLf, "<CR>"))
                End If

            End With

        Catch exc As Exception
            sb.Append("<ERROR WHILE PARSING: " & exc.Message & ">")
        End Try

        LogMessage(sb.ToString)

    End Sub

    Shared Sub LogMessage(ByVal msg As String)
        Try
            If Not IO.File.Exists(LOGFILE_NAME) Then
                IO.File.WriteAllText(LOGFILE_NAME, "DATE" _
                    & vbTab & "USER" _
                    & vbTab & "METHOD" _
                    & vbTab & "ERROR MESSAGE" _
                    & vbCrLf)

            End If

            IO.File.AppendAllText(LOGFILE_NAME, msg & vbCrLf)

        Catch ex As Exception

        End Try

    End Sub

End Class

In CASE you were wondering…

Here’s a thought I had a couple of days ago. I actually was almost done with this blog entry yesterday, but ended up closing the window and not saving any of it. Argh.

My thought was on sql sorting. There are times when you want to have your results sorted at the server based on a parameter. Those of you who like dynamic sql would be all over that. It’s so simple (in Northwind):

declare @SortType int
declare @sql varchar(2000)
-- SortTypes
-- 1- Lastname
-- 2- Firstname
-- 3- Employeeed
-- 4- Extension
-- 5- postalcode

set @sorttype=4

set @sql='select * from employees'
if @sorttype=1 set @sql=@sql + ' order by lastname'
if @sorttype=2 set @sql=@sql + ' order by firstname'
if @sorttype=3 set @sql=@sql + ' order by employeeid'
if @sorttype=4 set @sql=@sql + ' order by extension'
if @sorttype=5 set @sql=@sql + ' order by postalcode'

exec(@sql)

Ok. But as I’ve said before, I am not a fan of dynamic sql. And my environment doesn’t allow it because all security is done through the stored procedures. So, how do we do the same thing without dynamic sql? It’s a great use of the CASE function.

Let’s start with the first two sort types: lastname and firstname. you can put the CASE in the ORDER BY section:

select *
from employees
order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    end 

That was simple. So let’s add the third type, employeeid.

order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then employeeid
    end

Works well. Wait, no it doesn’t. Sorting by type 3 is fine, but sorting bu type 1 or 2 gives:

Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the nvarchar value 'Davolio' to data type int.

Ok, that’s not a problem, we’ll just convert everything to varchar. So now it’s like:

order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then cast(employeeid as varchar)
    end 

That works, kind of. You won’t notice the bug on Northwind, but you will when we do the next type, extension. We need to convert it to varchar also.

order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then cast(employeeid as varchar)
    when 4 then cast(extension as varchar)
    end 

Wow, that’s not right. 2344 doesn’t come before 428. But it does in the string world, which you forced your way into. There is a solution. we just need to pad the numbers with zeros. But how many zeros? All the values have to be the same length to be considered equivalent. so now we have:

order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then cast(employeeid as varchar)
    when 4 then replicate('0',10-len(extension))+ extension
    end 

That works fine. And we’ll have the same problem with postalcode, which is numeric and character, so we need to pad the beginning of those to be equal-length strings. The final version:

select *
from employees
order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then cast(employeeid as varchar)
    when 4 then replicate('0',10-len(extension))+ extension
    when 5 then replicate('0',20-len(postalcode))+ postalcode
    end 

You’d want to make the length of the postalcode be the max length of the field, in case it’s blank. But the point is CASE is a very powerful tool. It can be used anywhere a single value can be used. In ORDER BY, in JOIN, in SELECT, it’s universal. Now I’m going to save this before I lose it again.