Finally, The Truth on OEM Software

I just read a great article on OEM software, called Buying OEM versions of Windows Vista: the facts.  I am sure the data here could be extrapolated to any OEM software.

The basics with OEM versions of Vista are:

  1. You absolutely can buy OEM versions lawfully.
  2. There is no difference between OEM and non-OEM versions (EXCEPT):
  3. It is tied to your motherboard: You cannot move it to a new computer 3 years from now or upgrade your motherboard.
  4. Once you open the OEM package, you can no longer return it.

Render RadioButtonList as an Unordered List UL

In my latest project, I found the need to use the RadioButtonList which spits out the absolute nastiest HTML to render it unusable (for me anyway). The control gives you to HTML options using the RepeatLayout attribute.

Flow spits out a series of span’s and label’s with BR’s if you don’t specify RepeatDirection of Horizontal. And choosing Table gives you a nicely unaccessible table you can’t format easily. Because of these limitation’s, I have always stayed away from CheckBoxList and RadioButtonList controls.

My solution is to write my own RadioButtonList, but to spit it out as an Unordered List which I can style very easily horizontally or vertically, little or lots of padding, all with CSS.

I unfortunately have been forced to regress back to ASP.NET 1.1 and VB.NET for a little while until the company upgrades to 2.0. Hence why I wrote the following instead of writing a CSS Control Adapter.

Imports System
Imports System.ComponentModel
Imports System.Text
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls

Namespace MyControls

 <DefaultProperty("Text"), _
ToolboxData("<{0}:ULRadioButtonList runat=server />") _
, Description("Creates a RadioButtonList using an Unordered List.")> _
Public Class ULRadioButtonList
Inherits RadioButtonList

 Protected Overrides Sub Render(ByVal writer As HtmlTextWriter)

//"SBE 01/28/2007:  just in case...not sure if this is needed or not
Controls.Clear()

//"SBE 01/28/2007:  not sure why I am leaving quotes as a param...I was just undecided if I would ever need to use " instead of "
Dim inputFormatString As String = "<input id={0}{1}{0} name={0}{2}{0} type={0}radio{0} value={0}{3}{0} {4} />"
Dim labelFormatString As String = "<label for={0}{1}{0}>{2}</label>"

//"SBE 01/28/2007:  if user sets the cssclass, add it to the <ul> tag
If (Not MyBase.CssClass Is Nothing AndAlso MyBase.CssClass <> "") Then
     writer.WriteLine("<ul class="" & MyBase.CssClass & "">")
Else
     writer.WriteLine("<ul>")
End If

