A simple Formatted ToolTip text on hover

The task was to add a formatted “ToolTip” message when hovering on a “More” link.  On a certain report, some of the names got pretty long.  Instead of wrapping text on the report, the desire was to simply truncate the name, then allow the user to see the entire name by hovering over the “More” link.  However, we wanted more than just the built-in ToolTip look.

I’ve seen JavaScript implementations of this, but I was looking for a simpler solution.  This is what I came up with:

The Mark-up

<td>
    <%# (Eval("Name").ToString().Length > 65) ? Eval("Name").ToString().Substring(0, 60) + "..." : Eval("Name")%>
    <span>
        <a href="#"><%# (Eval("Name").ToString().Length > 65) ? "More" : "" %></a>
        <span><%#Eval("Name")%></span>
    </span>
</td>

The CSS

.showonhover .hovertext { display: none;}
.showonhover:hover .hovertext {display: inline;}
a.viewdescription {color:#999;}
a.viewdescription:hover {background-color:#999; color: White;}
.hovertext {position:absolute;z-index:1000;border:1px solid #ffd971;background-color:#fffdce;padding:11px;width:150px;font-size: 0.75em;}

Comment

The key is the first two lines of the CSS, specifically hiding the .hovertext node until the mouse hovers over the .showonhover node.  This is a pretty simple solution, but it wasn’t immediately apparent to me, so I thought I’d share it with others who may have the same initial mental block that I had.

Advertisements

Extended CheckBoxList Control

A common task in programming is to retrieve all of the selected items or values from a CheckBoxList control. It is unfortunate that no native properties provide this already. However, extending the CheckBoxList to provide these is simple enough.

The extension I created provides four new properties:

  • SelectedValuesString – A comma-delimited string of the selected values.
  • SelectedValuesAsIntegers – An Integer List of the selected values.
  • SelectedValuesAsStrings – A string List of the selected values.
  • SelectedItemTextAsStrings – A string list of the text for the selected items.
public class ExtendedCheckBoxList : CheckBoxList
{
    public string SelectedValuesString
    {
        get
        {
            return String.Join(",",SelectedValuesAsStrings);
        }
    }

    public IEnumerable SelectedValuesAsIntegers
    {
        get
        {
            int temp;
            return this.Items.Cast<ListItem>()
                 .Where(i => i.Selected && int.TryParse(i.Value,out temp)).Select(i => int.Parse(i.Value));
        }
    }
    public IEnumerable SelectedValuesAsStrings
    {
        get
        {
            return this.Items.Cast<ListItem>().Where(i => i.Selected).Select(i=>i.Value);
        }
    }

    public IEnumerable SelectedItemTextAsStrings
    {
        get
        {
            return this.Items.Cast<ListItem>().Where(i => i.Selected).Select(i => i.Text);
        }
    }
}

Registration Code Generation

I was recently assigned the task of creating unique registration codes for a product. The requirements were as follows:

  • 12 digits long
  • 3 groups of 4, separated by hyphens
  • No duplicates, of course
  • Easy for anyone to recognize and enter

I began by using the GUID.NewGuid() method. This gave me a long hexadecimal string of random characters between 0 and 9, a-f. By taking various substrings from this and then making sure there were no collisions with any existing registration code, I could have a very randomized registration code generator.

However, upon further reflection–and recalling difficulty I myself have had in the past trying to enter the right codes–I came up with the following issues:

  • It’s really difficult to distinguish between a zero and the letter “O”. The people trying to enter the registration code would not know that it was generated from a GUID that only had the letters A-F, so I wanted to eliminate this confusion for the user.
  • It’s really difficult to distinguish between the lowercase “L” -“l” and the number one (“1”) in some fonts.
  • You probably want to avoid some obvious combinations in the code like “KKK” or “666”. Even though our code would contain groups of 4 characters, it seemed best to still avoid those.

We opted to make the registration code all upper case. We also decided to change the ones and zeroes to “h’s” and “k’s. We skipped “g” since it goes “below the line” in the lower case version. We also skipped “i” since that can look like a lowercase “l” or a “1” if, for whatever reason, the printing isn’t clear. So, with all of that, here’s the code:

public string CreateRegistrationCode()
{            
    string code = "";
    while (code == "") //repeat until you don't get a collision
    {
         //replace zeroes and ones to eliminate confusion between o's and L's.  
         //Also get rid of the hyphens in the GUID
         string newGuidString = Guid.NewGuid().ToString().Replace("-", "").Replace("0", "h").Replace("1", "k");
         
         code = newGuidString.Substring(0, 4) 
                 + "-" + newGuidString.Substring(4, 4) 
                 + "-" + newGuidString.Substring(8, 4);
        
         code = code.ToUpper().Replace("KKK", "HHH").Replace("666", "777"); // just to avoid an undesirable code

         var codeExists = (from r in DataContext.Registrations
                                  where r.Code == code
                                  select r).Any(); //test to see if the registration code already exists.  

         if (codeExists)
               code = "";
    }

    return code; 
}

Converting Names to Mixed Case/Camel Case in SQL Server

I was recently given the task to convert names in a database to mixed case.  Many of them were already in mixed case, so I wanted to leave those alone and just focus on the ones that were in all caps or all lowercase.  No automated conversion could be perfect, but this got me pretty close.  To view a post on determining mixed case in Sql Server, click here.

Here’s my strategy:

  • If the name is already in mixed case, trust that it’s right.
  • If the name is not in mixed case, then do the following:
    • Trim up the name to eliminate white space
    • Account for the names that start with “Mc” like “McDavid”
    • Account for names with apostrophes like O’Reilly
    • Account for hyphenated names (married names) “Anderson-Johnson”
    • Account for multiple word names like “La Russa”
    • Make sure suffixes included in the names field are capitalized appropriately

Here’s the code:

CREATE FUNCTION [dbo].[GetCamelCaseName]
(
	@Name varchar(50)
)
RETURNS VARCHAR(50) WITH SCHEMABINDING
AS
BEGIN				
			-- Capitalize the first letter and make the rest lowercase
			SELECT @NameCamelCase =	UPPER(SUBSTRING(@Name, 1, 1)) +	LOWER(SUBSTRING(@Name, 2, 50))

			-- McDavid, McReynolds, etc. --> 1st character Uppercase, 2nd Lower, 3rd Uppercase, the rest lower
			IF (@NameCamelCase LIKE 'mc%')
				SELECT @NameCamelCase =	UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 1)) + UPPER(SUBSTRING(@Name, 3, 1))  + LOWER(SUBSTRING(@Name, 4, 50))

			-- Hyphenated Names "Anderson-White" --> Capitalize after the hyphen
			IF (@NameCamelCase LIKE '%-%')
				SELECT @NameCamelCase =	SUBSTRING(@NameCamelCase, 1, CHARINDEX('-', @NameCamelCase)) + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 2, 50)

			-- O'Reilly, O'Neil, etc. --> 1st and 3rd upper case, the rest lower
			IF (@NameCamelCase LIKE '%''%')
				SELECT @NameCamelCase = SUBSTRING(@NameCamelCase, 1, CHARINDEX('''', @NameCamelCase)) + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('''', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('''', @NameCamelCase) + 2, 50)

			-- I found some compound last names in parentheses in my sample --> capitalize after '('
			IF (@NameCamelCase LIKE '%(%')
				SELECT @NameCamelCase = SUBSTRING(@NameCamelCase, 1, CHARINDEX('(', @NameCamelCase) - 1) + '('  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('(', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('(', @NameCamelCase) + 2, 50)


			-- Hyphenated Names "De La Hoja" --> Capitalize after each space (do it twice for names with three words)
			--     Change the space to '?' each time (so you actually get to the second space), then replace the '?' with ' '
			IF (@NameCamelCase LIKE '% %')
				SELECT @NameCamelCase =	SUBSTRING(@NameCamelCase, 1, CHARINDEX(' ', @NameCamelCase) - 1) + '?'  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 2, 50)
			IF (@NameCamelCase LIKE '% %')
				SELECT @NameCamelCase =	SUBSTRING(@NameCamelCase, 1, CHARINDEX(' ', @NameCamelCase) - 1) + '?'  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 2, 50)

			SELECT @NameCamelCase = REPLACE(@NameCamelCase, '?', ' ')

			-- Suffixes and other miscellaneous things			
			SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' sr', ' Sr')			
			SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' jr', ' Jr')			
			SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' ii', ' II')
			SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iii', ' III')
			SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' DE ', ' de ')
			SELECT @NameCamelCase = REPLACE(@NameCamelCase, 'macdonald', 'MacDonald')


			if (@NameCamelCase LIKE '% iv') -- just do names that end in 'IV' so you don't change "Ivy" to "IVy"
				SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iv', ' IV')

			if ((@NameCamelCase = 'i') or (@NameCamelCase = 'ii') 
or (@NameCamelCase = 'iii') or (@NameCamelCase = 'iv'))
				SELECT @NameCamelCase = UPPER(@NameCamelCase)

			-- Return the result of the function
			RETURN ISNULL(@NameCamelCase, '')
			
		END

	RETURN ISNULL(@NameCamelCase, '')

END

Comparing Uppercase and Lowercase in SQL Server

Occasionally it is necessary to really tell whether or not “THIS” is equal to “this” or “This”, including the cases.  For me, this came up when trying to assess whether a name in the database was already in mixed case or not.  If it was upper, I needed to convert it to mixed case, but if it was already in mixed case, I wanted to leave it alone.

If you just do a straight compare, case is not taken into account.  So, we need to somehow compare the actual character values of these.  It turns out that if we cast them to varbinary values, the uppercase letters do get different values than the lowercase letters that we can compare and determine the difference.

So, in my case, I decided that if the first character was in lower case or the second character was in upper case, I was probably dealing with a name that was in either all caps or all lowercase.  This is how I tested for that:

SELECT *
FROM Person
WHERE CAST(SUBSTRING(LastName, 1,1) as varbinary) = CAST(SUBSTRING(LOWER(LastName), 1,1) as varbinary)
	OR CAST(SUBSTRING(LastName, 2,1) as varbinary) = CAST(SUBSTRING(UPPER(LastName), 2,1) as varbinary)

Basically, it checks to see if the varbinary representation of the first character is equal to the varbinary representation of the uppercase value of that character OR if the varbinary representation of the second character is equal to the varbinary representation of the lowercase of that character.

This allowed me to focus in on converting only the names that were not already in mixed case/camel case.

This also works well in a function:

CREATE FUNCTION [dbo].[IsUpperCase]
(
	-- Add the parameters for the function here
	@Char char
)

RETURNS BIT WITH SCHEMABINDING
AS
BEGIN

	DECLARE @IsUpperCase BIT

	if (CAST(@Char AS VARBINARY) = CAST(UPPER(@Char) AS VARBINARY))
		SELECT @IsUpperCase = CAST(1 AS BIT)
	ELSE
		SELECT @IsUpperCase = CAST(0 AS BIT)

	RETURN @IsUpperCase
END

CREATE FUNCTION [dbo].[IsLowerCase]
(
	-- Add the parameters for the function here
	@Char char
)

RETURNS BIT WITH SCHEMABINDING
AS
BEGIN

	DECLARE @IsLowerCase BIT

	if (CAST(@Char AS VARBINARY) = CAST(LOWER(@Char) AS VARBINARY))
		SELECT @IsLowerCase = CAST(1 AS BIT)
	ELSE
		SELECT @IsLowerCase = CAST(0 AS BIT)

	RETURN @IsLowerCase
END

SQL Select Multiple Columns into Comma-Separated List

I needed to select a field from multiple rows in one table into one column in another table.  I wanted the one column to be a comma-separated list as well.

One way to do this would be to use cursors, but the following post showed an easier way using XML Path:  http://bytes.com/topic/sql-server/answers/431513-getting-data-multiple-rows-into-one-column.

I ended up doing something like this:


SELECT  State.Name,
       (SELECT  City.Name AS [text()]
        FROM City
        WHERE City.StateID = State.StateID  && City.Population > 100000
        FOR XML Path(")) AS LargeCities
FROM State

Of course, this just gives me the list of city names all run together like this:  “DenverBoulderPuebloColorado Springs”.

I wanted to separate these by commas so I changed the query to “Select City.Name + ‘, ‘ AS [text()], but this gives me an extra comma on the end like this: “Denver, Boulder, Pueblo, Colorado Springs, “.

Of course, I could have used SUBSTRING(“the inner select statement”, 0, LEN(“the inner select statement”) – 2) and been fine, but I didn’t want to rerun the query and it would be harder to ready anyway.

What I ended up doing was inverting the positioning of the “City.Name” and the “, “.  This gave me the following:  “, Denver, Boulder, Pueblo, Colorado Springs”.

Then, all I had to do with the SUBSTRING command was this:  SUBSTRING(“the inner select statement”, 2, 10000).  The “2” as the starting position got me past the first comma, and there was no trailing comma to worry about, since each city was preceded by a comma rather than followed by a comma.  So, the final query looks like this:


SELECT  State.Name,
        SUBSTRING((SELECT ', ' +  City.Name AS [text()]
                   FROM City
                   WHERE City.StateID = State.StateID  && City.Population > 100000
                   FOR XML Path("))), 2, 10000) AS LargeCities
FROM State

and would give the following results:

State Name      LargeCities
Colorado               Denver, Boulder, Pueblo, Colorado Springs

This strategy could be applied to regular code as well when building a comma-separated list.


string list = "";
for (int i = 0; i < items.count; i++)
{
   list += ", " + items[i].Name;
}
list = list..Substring(2);

Really Nice Looking Export to Excel using Table Formatting

Inevitably, someone is going to want your data exportable to an Excel format, no matter how many different ways you let them look at right on your site–and they’re going to want it to look really nice.

If you’re just going to a CSV format, it’s pretty easy to do, but if it opens in Excel, none of the cells are formatted initially.  It can look good eventually, but you have to manually format it after the export.  It would sure be nice if you could format it better from the code itself.  For me, I thought it would be too tedious and time-consuming to actually create the Excel Spreadsheet.  I wanted something quicker.

Using a DataGrid for the bulk of the actual data works well as it renders as a table, and Excel seems to work well with table formats.  I used an HtmlTextWriter for this purpose.

HtmlTextWriter hw
dg.RenderControl(hw)

However, the DataGrid wasn’t the only thing I wanted to export.  What I wanted was a nice big heading that was bolded, nice subheadings, nice spacing, and a bunch of things like that.  What I found is that if I just wrote table structure syntax, I could do basically everything I wanted.

What I did here was to write a large, centered heading that spanned 8 columns (the entire width of my report).  Both the “th” and the “h1” tags impact the way it renders in Excel.  I added the colspan and alignment attributes on the th tag, and it worked great.  Then I added a blank link by simply writing a br tag.  Here’s the code:

hw.WriteLine("<table><tr><th colspan=\"8\" align=\"center\"><h1>My Nice Looking Report</h1></th></tr></table>");
hw.WriteLine("<br>");

The rendering just follows down the page pretty nicely.  I added subheaders with h2 tags, and I played around with color and other attributes as well.  Then, after you export to Excel, the moment you open it up, all the formatting is there.  It looks pretty nice–particularly so in product demos.  Even if clients opt for csv format so they can put it into reporting software, the look that this provides gives great curb appeal.

Here’s the code for the sub heading and the legend for the report.  The term takes up two cells, and the description is confined to the other 6 cells (just because the total width was 8 cells/td’s).

hw.WriteLine("<table>");
hw.WriteLine("<tr><td colspan=\"8\"><h3>Report Legend</h3></td></tr>");

hw.WriteLine("<tr><td colspan='2'>Incomplete Evaluations</td><td colspan='6'>Evaluations assigned but not been completed.</td></tr>");
hw.WriteLine("<tr><td colspan='2'>Completed Evaluations</td><td colspan='6'>Evaluations assigned that have been completed.</td></tr>");
hw.WriteLine("<tr><td colspan='2'>Request Reviews</td><td colspan='6'>Total number of requests for reviews.</td></tr>");
hw.WriteLine("<tr><td colspan='2'>Unfulfilled Request Reviews</td><td colspan='6'>Total number of requests not yet reviewed.</td></tr>");
hw.WriteLine("<tr><td colspan='2'>Request Responses</strong>/td><td colspan='6'>Total number of request review responses. </td></tr>");

hw.WriteLine("</table>");
hw.WriteLine("<br>");

Again, this works really well for me. It allows me to easily control the format without actually creating an Excel Spreadsheet in the code itself.