Wednesday, September 26, 2012
Maintain Scroll Position on Postback
var prm = Sys.WebForms.PageRequestManager.getInstance();
prm.add_beginRequest(function() {
prm._scrollPosition = null;
});
Sunday, March 6, 2011
IIS 7.0 configuration issue: This configuration section cannot be used at this path. This happens when the section is locked a
HTTP Error 500.19 - Internal Server Error
Description: The requested page cannot be accessed because the related configuration data for the page is invalid.
Error Code: 0x80070021
Notification: BeginRequest
Module: IIS Web Core
Requested URL: http://localhost:80/
Physical Path: C:\www\Projects\3.5 VS 2008 Beta\Elbalazo\Web
Logon User: Not yet determined
Logon Method: Not yet determined
Handler: Not yet determined
Config Error: This configuration section cannot be used at this path. This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault="Deny"), or set explicitly by a location tag with overrideMode="Deny" or the legacy allowOverride="false".
Config File: \\?\C:\www\Projects\3.5 VS 2008 Beta\Elbalazo\Web\web.config
Config Source:
170:
171:
172:
More Information... This error occurs when there is a problem reading the configuration file for the Web server or Web application. In some cases, the event logs may contain more information about what caused this error.
Server Version Information: Internet Information Services 7.0.
The handlers section is talking about this in my web.config which I believe is there by default when you create a new web application in the .NET 3.5 framework:
Initially I thought this problem was because I needed to change overrideModeDefault="Deny" to overrideModeDefault="Allow" so I changed all instances of this to Allow in my C:\Windows\System32\inetsrv\config\applicationHost.xml file on my Vista Ultimate machine. That made this error go away but then the second error stated that a handler that is mapped in my web.config has a problem.
Working Solution
Received this same issue after installing IIS 7 on Vista Home Premium. To correct error I changed the following values located in the applicationHost.config file located in Windows\system32\inetsrv.
Change all of the following values located in section -->
*
change this value from "Deny" to "Allow"
*
change this value from "Deny" to "Allow"
Description: The requested page cannot be accessed because the related configuration data for the page is invalid.
Error Code: 0x80070021
Notification: BeginRequest
Module: IIS Web Core
Requested URL: http://localhost:80/
Physical Path: C:\www\Projects\3.5 VS 2008 Beta\Elbalazo\Web
Logon User: Not yet determined
Logon Method: Not yet determined
Handler: Not yet determined
Config Error: This configuration section cannot be used at this path. This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault="Deny"), or set explicitly by a location tag with overrideMode="Deny" or the legacy allowOverride="false".
Config File: \\?\C:\www\Projects\3.5 VS 2008 Beta\Elbalazo\Web\web.config
Config Source:
170:
171:
172:
More Information... This error occurs when there is a problem reading the configuration file for the Web server or Web application. In some cases, the event logs may contain more information about what caused this error.
Server Version Information: Internet Information Services 7.0.
The handlers section is talking about this in my web.config which I believe is there by default when you create a new web application in the .NET 3.5 framework:
Initially I thought this problem was because I needed to change overrideModeDefault="Deny" to overrideModeDefault="Allow" so I changed all instances of this to Allow in my C:\Windows\System32\inetsrv\config\applicationHost.xml file on my Vista Ultimate machine. That made this error go away but then the second error stated that a handler that is mapped in my web.config has a problem.
Working Solution
Received this same issue after installing IIS 7 on Vista Home Premium. To correct error I changed the following values located in the applicationHost.config file located in Windows\system32\inetsrv.
Change all of the following values located in section
*
change this value from "Deny" to "Allow"
*
change this value from "Deny" to "Allow"
Jquery Regular Expression validation
function isValidDate(dateString)
{
var regEx = /^\d{1,2}(\-|\/|\.)\d{1,2}\1\d{4}$/; //regular expression
if (regEx.test(dateString))
{
return true;
}
else
{
return false;
}
}
{
var regEx = /^\d{1,2}(\-|\/|\.)\d{1,2}\1\d{4}$/; //regular expression
if (regEx.test(dateString))
{
return true;
}
else
{
return false;
}
}
Jquery Fix for Calendar to work on postback inside update panel
/*Handling jquery datepicker to work on update panel after post back.Added on */
Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);
function EndRequestHandler(sender, args)
{
$("#<%= txtCalendar.ClientID %>").datepicker({
numberOfMonths: 1,
dateFormat: 'dd/mm/yy',
changeMonth: true,
changeYear: true,
beforeShow: customRange
});
}
Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);
function EndRequestHandler(sender, args)
{
$("#<%= txtCalendar.ClientID %>").datepicker({
numberOfMonths: 1,
dateFormat: 'dd/mm/yy',
changeMonth: true,
changeYear: true,
beforeShow: customRange
});
}
Tuesday, June 30, 2009
SQL Server: Calculating Running Totals, Subtotals and Grand Total Without a Cursor
If you have ever had the need to show detailed data of individual transactions and also keep a running total, subtotals, and grand total columns at the same time, but were not exactly sure how to tackle the problem then this article might help. In this article I will show you a few different techniques for calculating and summing information on multiple rows without using a cursor. The techniques I will show you will just use a basic SELECT statement. Of course, the calculations of the running total, subtotals and grand total will be done using other SQL clauses and functions like SUM and CASE.
create table Orders
(OrderID int identity,
OrderAmt Decimal(8,2),
OrderDate SmallDatetime)
OrderID OrderAmt OrderDate
----------- ---------- --------------------
1 10.50 2003-10-11 08:00:00
2 11.50 2003-10-11 10:00:00
3 1.25 2003-10-11 12:00:00
4 100.57 2003-10-12 09:00:00
5 19.99 2003-10-12 11:00:00
Running Total On Each Record
This first example will display a simple method of calculating the running total of the OrderAmt. The calculated running total will be displayed along with each record in the Orders table. The "Running Total" column will be created with a simple SELECT statement and a correlated sub query. The correlated sub query is the part of the statement that does the heavy lifting to produce the running total.
select OrderId, OrderDate, O.OrderAmt
,(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID)
'Running Total'
from Orders O
When I run this query against my Orders table I get the following results:
OrderId OrderDate OrderAmt Running Total
----------- -------------------- ---------- -------------
1 2003-10-11 08:00:00 10.50 10.50
2 2003-10-11 10:00:00 11.50 22.00
3 2003-10-11 12:00:00 1.25 23.25
4 2003-10-12 09:00:00 100.57 123.82
5 2003-10-12 11:00:00 19.99 143.81
As you can see, there is a "Running Total" column that displays the running total along with the other column information associated with each Orders table record. This running total column is calculated, by summing up the OrderAmt for all Orders where the OrderID is less than or equal to the OrderID of the current ID being displayed.
Running Total for Each OrderDate
This example is similar to the one above, but I will calculate a running total for each record, but only if the OrderDate for the records are on the same date. Once the OrderDate is for a different day, then a new running total will be started and accumulated for the new day. Here is the code to accomplish this:
select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt,
(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID and
convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
'Running Total'
from Orders O
order by OrderID
Here are the results returned from the query using my sample Orders Table:
OrderId Order Date OrderAmt Running Total
----------- ---------- ---------- ---------------
1 10/11/2003 10.50 10.50
2 10/11/2003 11.50 22.00
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57 100.57
5 10/12/2003 19.99 120.56
Note that the "Running Total" starts out with a value of 10.50, and then becomes 22.00, and finally becomes 23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But when OrderID 4 is displayed the running total is reset, and the running total starts over again. This is because OrderID 4 has a different date for its OrderDate, then OrderID 1, 2, and 3. Calculating this running total for each unique date is once again accomplished by using a correlated sub query, although an extra WHERE condition is required, which identified that the OrderDate's on different records need to be the same day. This WHERE condition is accomplished by using the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.
Running Totals With Subtotals and Grand totals
In this example, I will calculate a single sub totals for all Orders that were created on the same day and a Grand Total for all Orders. This will be done using a CASE clause in the SELECT statement. Here is my example.
select O.OrderID,convert(char(10),O.OrderDate,101) 'Order Date',O.OrderAmt,
case when OrderID = (select top 1 OrderId from Orders
where convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101)
order by OrderID desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders
where OrderID <= O.OrderID
and convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
else ' ' end as 'Sub Total',
case when OrderID = (select top 1 OrderId from Orders
order by OrderDate desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders)
else ' ' end as 'Grand Total'
from Orders O
order by OrderID
Output from the SELECT statement looks like this:
OrderID Order Date OrderAmt Sub Total Grand Total
----------- ---------- ---------- ---------- -----------
1 10/11/2003 10.50
2 10/11/2003 11.50
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57
5 10/12/2003 19.99 120.56
6 10/13/2003 47.14
7 10/13/2003 10.08
In this example the first CASE statement controls the printing of the "Sub Total' column. As you can see, the sub total is printed only on the last order of the day, which is determined by using a correlated sub query. The second CASE statement prints the "Grand Total", which is only printed along with the very last order. Each of these CASE statements uses the TOP clause to determine which OrderID is the correct order for which to print out the "Grand Total".
Conclusion
Hopefully these examples will help you understand different methods that can be used to calculate running totals, sub totals, and a grand total. As you can see you don't need to use a cursor to calculate these different totals. With the creative use of correlated sub queries and other SELECT clauses like CASE you can easily create all these different totals. Next time you need to calculate totals consider using one of these non-cursor based solutions.
create table Orders
(OrderID int identity,
OrderAmt Decimal(8,2),
OrderDate SmallDatetime)
OrderID OrderAmt OrderDate
----------- ---------- --------------------
1 10.50 2003-10-11 08:00:00
2 11.50 2003-10-11 10:00:00
3 1.25 2003-10-11 12:00:00
4 100.57 2003-10-12 09:00:00
5 19.99 2003-10-12 11:00:00
Running Total On Each Record
This first example will display a simple method of calculating the running total of the OrderAmt. The calculated running total will be displayed along with each record in the Orders table. The "Running Total" column will be created with a simple SELECT statement and a correlated sub query. The correlated sub query is the part of the statement that does the heavy lifting to produce the running total.
select OrderId, OrderDate, O.OrderAmt
,(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID)
'Running Total'
from Orders O
When I run this query against my Orders table I get the following results:
OrderId OrderDate OrderAmt Running Total
----------- -------------------- ---------- -------------
1 2003-10-11 08:00:00 10.50 10.50
2 2003-10-11 10:00:00 11.50 22.00
3 2003-10-11 12:00:00 1.25 23.25
4 2003-10-12 09:00:00 100.57 123.82
5 2003-10-12 11:00:00 19.99 143.81
As you can see, there is a "Running Total" column that displays the running total along with the other column information associated with each Orders table record. This running total column is calculated, by summing up the OrderAmt for all Orders where the OrderID is less than or equal to the OrderID of the current ID being displayed.
Running Total for Each OrderDate
This example is similar to the one above, but I will calculate a running total for each record, but only if the OrderDate for the records are on the same date. Once the OrderDate is for a different day, then a new running total will be started and accumulated for the new day. Here is the code to accomplish this:
select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt,
(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID and
convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
'Running Total'
from Orders O
order by OrderID
Here are the results returned from the query using my sample Orders Table:
OrderId Order Date OrderAmt Running Total
----------- ---------- ---------- ---------------
1 10/11/2003 10.50 10.50
2 10/11/2003 11.50 22.00
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57 100.57
5 10/12/2003 19.99 120.56
Note that the "Running Total" starts out with a value of 10.50, and then becomes 22.00, and finally becomes 23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But when OrderID 4 is displayed the running total is reset, and the running total starts over again. This is because OrderID 4 has a different date for its OrderDate, then OrderID 1, 2, and 3. Calculating this running total for each unique date is once again accomplished by using a correlated sub query, although an extra WHERE condition is required, which identified that the OrderDate's on different records need to be the same day. This WHERE condition is accomplished by using the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.
Running Totals With Subtotals and Grand totals
In this example, I will calculate a single sub totals for all Orders that were created on the same day and a Grand Total for all Orders. This will be done using a CASE clause in the SELECT statement. Here is my example.
select O.OrderID,convert(char(10),O.OrderDate,101) 'Order Date',O.OrderAmt,
case when OrderID = (select top 1 OrderId from Orders
where convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101)
order by OrderID desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders
where OrderID <= O.OrderID
and convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
else ' ' end as 'Sub Total',
case when OrderID = (select top 1 OrderId from Orders
order by OrderDate desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders)
else ' ' end as 'Grand Total'
from Orders O
order by OrderID
Output from the SELECT statement looks like this:
OrderID Order Date OrderAmt Sub Total Grand Total
----------- ---------- ---------- ---------- -----------
1 10/11/2003 10.50
2 10/11/2003 11.50
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57
5 10/12/2003 19.99 120.56
6 10/13/2003 47.14
7 10/13/2003 10.08
In this example the first CASE statement controls the printing of the "Sub Total' column. As you can see, the sub total is printed only on the last order of the day, which is determined by using a correlated sub query. The second CASE statement prints the "Grand Total", which is only printed along with the very last order. Each of these CASE statements uses the TOP clause to determine which OrderID is the correct order for which to print out the "Grand Total".
Conclusion
Hopefully these examples will help you understand different methods that can be used to calculate running totals, sub totals, and a grand total. As you can see you don't need to use a cursor to calculate these different totals. With the creative use of correlated sub queries and other SELECT clauses like CASE you can easily create all these different totals. Next time you need to calculate totals consider using one of these non-cursor based solutions.
Subscribe to:
Posts (Atom)