//"SBE 01/28/2007:  loop through the dataitems
For index As Integer = 0 To Items.Count - 1
     writer.Indent += 1
     writer.WriteLine("<li>")
     writer.Indent += 1

     Dim inputBuilder As New StringBuilder
     Dim labelBuilder As New StringBuilder
     Dim checked As String = ""

     If (Items(index).Selected) Then
          checked = "checked"
     End If

     inputBuilder.AppendFormat(inputFormatString, """", MyBase.ClientID & "_" & index.ToString(), MyBase.UniqueID, Items(index).Value, checked)
     labelBuilder.AppendFormat(labelFormatString, """", MyBase.ClientID & "_" & index.ToString(), Items(index).Text)

     writer.WriteLine(inputBuilder.ToString())
     writer.WriteLine(labelBuilder.ToString())

     writer.Indent -= 1

     writer.WriteLine("</li>")
     writer.WriteLine()
     writer.Indent -= 1

 Next

 writer.WriteLine("</ul>")

 End Sub

 End Class

End Namespace

Before I started writing this, I found a beautiful website SimplyGold who had already wrote something similar for the CheckBoxList. Thanks for saving me some time!

Now playing: MenomenaMuscle n’ Flo

Add Additional Information to Your Exceptions

When Enterprise Library was called Microsoft Application Blocks, if you wanted to log an Exception, you would write (assuming “ex” is an Exception):

ExceptionManager.Publish(ex)

And if you wanted to log some extended properties you could do something like this:

NameValueCollection customerInfo = new NameValueCollection();
customerInfo.Add("name","scott");
customerInfo.Add("email","blah@blah.com");
ExceptionManager.Publish(ex,customerInfo); 

Now that I am upgrading all legacy code to Enterprise Library 2006 for .NET 2.0, I couldn’t find a way to do this since the only way to log an error is:

ExceptionPolicy.HandleException(ex, "General Policy");

where “General Policy” is the name of the Exception Policy in the config file telling the Block what to do with the exception.

Looking in the config file, I noticed a formatter template with the following at the tail end of the template attribute, “…Extended Properties: {dictionary({key} – {value} )}”.  The formatter template is used to format the exception that is about to be logged somewhere.  Looking through the code for where it loops through this Dictionary, I noticed it accessing an IDictionary of Exception.Data.  Exception.Data?  Where did that come from?

Data is a new .NET framework 2.0 property of the Exception class to allow you to add any user-defined information about the exception.  Nothing more to it:  Just a simple name/value dictionary for your enjoyment.  As if you couldn’t figure it out, my new code would look like:

ex.Data.Add("name","scott");
ex.Data.Add("email","blah@blah.com");
ExceptionPolicy.HandleException(ex, "General Policy");

And as long as you use the default formatter, it will log this data at the end of the FormattedException field.

Community Server Upgrade Hell

This last week I was tasked with upgrading a Community Server 1.1 install to 2.0.  I was expecting a simple process after navigating the seas of .Text to CS 2.0, but I forgot to “knock on wood”.

I ran the upgrade script as indicated in the upgrade instructions and got about 15 separate errors.  I don’t remember what they all were, but there was about three missing fields in various tables that I had to add before the script would complete successfully.

Once that was done, I copied the new web files over the old ones, and tried browsing to ye’ ol’ default.aspx which resulted in a Yellow Page of Death.

I assumed that the CS website wasn’t the problem, and could assume it was my database installation, since I had a gazillion errors on install.  I booted up SQL Profiler and filtered on “TextData” for “%cs_%” and “%aspnet_%”, which would only grab sprocs called in Community Server (all sprocs start with either aspnet_ or cs_).

After starting Profiler, I browsed to default.aspx again and watched what happened.  Each sproc that was called I compared against my scottelkin.com blog and compared the results.  By doing this I found the following three problems:

  1. All my users were missing from the cs_Users table. I was able to reassemble them by looking at the aspnet_users for the MembershipID, and looked through the cs_Posts table to find the UserID. I tried to look at a backup, but that was another stupid problem I ran into…
  2. postconfiguration must not be 0 for each post in the cs_Posts table. I changed them to 11. Not sure why 11 and not 1 or 2 or 3. My blog had 11 for each post, so there you go.
  3. usertime must not be null in cs_Posts, so I set it equal to the postdate.

Find and Delete Duplicates in SQL

We all know we shouldn’t have duplicates in the database. And despite my best efforts, somehow they sneak in from some legacy code, or from the hyper-active-compulsory-submit-the-form-fifty-times-in-five-seconds-bloke.

So now and then I find myself writing the same SQL to track them down. Most solutions online say that you have to use a temporary table or rename tables to get rid of them. This solution works without either, providing my way of eliminating the duplicates is sufficient for your needs.

Here is my code to find duplicates:

SELECT *
FROM users u JOIN (
   SELECT username, min(userid) AS minid FROM users
   GROUP BY username
   HAVING ( COUNT(username) > 1 )
) AS dupusers
ON u.username = dupusers.username
ORDER BY userid DESC

Let’s first look at the nested Select:

SELECT username, min(userid) AS minid FROM users
GROUP BY username
HAVING ( COUNT(username) > 1 )

Because I identify a record by the same username, I group on that. Once grouped on username, I can use Having to check the count for that username. If the count is > 1, then it is a duplicate of another record. I am taking the viewpoint that the first user inserted is the one we are going to keep. All users with the same username after that one is hence a duplicate. I am returning the minimum userid for each duplicate record so I know which one I will not delete later.

This query by itself would find you all the duplicates, but isn’t as handy because you can’t view all the columns of each record.

Now if we look at the full join, I join the two tables together: The nested table is the set of all users that have a duplicate in the database inner joined with the set of all users. This will give us the set of all users that have duplicates.

Ok, so how do we go about deleting the sibling duplicates of a base record and not every record that has a duplicate? In other words, if I have:

ID: 1
username: Scott
MinID: 1

ID: 2
username: Scott
MinID: 1

ID: 3
username: Scott
MinID: 1

I only want to delete records with ID’s of 2 and 3; not the ID of 1. Let’s look at my Delete syntax:

DELETE users
FROM users u JOIN (
    SELECT username, min(userid) AS minid FROM users
    GROUP BY username
    HAVING ( COUNT(username) > 1 )
) AS dupusers
ON u.username = dupusers.username
WHERE u.userid <> minid

The code is the exact same with two exceptions. One, I switched from the Select statement to a Delete. The second is the filter :

WHERE u.userid <> minid

This will delete every userid that isn’t the smallest userid for that group of duplicates. Using my example set above, ID’s 2 and 3 would not equal the minid of 1, and therefore would be deleted, leaving one non-duplicated record.

This code assumes that a row is a duplicate of another if their username is the same. In most cases, a duplicate is only a duplicate if a combination of fields are the same. This can be accommodated by changing the nested Select statement:

SELECT *
FROM users u JOIN (
    SELECT firstname, lastname, min(userid) AS minid FROM users
    GROUP BY firstname, lastname
    HAVING ( COUNT(*) > 1 )
) AS dupusers
ON u.firstname = dupusers.firstname AND u.lastname = dupusers.lastname
ORDER BY u.lastname, u.firstname

This is a lame example since there can be many people with the same name, but you get the picture. In reality, I have had to use this technique when looking for duplicate addresses. For that I grouped on address, city and zip.

So there you go…a way to nuke duplicates without using temp tables.

Using Case in Order By with Group By

Wow, that’s a mouthful. This is what I am trying to convey: When you do a Group By with Rollup, what happens is that the rolled up column shows null, and won’t necessarily be ordered at the bottom. For example:

select
    firstname
    , count(*) as total
from AdsEntered ads
join tblStaff s on s.staffid = ads.staffidgroup by firstname
with rollup
order by
total desc

This would output:

Groupbynull

Now, I want the person with the highest total at the top, but I certainly don’t want the the total row being first. So the goal is to still sort descending, yet stick the total row at the bottom:

select
    isnull(firstname, "TOTAL") as firstname
    , count(*) as total
from AdsEntered  ads join tblStaff s on s.staffid = ads.staffid
group by firstname
with rollup
order by
case when firstname is null then 0
else 1 end  desc

This would yield:

Orderbycase1

First, I used isnull(firstname, “TOTAL”) to get rid of the ugly null. Pretty simple.

Now look at the case statement in the order by. I am saying, “when the firstname column is null, then treat it like it is 0, else treat it like it is 1. Then order it descending, so the field with the 1 will go last.” Notice I wrote when firstname is null, not when firstname = ‘TOTAL’. To check for ‘TOTAL’ you would have to apply the entire line isnull(firstname, “TOTAL”) = “TOTAL”.

Now that we got the total line in the right place, we need to order all the “0” rows descending. We do this by applying another order by on the total column.

select
    isnull(firstname, "TOTAL") as firstname
    , count(*) as total
from AdsEntered  ads join tblStaff s on s.staffid = ads.staffid
group by firstname
with rollup
order by
case when firstname is null then 0
else 1 end  desc
, total desc

Yielding:

Orderbycase

Cool, huh?

.Text to Community Server 2.0

I made the leap and upgraded my blog to Community Server (CS) 2.0.  It took about 4 hours researching, downloading, uploading, converting and fixing. 

I couldn’t find a way to directly upgrade my .Text Version .95 directly to CS 2.0, so I decided to first upgrade from .95 to CS 1.1, then use the standard upgrade path from CS 1.1 to CS 2.0. Here is what I did in detail:

  1. Downloaded CS 1.1.
  2. Created a test IIS website to host my new site, and a new empty database.
  3. Read through the CS 1.1 Readme file and created a CS 1.1 Database. For some reason I kept getting SQL errors when using the wizard, so ran InstallCommon.sql, InstallMembership.sql, InstallProfile.sql, and InstallRoles.sql myself. I then rerun the wizard and it finished.
  4. Finished the CS 1.1 Readme file instructions.

    Special Note: My .Text install used a single site configuration, meaning my blog was directly off the root directory. There are ways to tweak CS 1.1 to also have this, but because we are directly upgrading to CS 2.0, you don't need to do this. We will attack this at the end.

  5. Verified that the installation worked by browsing to my CS 1.1 default page.
  6. Backed up my CS 1.1 database, and .Text database.
  7. Downloaded and ran Kevin Harder's DotText-CS-Converter Utility (Version 2.2).
  8. Read the utilities "READ_ME_FIRST.HTML" and followed it to the "T".
  9. I got an error that “IsActive” column doesn’t exist, but all you have to do is add that bit column to your blog_config table and make sure it gets populated.  He explains this in his blog post.
  10. Tested my CS 1.1 website and verified all my old posts were showing up.
  11. Backed up my new CS 1.1 database.
  12. Downloaded the Community Server 2.0 Web Installer to upgrade from CS 1.1 to CS 2.0.
  13. Again, read and completed each step of the the Readme file.
  14. Tested my (now) CS 2.0 website.  Keep in mind my http://www.scottelkin.com site is still working and pointing to the scottelkin database.  My test site is working at http://localhost:81 and mapped to my temporary scottelkin2 database.
  15. Backed up my CS 2.0 website and CS 2.0 database.
  16. Now I want to move my blog from where it is by default:

    http://scottelkin.com/blogs/blog_0/archive/2006/02/15/7456.aspx

    and switch it to the root at:

    http://scottelkin.com/archive/2006/02/15/7456.aspx

    This will mirror my .Text installation and make sure that all old links to my site don't break.

  17. For this, I followed Dan Bartels – Blogging At the Root.
  18. The one question I was unsure of was what my “defaultApplicationKey” was.  Because I had tested my installation prior and saw “blog_0” in the querystring, I guessed that and was correct.  All the other settings were by the book.
  19. At this point, I decided to make my site live.  I restored my scottelkin2 database into my scottelkin database.  Funny enough, I had never tried that before.  It just took me actually reading the column headings of the restore dialog and it went very smoothly.  I had to make sure it showed “scottelkin2_data” was being restored into “C:\data\scottelkin_data.mdf”.
  20. I then stopped the WWW Publishing Service, renamed my live site to “scottelkin_old” and changed the new one to “scottelkin”.
  21. Because I changed databases, I had to update my web.config to point to the new one.
  22. I also changed my SiteURL in the cs_Sites table to my fully qualified “www.scottelkin.com”.  I doubt that mattered, but did it just in case.
  23. At this point my site was hanging externally, but worked locally.  This was caused by my ISAPI_Rewrite extension I installed to rewrite all http://scottelkin url’s to http://www.scottelkin.com. Community Server was rewriting all http://www.scottelkin.com url's to http://scottelkin.com, then the extension would re-write them back. Ouch. When I nuked that, all worked fine, except now Community Server was re-writing to scottelkin.com which I didn't want..  This was why I was using the extension to begin with, as Google will rank your www and non-www url’s separately, yielding lower PageRank all around.
  24. After a little hunting, I made a switch in the cs.config file and now all url's correctly point to my www domain. 

And now to start customizing…

Remembering Regsvr32

I just tried to open Sauce Reader, my blog reader of choice, but all it gave me was an error and an option to close.  After digging through the error message, I found the message, “Class not registered”.

Ok, easy enough.  I opened a command prompt and started to register each dll in the Sauce Reader directory.  But wait, it wouldn’t let me run Registration Server.  I tried:

  1. regsrv
  2. regsrv32
  3. regsrv32
  4. regsvr
  5. regsvr32 (bingo)

I mean, that was pretty lame.  In my defense, I seriously think the first season of The OC I was forced to sit through ejected this out of my head.

Emulating Digg Spy using AJAX.NET

In my latest project, I need to build a lead system for our sales people. It will work very similarly to the digg.com/spy application, so I took advantage of their model in order to learn AJAX.NET.

I decided to go with AJAX.NET, rather than the new Anthem, just to get my feet wet. I will make it work in Anthem next.

This is what my example will look like:

Beforesubmit
and after you click the button:

Aftersubmit
Using AJAX, it went and grabbed a new item, and inserted it to the top row, and removed the bottom row. Digg’s Spy is obviously nicer, but it does essentially the same thing.

Yes, there are TONS of ways I can optimize this and make it slimmer and nicer, but his was a proof of concept. I know I didn’t have to use a DataSet, but since I wanted to see it work, I used it. I also didn’t care about formatting a date in javascript.

Here is my code behind code:

public class test : System.Web.UI.Page
{
   private void Page_Load(object sender, EventArgs e) {
     Ajax.Utility.RegisterTypeForAjax(typeof(test));
   }

   [Ajax.AjaxMethod()]
   public DataSet GetLeads() {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable("MyTable");
        ds.Tables.Add(dt);
        dt.Columns.Add("ID", typeof(int));
        dt.Columns.Add("Firstname", typeof(string));
        dt.Columns.Add("Date", typeof(DateTime));

        dt.Rows.Add(new object[] { 1, "steve", DateTime.Now });
        return ds;
   }

And here is my sample for the .aspx file:

<html>
<head>
<style>

.lead {
clear: both;
border-top: 1px solid #ccc;
}

#mostrecent { font-weight:bold; }

.column1, .column2, .column3 {
float: left;
display: block;
text-decoration: none;
}

.header span {
font-weight: bold;
background:#eee;
}

.column1 {
width: 6em;
text-align: center;
/*border: 1px solid green;*/
}

.column2 {
display: block;
width: 10em;
/*border: 1px solid blue;*/
}

.column3 {
display: block;
width: 20em;
/*border: 1px solid yellow;*/
}
.button {
clear: left;
}
</style>
</head>
<body> 

<form id="Form1" method="post" runat="server">
<div id="leads">
   <div class="header">
      <span class="column1">ID</span><span class="column2">Name</span><span class="column3">Date</span>
   </div>

   <div class="lead" id="mostrecent"><span class="column1">54</span><span class="column2">scott</span><span class="column3">1/1/2006</span></div>
   <div class="lead"><span class="column1">22</span><span class="column2">peter</span><span class="column3">1/2/2006</span></div>
   <div class="lead"><span class="column1">12</span><span class="column2">sam</span><span class="column3">1/3/2006</span></div>
   <div class="lead"><span class="column1">44</span><span class="column2">sally</span><span class="column3">1/4/2006</span></div>
   <div class="lead"><span class="column1">51</span><span class="column2">paul</span><span class="column3">1/5/2006</span></div>
</div>
<div class="button">
   <input type="button" onclick="buttonClick()" value="blah"/>
</div>

<script language="javascript">

function buttonClick() {
   test.GetLeads(GetLeads_CallBack);
}

function GetLeads_CallBack(response){

//SBE 02/08/2006:  create the div element
var newLead = document.createElement('div');

//SBE 02/08/2006:  set its class to lead
newLead.setAttribute('class', 'lead');

var leadTable = document.getElementById('leads');
var rows = leadTable.getElementsByTagName('div');
var numrows = rows.length;
var html = "";

//SBE 02/08/2006:  make sure we got a dataset to loop through
var ds = response.value;
if(ds != null && typeof(ds) == "object" && ds.Tables != null){
   var s = new Array();

   s[s.length] = "<span class='column1'>" + ds.Tables[0].Rows[0].ID + "</span>";
   s[s.length] = "<span class='column2'>" + ds.Tables[0].Rows[0].Firstname + "</span>";
   s[s.length] = "<span class='column3'>" + ds.Tables[0].Rows[0].Date + "</span>";

   html = s.join("");
}
else{
   alert("Error. [3001] " + response.request.responseText);
}

//SBE 02/08/2006:  assign the html to the new lead
newLead.innerHTML = html;

oldItem = document.getElementById('mostrecent');
leadTable.insertBefore(newLead, oldItem);
newLead.setAttribute('id', 'mostrecent');
oldItem.setAttribute('id', '');

// remove last row
leadTable.removeChild(rows[rows.length-1]);

}
</script>
</form> 

</body>
</html>