I had a spreadsheet with tens of thousands of mailing addresses. One of the columns was  the ZIP code for each address row.  Some of those ZIP codes were in the ZIP + 4 format (12345-6789) and others were not.  Those others were 3, 4 or 5 digit numbers.  When the data was put into the spreadsheet, Excel removed the leading zeroes from some ZIP codes (like “00345” truncated to “345”).

I used the Import functionality in SQL Server Management Studio 2005 to put the data from the Excel spreadsheet into a table.  Without modifying the spreadsheet data the import would place a NULL into each row that had a 3,4 or 5 digit ZIP code, while keeping the ZIP + 4 codes correctly.

To get both “5-Digit” and “Zip Plus Four” ZIP codes into the same database column I needed to make Excel see them as text. To do this I made a new column next to my ZIP Codes with this style of formula:                =TEXT(B2,”00000″)

This causes the data to have at least 5 digits and leading zeroes if less than five  digits. Next I copied this new column and then used “Paste Special” with the “Values” option to replace my original ZIP code column. Saving the spreadsheet and executing a SQL Server import of the XLS file, all ZIP codes were then correct.

I also found that using the “Format Cells” functionality with the Special “Zip Code” setting only changes how the data appears. It does not change the underlying data. For example “345” shows as “00345” but if you look at the actual data, it remains unchanged. So just doing Excel formating did not work when importing in to a SQL Server database.

By George Zheng

When we deploy one of our web sites to a 64 bit web server, we get following error: “Could not load file or assembly ‘apachefop.net’ or one of its dependencies. An attempt was made to load a program with an incorrect format.”

This web site is using fop.net to generate the PDF for printing. After investigation, we realize the fop.net can’t run on IIS with 64-bit version of ASP.NET.

IIS 6.0 on a 64-bit hardware supports both the 32-bit version of ASP.NET and the 64-bit version of ASP.NET. However IIS 6.0 does not support running both modes at the same time on a 64-bit version of Windows.  Here are the steps to run the 32-bit version of ASP.NET 2.0 on IIS:

1. Click Start, click Run, type cmd, and then click OK.
2. Type the following command to enable the 32-bit mode:
   cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
3. Type the following command to install the version of ASP.NET 2.0 (32-bit) and to install the script maps at the IIS root and under:
   %SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
4. Make sure that the status of ASP.NET version 2.0.50727 (32-bit) is set to Allowed in the Web service extension list in Internet Information Services Manager.

Useful Links

How to switch between the 32-bit versions of ASP.NET 1.1 and the 64-bit version of ASP.NET 2.0 on a 64-bit version of Windows

The past few weeks have been challenging in an interesting and fun way. We are on track to release version 2.6 of our Content Management System and finally decided it was time to overhaul our security framework. We are moving from a simple role based architecture to a framework that supports the following:

• Roles
• Permissions
• Ability to assign Permissions to Roles
• Ability to assign Supplemental Permissions to Users external of Roles
• Ability to assign Roles to Users
• Ability to grant/deny access down to the control level
• Reverse capability of locking individual elements down by assigning Roles/Permissions to user created elements

In working on this there have been LOTS of questions as you can imagine. Here are a few resources I found useful and/or interesting while working on this implementation.

• I am a regular reader of the .NET Security Blog and found the CAS and CLR discussions pertaining to .NET 4 interesting.
• I found a nice way to get a flattened hierarchy of controls on a page over at the Vault of Thoughts. We implemented something similar but with a few additions.
• Here is a good read on general role based security.

Derek Bemis