Mark Mintoff My superpower is common sense

5Nov/130

Microsoft Excel Mod Problem

There exists a problem in Microsoft Excel's Mod function, whereby performing modulo on decimal numbers results in incorrect remainders.

For instance, MOD(0.17, 0.17) results in 0 (as expected), whereas MOD(0.51, 0.17) results in 0.17 (incorrect).

In fact, a modulo on the following multiples of 0.17 has resulted in many erroneous reports of remainders:

modulo1

The reasoning behind this is that internally a float data type is used which results in an incorrect modulo to be calculated. In fact, if you try this out in C# using float or double, you will get the exact same results.

The solution, I have found is to use the formula for modulo:
    x - (y * INT(x/y))

To this end, I have created a simple function through VBA which achieves a correct result by enforcing the usage of the decimal data type.

 

VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)
11Jun/134

ASP.Net Update Panel Flickering Scroll Issue

There appears to be an issue with the ASP.Net Update Panel which causes scroll position to be lost in between page reloads, making the page appear jumpy.

This is a very simple, yet effective fix:

The secret is this little nugget of javascript written exactly after the Script Manager:

This disallows the resetting of the scroll position, meaning that the original scroll position is maintained.

VN:F [1.9.22_1171]
Rating: 5.0/5 (3 votes cast)
7Mar/131

Regex Split by Comma not Surrounded by Quotes

Recently I had a requirement where I had to programmatically extract data from a very messy CSV file and populate a table. The data contained addresses, which included commas in them. In order to distinguish between a comma which separates columns and a comma in the text, the text was surrounded by double quotes.

The solution is obvious; use Regex. The regular expression however is not so straight-forward.

In C#, with escape characters:

Works like a charm.

VN:F [1.9.22_1171]
Rating: 4.9/5 (19 votes cast)
10Jan/130

Developer Friendly Windows Search

As a developer, I find Windows' search incredibly lacking. I can never find the files I want, especially if I'm trying to find a particular pattern of text within the files. To this end, I resorted to making my own simple search algorithm:

I hope you find this to be as useful as I do.

VN:F [1.9.22_1171]
Rating: 5.0/5 (3 votes cast)
16Nov/120

C# ASP.Net Custom HTML Button

I have opted to create an ASP.Net button (postback and event handle) from scratch.

Why? Because I can. Also because it allows me more control over the visual presentation of a button.

This little endeavor is still a work in progress however it supports a server-side click event, a client-side click event, button text, an icon image and works within ASP Update Panels as well.

The markup is relatively straight-forward:

