Valuable Learnings

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)

The following query will be Very useful when one want to calculate the Cumulative total in SQL Server. For Ex: Create Table TEST_DATA { ID Int NOT NULL, 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 :)

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 ano

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: http://code.msdn.microsoft.com/KB962351/Release/ProjectReleases.aspx?ReleaseId=2294 Windows Vista, Server 2008: http://code.msdn.microsoft.com/KB967535/Re

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 sec

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; height:600px; 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

We can maintain the session for asp.net application in the following three ways. 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. 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. 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, Open the command prompt and

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. CREATE PROCEDURE GetProcedure @ sortExp varchar(25) AS 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"

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.

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.