Thursday, December 11, 2008

String Formatting in ASP.Net Code behind to improve readability

Quite often I write SOL procedures in the code behind as oppose to using a stored procedure. The main reason for this is if I want to generate the SOL statement on the fly based on the users input. Here is a handy tip for improving the readability of your SQL written inside the code behind page;

str_sql = String.Format(@"
SELECT tblProducts.ProductID, tblProducts.Title, SellCost, COALESCE(MediaID, 0) AS MediaID FROM tblProducts
LEFT JOIN tblRelatedMedia ON tblProducts.ProductID = tblRelatedMedia.ProductID AND tblRelatedMedia.Ordered = 1
LEFT JOIN tblProductGroups tblBrands ON tblProducts.BrandGroupID = tblBrands.GroupID
WHERE tblProducts.IsDeleted = 0 AND tblProducts.IsAvailable = 1
AND (tblProducts.ProductID IN (SELECT ProductID FROM tblRelatedGroups WHERE GroupID = {0}) OR
tblProducts.ProductID IN (SELECT ProductID FROM tblRelatedGroups WHERE GroupID IN (SELECT GroupID FROM tblProductGroups WHERE ParentGroupID = {0})))", group_id);

Basically using the string.format with the @ symbol enables me to lay the SQL out like I would if I was creating a stored procedure in SQL SERVER. This makes it much easier to debug any issues with the SQL generated.

Wednesday, December 10, 2008

Changing ownership of a table (object) in SQL Server

use dbname
EXEC sp_changeobjectowner 'oldowner.tblToChange', 'newowner'
Note: The default owner is dbo.

Tuesday, December 2, 2008

Making sure MasterPage CSS and JavaScript reference path are always correct

Depending on the folder, the relative path to CSS and JavaScript references may be different. We need toensure that regardless the path of the page that called the master page, the relative paths to the reference files are always correct. We also want the pages and master pages to display properly in Visual Studio and that the links are always relative.

Here is how to do it:

1. Make sure the head tag is runat=server
2. CSS should use a link tag with a relative path
3. JavaScript should use ResolveUrl built in Dot Net method to dynamically generate actual relative path

Here is the html source, assuming that the master page exists in ~/templates/ folder, the .js script exists in ~/scripts/ folder, the CSS stylesheets exists in ~/styles/ folder:

<head runat="server">
<title>Title of Page</title>
<!-- meta information goes here -->
<link href="../styles/layout.css" rel="stylesheet" type="text/css" />
<script type='text/javascript' src='<%= ResolveUrl("~/scripts/common.js") %>'>

Google AJAX APIs and Libraries

Google have started hosting always-up-to-date script libraries via their Google AJAX APIs -

This means they are hosting versions of jQuery, MooTools, Scripaculous etc. which are really helpful for developing JavaScript at a fraction of the time and effort. It allows cool effects (transitions, moving parts and the like) and many scripts online utilize these.

The API also allows taking advantage of Google Maps & Visualizations, which I'll go into detail about on a later post. To utilize Google AJAX APIs:

<script src=""></script>

* I recommend always use https - to ensure no problems when the page calling it is SSL protected.

Then to load, for example, jQuery, do the following:


google.load("jquery", "1.2.6"); // Load jQuery


jQuery is a great, simple to use library that is used to produce amazing effects such as those seen in this tutorial: (the .js used here is ridiculously easy to plug-in)

Wednesday, November 19, 2008

Turning an ASP.NET Panel into a Fieldset

I found out a while back that you can make a panel a fieldset simply by setting the value of the Grouping text attribute. This proved to be really useful as although I use Multiview for navigating a page I always found myself adding panels inside views as I needed to set the default button attribute. See Below;

<asp:Panel ID="pl_login" runat="server" DefaultButton="btn_login" GroupingText="Returning Customers, Enter your Email Address and Password ">
<table class="form_table" width="400">
<td>&nbsp;<asp:TextBox ID="tb_login_email" runat="server" CssClass="textbox_wider"></asp:TextBox></td>
<td>&nbsp;<asp:TextBox ID="tb_login_password" runat="server" TextMode="Password"></asp:TextBox></td>
<td align="left">&nbsp;<asp:Button ID="btn_login" runat="server" CommandName="login" OnCommand="loginAction"
Text="Login" /></td>
Not yet signed up -
<asp:LinkButton ID="lb_sign_up" runat="server" CommandName="signup" OnCommand="loginAction">Click here</asp:LinkButton></td>
I didn't embrace it 100% as the width of fieldset kept defaulting to 100%. I didn't want to set a specific width as I was if you did and the content exceeded that width it went outside the fieldset. Anyhow I tweaked the css of the fieldset so that the width would default to width of the content inside (display:inline). If you must set a width - use the min-width style;

form fieldset
top: 5px;
border: solid 1px #A8A8A8;
margin: 20px 0px 20px 0px;
display:inline; /* fieldset fits content */
padding: 0px 10px 10px 10px;
min-width: 550px;

form fieldset legend

background-color: #56458C;
padding: 0px 8px 2px 8px;
margin: 2px 0px 10px 0px;
top: -10px;

.form_table th {
width: 120px;
text-align: right;

Friday, November 14, 2008

Registering MIME Types in IIS

Recently a new extension (Office 2008 - .pptx) could not be accessed via IIS. It showed a 404 (file does not exist error). Basically, the MIME type for the new Office Extensions do not exist in IIS.

Instructions for adding the extension:

  1. Get to the IIS Manager
  2. Right click on the Server and select “Properties” to modify the global behavior, or 'Websites' or any specific website to edit just that one.
  3. Select “HTTP Headers” (or skip straight to 4)
  4. Select “Mime Types”
  5. Enter a new extension (no leading period is required) and the appropriate mime type. Repeat as needed for each extension.
  6. Click “OK” to close all the dialogs
  7. Restart IIS i necessary
 Try this method if a file fails to show even though you are certain it exists.

SQL Distinct Dates

If you wish to select distinct Dates from a table, as opposed to distinct DateTimes, simply use the following:
select distinct(cast(datediff(day, 0, @InputDate) as datetime)) AS OutputDate

SQL String Parsing Function

The following is a SQL function for parsing a string into its alphanumeric characters. It can be called as dbo.funcParseAlphaChars(@string)


FUNCTION [dbo].[funcParseAlphaChars]
@IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%',
WHILE @IncorrectCharLoc > 0
SET @string =
STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc =
PATINDEX('%[^0-9A-Za-z]%', @string)
SET @string = @string

Create a html form on the fly and submit using javascript

I had to plug in a UPS tracking form. There's difficulties putting a HTML form inside an form tag. Also as the data to be passed in the form was coming from the database it didn't make sense to load the data in a form and have the user press the submit button. Although I'm still submitting a form. To the user it seems like they're simply clicking on a hyperlink.


<asp:HyperLink ID="hl_track" runat="server"
NavigateUrl='<%# getJs(Eval("Tracking").ToString()) %>'
Text='<%# Eval("Tracking") %>' Font-Overline="False" Font-Underline="True"></asp:HyperLink>


Return the navigation URL as so;

protected string getJs(string tracking)
string ups_from = string.Format(@"<form name=""ups"" id=""ups"" method=""post"" action="""" target=""_blank""><input type=""text"" size=""35"" name=""InquiryNumber1"" value=""{0}"" /><input type=""hidden"" name=""UPS_HTML_License"" value=""3C34A55003D153B0"" /><input type=""hidden"" name=""IATA"" value=""us"" /><input type=""hidden"" name=""Lang"" value=""eng"" /><input type=""hidden"" name=""UPS_HTML_Version"" value=""3.0"" /><input type=""hidden"" name=""TypeOfInquiryNumber"" value=""T"" /></form>", tracking);

string js = string.Format("javascript:submitUPS('" + ups_from + "','ups');");

return js;

function submitUPS(form_content, form_name) {
var _body = document.getElementsByTagName('body')[0];
var _div = document.createElement('div');

_div.innerHTML = (form_content);


Monday, August 4, 2008

SQL inline query with input and output parameters

How to build up a query within a stored procedure and get output parameters from it, here's an example...

DECLARE @Value NVARCHAR(50), @Query NVARCHAR(255), @Params NVARCHAR(50)

SET @Query = 'SELECT @Value = 54;'

EXEC sp_executesql @Query, N'@Value int OUTPUT', @Value = @Value OUTPUT


You can also feed input parameters if need be...