30 November, 2009

Things to remember with Datetime Queries

I wrote some stored procedures to get data for my reports in last week. Most of the queries involve day's summary and cumulative summary till the given date. After running the queries I observed that I am getting wrong results in all the datasets.
After some analysis, I figured out my mistake. I have compared the date given directly with the date field in the table. The problem here is If I give '11/30/2009' as my input date, Database automatically adds the time part to that. So the input becomes '11/30/2009 00:00:00:000'. Whenever I compare the date the query always checks for the exact match. So I need to modify my queries to return correct data.
Here is the modification:
instead of comparing date directly... like 'date = @ReportDate' or 'date < @ReportDate', we should compare with the 'floor' and 'ceil' values for the given date. So the query has modified as 'date between cast(floor(cast(@ReportDate as float))as datetime) and cast(ceiling(cast(@ReportDate as float))as datetime)'. This query works and returns the required datarows. But here one point should be remembered, if we give the parameter with any date picker, It selects only the date and by default it will become 'givenDate 00:00:00:000'. In this case the floor and ceil values for this date are same. So We need to correct our query once again. This modification is very simple and floor value is considered as a benchmark always. After this modification, the query will look like, 'date between cast(floor(cast(@ReportDate as float))as datetime) and dateadd(hh,24,cast(floor(cast(@ReportDate as float))as datetime))'. This last query will always return the correct set of results for a given date.

I think this is known to many of the techies.. But useful to starter bees :)

25 November, 2009

Cumulative totals Query with T-SQL

The following query will be Very useful when one want to calculate the Cumulative total in SQL Server.

For Ex:
Create Table TEST_DATA
Amount Int NOT NULL

if the table data is like

ID  Amount
--- ------
1 10
2 20
3 40
4 50
5 30

select t1.ID, t1.Amount,
(select sum(t2.Amount ) from
TEST_DATA as t2 where t2.ID <= t1.ID) as Total from TEST_DATA as t1

This Query will return the results as

ID Amount Total
--- ------ -----
1 10 10
2 20 30
3 40 70
4 50 120
5 30 150

-Happy Querying :)

10 September, 2009

Link tag in Content Page

Recently I faced a problem with attaching JavaScript and Stylesheet files from the Content Page.

Generally all the stylesheets and Java script files are included in the Head section of the Master Page. But sometimes, we may need a javascript/stylesheet file in one particular page. Then it is better to load files only in that page to avoid un-necessary download for all the pages.

When I have this requirement, I started including link tag in the content page. But I came to know that link tag can't work with content pages, when I check my page in the browser. Then I realized that the script and style tags should be used in Content pages. As, Writing long scripts and Styles in the page is not a good practice, I am not convinced with this option and serched for best solution.

There are two solutions for this problem. The best solution is to create a content placeholder in the Head section of the master page and use that placeholder to add extra files in the page wherever needed;

And another solution is to link the css and js files from the code behind. This involves creating a HtmlGenericControl with properties tagname, attributes and innerHtml in the page_Load event of the required page. We can select any of these two to fulfill the need.

--Happy Programming :)

17 June, 2009

asp:menu fix for IE8 problem available

This is a popular problem with recently released browser IE8. Simple googling will give so many results consisting workarounds for this problem. The best way to set the things in IE8 is place the meta tag in all the master pages head section, for IE7 compatibility. This is legal and you can find this suggestion in many forums of ASP.Net. The magic meta tag is
meta equiv="X-UA-Compatible" content="IE=EmulateIE7" (with in tags < />)

Another workaround for this is to set the z-index of the menu control to a higher value like 100.

Now, there is a solution for this behavior. Interestingly, this mis-functionality is due to a bug in asp.net framework, which is fixed now. Microsoft has release a patch to fix this issue and the patch is available for download at following address.

Windows 2000, XP, Server 2003:

Windows Vista, Server 2008:

You can fine more details about this issue and fixes here.

The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>)

Last Day I encountered this problem and spent nearly 2 hours for searching for the root cause. The root for this exception is server code block(<% .. %>) inside the master page. I need to read the value of a variable which runs at server from javascript. By default all my script tags are included in the head section of the master page. So, I have attached another script tag to fetch the required value in the head section, in which the server tags included. When I run my program, I got the exception "The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>)".

For troubleshooting, I started debugging the application. I observed that everything is working fine, but the master page where I added the script, is repeatedly loading. It went into some kind of infinite loop redirecting to itself. I still dont know the reason for this mis-behavior.

The workaround for this is to replace the script tags with <% %> from head section to body. This will solve the problem and everything works as expected.

Useful IE CSS hacks

Many of the developers feel pain to develop their applications in cross platform, especially when there is Internet Explorer 6. This is because, IE6 do not support some core css Styles like min-height. Also, Many times it is observed that IE renders the things a bit different compared to other browsers. The following are the useful hacks for developing websites in IE.

Min-Height Property in IE6:
This is a very commonly used property to set the look and feel of the page. There is a workaround for this tag in IE6. Use the following styles in sequence:
min-height: 600px;
height:auto !important;
These lines can solve the problem of fixing min-height for ie6.

