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.

I find myself using .net data containers in many web applications.  Binding controls such as asp:Repeater and asp:DataList to data sources can make displaying your data more manageable.  In a few of those instances I need to have some of the data display in a little different manner depending on that particular info.

For example, a “sold” image could be shown if the status of a data item warrants it.  If the status is anything else no extra HTML is rendered.

<%# (DataBinder.Eval(Container.DataItem, "Status").ToString() == "Sold") ?
<img src='/images/sold.gif' />" : "" %>

Instead of making a server-side control in my .aspx page then having my code behind find it in some sort of databound function, I find it cleaner to use an inline C# IF statement for simple display changes such as these.

In the sample code below, we are making an anchor link. The data in the “Type” column from the database describes if the data is a URL or an email address.  Our inline IF statement has this structure:

If the Type equals “Website” then add nothing to the data.  Otherwise, the data is an email address, so make a “mailto:” link out if it.

<asp:Repeater id="rptAddress" runat="server">
<ItemTemplate>

<a href="<%# (DataBinder.Eval(Container.DataItem, "Type").ToString() == "Website") ?
 "" : "mailto:" %>   <%# DataBinder.Eval(Container.DataItem, "Address")%>" >
<%# DataBinder.Eval(Container.DataItem, "Address")%>
</a>

</ItemTemplate>
</asp:Repeater>

Just remember, the C# syntax for an inline If uses the question mark and a colon:

<% (condition) ? "true" : "false" %>