I am using an

  • as the root element, because most of the time I make use of a
      -based layout

      The back-end for the button is as follows:

      As you can see, I am making use of the IPostBackEventHandler and registering the control with the ScriptManager as a PostBackControl or an AsyncPostBackControl, depending on whether or not the control is contained within an Update Panel.

      Currently, I am hoping there is a more efficient way of detecting whether the button is within an Update Panel, but the while loop will do for now.

      I have written a small amount of CSS for the button, in order to make it look good:

      In a perfect world, I would not need to write CSS to support anything less than IE8, but this CSS breaks on IE7. I would imagine it would do very much the same thing on IE6. However, I actually dedicated (wasted) an hour towards trying to get this to work on IE7:

      Usage is simple and can be figured out from the download:

      What I have provided is a veritable playground of design customization in lieu of the standard button whilst more or less retaining the same functionality of the standard button.

      Any customization or features are completely up to you. Happy coding/designing!

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  • 24Sep/126

    C# Console Progress Bar

    At work, I sometimes find myself needing to make Console applications which take some time. For example, I have a few console applications which parse dump files into objects and then insert the data into a database. Usually, I represent the progress of these applications with a simple incremental percentage display, however I thought I'd create a generic method which would display an ASCII progress bar.

    The code itself is pretty self explanatory and probably more verbose than it really needs to be, but it gets the job done and looks good. It is a nice throwback to Pascal and QBasic for me.

    EDIT: Amended code to append characters to a string before writing on screen. This fixes the flickering problem.

    VN:F [1.9.22_1171]
    Rating: 5.0/5 (13 votes cast)
    27May/120

    The Towers of Hanoi Problem

    About a year ago, in the summer of 2011 I had found myself unemployed for 2 months through no fault of my own. I had accepted an ASP.Net development position with a gaming company called GamingVC and had thus resigned from my job at WagerLogic.

    Unfortunately, on the day I was to begin my new job with GamingVC, as soon as I entered the building; I was taken into a boardroom and fired without reason. Thus, I spent two months searching for gainful employment and had many a strange interview.

    The worst interview I had that summer, and consequently the worst interview I had in my entire life is what I shall be talking to you about today.

    By nature, I am an introverted person. I do make an effort to be sociable, however it takes me some time to warm up to a person and be comfortable. The interview, unbeknownst to me consisted of an interview with a woman in human resources and then a surprise interview consisting of the IT manager and a senior programmer. The IT manager was boisterous and rude, bouncing a stress ball against the wall, seemingly paying no regard to a word I was saying. I felt stressed, sweating inside a thick suit in the middle of June, when suddenly the CIO walks in. After a few minutes he launches into a tirade about games.

    He mentions to me Towers of Hanoi as if it were the greatest game ever to have been created (he's wrong, the greatest game is chess), framing it with arrogant disdain aimed at me with the term "before your World of Warcraft nonsense we had a game called Towers of Hanoi". It is funny, come to think of it, because I never even played World of Warcraft. Anyway, he wanted me to programmatically solve Towers of Hanoi on a whiteboard whilst they all watched. Given my character and nervousness I failed miserably and here's why:

    Towers of Hanoi is a game I played when I was seven years old. I played it on a cheap hand-held device along with Tetris, Snake and some other monochrome pixel games. At that age, I had not a care in the world and I had not yet started to analyze everything in terms of functional programming. Nor had I any idea what recursion was when I was that young. Suffice it to say, I knew how to solve it myself, which I saw mentally as a while loop, without having figured out that it was a recursive loop. What's more I am not accustomed to programming without a computer and with three people burning a hole in the back of my head.

    So, for the sake of no longer feeling stupid (because they made me feel pretty stupid) at having mucked it up so badly during my interview, I present to you the solution:

    I believe that redeems me from the absolute mess of a while loop I had tried to do on a whiteboard and I can (after a year) finally move on.

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    2May/120

    Adding Regular Expression Support to SQL Server

    First and foremost, it is worth noting that SQL Server supports .Net assemblies, allowing us to create methods which can be used from SQL.

    .Net assemblies (dll files) can be loaded into the below folder.

    With that out of the way, we can get started on creating a .Net assembly. SQL Server does not support regular expressions natively, so I thought it would be useful to add this functionality as my first assembly.

    To start, we can create a Class Library Solution (I called mine 'TextFunctions') and add a single class (I called mine 'StringUtility'). The following is the code I wrote for my StringUtility class:

    Very simple bit of code, which allows me to check for Regex matches. No big deal. Now we must install this assembly into the database. First we must check to ensure that the database is CLR enabled, so we run:

    Next we will create the assembly. Please note that the dll file must be on the same machine as the database is. You might also want to specify the database you will be adding this to. I added a simple "use master;" to my script.

    Now we can install the RGX function I created.

    Note that the syntax for EXTERNAL NAME is SolutionName.[Namespace.ClassName].MethodName

    That is all! You now have a brand new function which executes C# code and returns a boolean value back! Let us try some simple examples:

    This function can also be used in WHERE clause conditions as follows:

    Enjoy!

    VN:F [1.9.22_1171]
    Rating: 4.3/5 (3 votes cast)
    27Mar/120

    A string literal in the expression exceeds the maximum length of 4000 characters.

    In SSIS, the error "A string literal in the expression exceeds the maximum length of 4000 characters." has proved to be a stumbling block for me in the past because the error message is misleading. In SSIS, when a string variable is set to Evaluate As Expression, then the string being put together by that expression is not actually a string, but rather the SSIS specific DT_WSTR.

    DT_WSTR has a limit of 4000 characters whereas string does not, in spite of what the error message appears to suggest.

    I personally ran into this error on attempting to build a large query through Expression, but there is an alternative solution to using EvaluateAsExpression.

    If instead, we create a Script Task, providing it with all the necessary variables; we could construct a script which exceeds 4000 characters and output it to the string variable. Contrary to expectations, the string is accepted in it's entirety and it can be used normally.

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    27Mar/124

    C# Equivalent for SQL IN

    One of the things that irks the most when reading code is cumbersome and long-winded IF statements to the tune of:

    Naturally, this could be easily typed out as:

    But I am not entirely comfortable with this. Certainly it is infinitely better than trying to cram all those possibilities for a into one line, but there must be a more readable syntax. SQL has the answer with the IN statement. For example:

    I wanted to emulate this syntax in C#, so I wrote the following extension method which effectively mimics the SQL IN syntax:

    or alternatively as Andreas suggested in the comments below:

    What this does conceptually, is incredibly simple. You pass to it a generic List or an array and it simply loops on that array searching for a match returning true or false, depending on whether a match has been found. Conceptually, this is akin to the SQL IN syntax.

    What this means, is that we can now type the above cumbersome IF statement as:

    Which I feel, is a noticeable improvement in the readability of a long-winded collection of OR conditions in a single IF statement.

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    Tagged as: , , 4 Comments