IE6 specific functionality:
Many times, IE6 require different styles from other browsers for constant look across the browsers. Then we have to define those properties two times, once for all the browsers and second for IE6. Many of the developers define a special css file for IE6 and will add that on checking the client browser. Another workaround for this is to add IE6 specific hack for all the properties.

IE6 hack is to prefixing an underscore(_) before the property. For Example,
If we need the height of an object to 450px and in IE6 it is 430px then write these styles as

height: 450px;

Only IE6 can understood these properties and render the page. These hacks are handy while developing the web pages in a cross browser platform.

Happy Programming.!!

How to Store Sessions in SQL Server

We can maintain the session for asp.net application in the following three ways.
  1. Inproc : This will store the session in the memory of the web server. This is mostly used and will be lost when the application crashes. But the advantage with this is the performance. As the session is stored in the web server itself, this is very fast compared to other methods.
  2. State Server: This will store the session in the memory of a machine, which is dedicated for the purpose of storing sessions. This is efficient and scalable.
  3. SQL Server: This will store the session in SQL Server. To store a session in SQL Server, we need to configure the sql server. This is also efficient, scalable and secure implementation of storing a session.
Now Discuss about configuring the SQL server to store the state:

This can be done by executing a simple file named 'aspnet_regsql.exe'. This file is located in the .NET framework folder.

Follow the steps to configure the Database server,
  1. Open the command prompt and goto OperatingSystemDrive\WINDOWS\Microsoft.NET\Framework\v2.0.50727
    • Note: The file is available in v2 only. Though you are using Asp.net Framework v3 or v3.5 you need to goto 2.0.50727 only.
    • As There wont be any change in the sql queries to create a database tables and stored procedure with the version of asp.net framework, They haven't provided with the file in v3 and v3.5 folder.(This is my understanding, the actual reason may be different)
  2. If you just type aspnet_regsql.exe and press enter, this will open a GUI to specify the Database server and the name of Database. If you don't specify any database name the default name will be 'sqldatadb'.
  3. This will create a database with tables and stored procedures, but the database is not yet ready to store the sessions.
  4. For completing the configuration we need to run the command aspnet_regsql.exe -ssadd -sstype c -d DATABASENAME -E.(Give the Database Name if you specity any in step 2).
  5. For more details related to options for this command can be found here.

With this the database is configured to store the sessions. Though it is verymuch scalable and secure, this has performance issues when compared to 'inproc' implementation.

Note: We can run step4 mentioned above in Visual Studio Command prompt to created the Database to store sessions in one shot.

Sort Expression as a parameter to Stored Procedure

How to send the sort expression as a parameter to Stored Procedure?

This is the problem that I faced in one of my recent tasks. When I was given this work,
without much thought, started with the following lines.

sortExp varchar(25)
select ProcedureID,Items,[Value] from Procedure
order by @sortExp DESC

When I tried to run this stored procedure, I got an unexpected error saying,

Msg 1008, Level 16, State 1, Procedure GetProcedure, Line 5
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Then, I started to think about the error and soon I traced the fault.
The problem is that- SQL Server is not recognizing the given parameter as a table column. By default all the parameters are considered to be of some data type. They can't be converted into type 'Column'. As "Order by" statement needs a column name, server is throwing an error.

What is the Solution?
The solution for the problem is using a "CASE" statement with the parameter.
Then the procedure will look like

sortExp varchar(25)
select ProcedureID,Items,[Value] from Procedure
order by CASE @sortExp
When '
ProcedureID' then
When '
Items' then
When '
Value' then

Now,What if you want to send another parameter for Direction...?
then, you have to check that in if-else loop or another CASE Statement.

What is 3-tier Architecture

The 3-tier Architecture is all about separating the Business Logic from the Application and Data Storage. i.e., Database (in general). It is popularly called as MVC (Model-View-Controller) Model. User will interact with View part in the application. This will be controlled by Controller and Model layers.

Whenever user performs something, that will call some action handler in the Code behind and calls a functions in the Controller. In Object Oriented approach, Model consists of Business objects. Depending on the situation, some business object will be created and that will be used to store and retrieve data from the Database.

This separation is needed in real time application,
Because the change in one layer should not effect the other layer's functionality.

For Ex. Change in the Database from SQL server to Oracle is nowhere related to user. So he/she should not know these details while interacting with the system.

How to configure Visual SVN Server

The configuration is very simple and involves only three installations
1. Visual SVN Server(For Server)
2.Tortoise SVN(Client for SVN)
3.ANKH SVN(to integrate with Visual Studio)

After installation, open Visual SVN Configure manager and create a repository and a user.
Go to the folder, which you want to share and right click on that.
In tortorise go to repo-browser and give the path for existing repository.
Thats it.. Now the file is ready to use on share...

The clients should install Tortoise SVN and ANKH to access the folder.
Everyone should create a local copy of the folder and should work from that.

As I am new to this, I dont know how to work with this simultaneously. I am working on that part now... and will write again after completing that too.