Skip to main content

Posts

Find All Characters In A Column

Here's an updated way to find all characters in a column. Basically, I loop thru the ASCII readable characters and see if they are present in the column. I did skip A-Z and a-z. If you want it to include A-Z and a-z, just comment out the If statements at the bottom of the loop.

DECLARE @ColumnName varchar(200) = 'Text'
DECLARE @TableName varchar(200) = 'Rows'
DECLARE @SchemaName varchar(200) = 'SourceData'
DECLARE @Sql varchar(max)
DECLARE @MaxLength int = 126
DECLARE @Iterator int = 32

CREATE TABLE #AllChars
(
    ColChar CHAR(1),
    Instances int
)

WHILE @Iterator < @MaxLength
BEGIN
    SET @Sql = 'INSERT INTO #AllChars (ColChar, Instances) SELECT CHAR(' + CAST(@Iterator as varchar) + '), COUNT(*) FROM ' + @SchemaName + '.' + @TableName + ' WHERE CHARINDEX(CHAR(' + CAST(@Iterator as varchar) + '), Text) > 0'

    EXEC (@Sql)

    SET @Iterator = @Iterator + 1

    -- Skips A-Z
    IF @Iterator = 65
        SET …
Recent posts

Back To Basics - Validating a date

Recently, a lot of my time has been spent working on systems written by someone else. One of the biggest problems I see in these systems is a lack of basic validation. Limiting the length of strings, requiring values and making sure values fall into the allowed range.

The one that annoys me the most is date validation. Let's be clear, I'm talking about some basic rules to make insure that a date is not just a valid date but is a reasonable value for the data element. January 1, 1900 is a valid date except if we're talking about a birth date for living people. The current oldest living person is 116 years old and there aren't that many people over 100. Ask yourself this question, "Are they going to be in my system?" Probably not, so don't allow it.

This simple rule will keep a lot date typos out of your system. Remember, dates come in different formats MM/dd/yyyy or dd/MM/yyyy or yyyy/MM/dd. Some users will enter zeros such as 08/02/1991 while others might…

VS Removes Usings and Can't Find Types

So I was working tonight and ran into a goofy issue with Visual Studio 2013. I had it Remove and Sort Usings for the entire solution. It did but along the way I kept getting a "Your project doesn't compile...Do you want to continue" message. Figuring it was some small issue with a quick fix, I hit Yes. Doh!! When it got done, I had a bunch of "The type or namespace name...could not be found (are you missing a using directive or assembly reference?)" errors. I also had a bunch of metadata errors but they weren't the problem.

When I'd add the needed Using, VS would remove it when I'd save and close the file. Wait a minute, I needed that reference and VS should have known that!! If I added the Using and did a Rebuild, it would leave it there and I could close the file. If the file is open it would give me the same error, red squiggles and Remove Usings would take it back out.

There were two projects that had issues, Entities and Infrastructure. Didn…

JavaScript runtime error: '$' is undefined

If you’re developing an MVC4 app and run into this error, you probably haven’t placed your scripts in the Script section.Looking at the _Layout.cshtml file, you’ll see one of the last lines is@RenderSection("scripts", required: false)which will render a section titled scripts. The following is a sample:@section Scripts { <script type="text/javascript"> $(function () { alert("JQuery loaded and working!!"); }); </script> }Here’s a link to the Gu man’s discussion of sections.Cost me about 3 hours. Hope it saves you a few!!!

First NuGet Public Package: States & Provinces

I recently had to code a drop down for a state field on an entry form. Didn’t take long and not a big pain but I thought, why is there no NuGet package for this. So,,,,First, states and provinces are generically termed political subregions or geographic subdivisions. There is an ISO standard so we’ll follow that.First thing we need is a representation of the subregion./// <summary>/// Represents a political subregion such as a state or province./// </summary>publicclass SubRegion { /// <summary>/// Gets or sets the name of the subregion./// </summary>publicstring Name { get; set; } /// <summary>/// Gets or sets the ISO-3166 code for the subregion./// </summary>publicstring IsoCode { get; set; } /// <summary>/// Gets or sets the standard abbreviation for the subregion./// </summary>/// <remarks>/// For the US, this is the USPS 2 character abbreviation./// </remarks>publicstring Abbreviation { get; set; } ///…

Migrating Legacy Apps to the New SimpleMembership Provider

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.UserProfileContains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.webpages_MembershipStores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.webpages_OAuthMembershipStor…

Windows 8 Keyboard Shortcuts

There are numerous posts on the keyboard shortcuts in Windows 8. I’m not going to regurgitate those. Here are the common things you’ll want to do.Shut Your Computer Off, Network / Wi-Fi, Volume, Keyboard Press Windows+I which brings up the Settings bar (below left). Search, Change Application Settings, Manage DevicesWindows+C which brings up the Charms bar (above right). Want to find a song or artist in Music, useThe rest of the storyIf you want to read about other short-cuts and print a handy-dandy cheat sheet, visit the Windows Experience Blog.IE 10 Want to get the most out of IE10, then How-To Geek’s The Best Tips and Tricks for Getting the Most out of Internet Explorer 10 is exactly what you need. There’s also Internet Explorer 10 Shortcut Keys In Windows 8 which has some Windows 8 stuff also.