Convert SQL Server nText to HTML By Robbe D. Morris Printer Friendly Version

Robbe & Melisa Morris

Have you ever needed to take the contents of a SQL Server nText column in a recordset and simply display the contents nicely in HTML?  Normally, all of the spacing and paragraph breaks are removed and you wind up with one big chunk of text that looks horrible.  I've also found some of the normal solutions utilizing the <pre> tag or simply replacing end of line control characters disappointing.  Here, I've put a few touches on the end of line character approach.  This function assumes that the formatted text will reside in an HTML table.  So, you'll need to pass in the number of columns the text should span.

I'd like to highlight a few items.  Setting the table width to a fixed pixel value works a little better than a dynamic table width.  Also included here is an example included from one of our Tips & Tricks submittals that demonstrates how to convert a URL into an HTML link.  And lastly, a stylesheet reference to the TD.clsBodyText class to properly set the font size, style, and color.  You'll undoubtedly want to include your own.  View A Sample Page.  Here's the code:

<% Function ConvertUrlToLink(sVal) Set RegEx = New RegExp RegEx.IgnoreCase = True RegEx.Global = True RegEx.Pattern = "(\bhttp://[^ ]+\b)" sVal = RegEx.Replace(sVal, "<a href='$1'>$1</a>") Set RegEx = Nothing ConvertURLToLink = sVal End Function Function FormatText(sVal,sColSpan) sVal = Trim(sVal) sVal = Replace(sVal,"<","&lt;") sVal = Replace(sVal,". ",".&nbsp;&nbsp;") sVal = Replace(sVal," ","&nbsp;&nbsp;") sVal = Replace(sVal,vbcrlf," <br>" & vbcrlf) sVal = ConvertURLToLink(sVal) FormatText = "<tr><td colspan=" & sColSpan & " class=clsBodyText width=500 >" & sVal & "</td></tr>" End Function %>

	Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of EggHeadCafe which provides .NET articles, book reviews, software reviews, and software download and purchase advice.