Pages

Friday, October 25, 2013

IE11 and Windows 8.1 : Solution for __doPostBack and Ajax not working

​Following Steps are performed to fix IE 11: ajax and __dopostback issues:
There are 2 solutions:
1. Site Level Fix:
   step 1: Perform Steps from following links
        http://www.nuget.org/packages/App_BrowsersUpdate/
  
   step 2: In ie.browser add mentioned settings from following link
           http://stackoverflow.com/questions/18009636/crosspostback-ajax-controls-and-asp-net-generated-postbacks-work-for-net-4-5

2. Machine Level Fix:
   Main Source: http://www.hanselman.com/blog/IE10AndIE11AndWindows81AndDoPostBack.aspx
   Step 1: Download patch from following link.
     http://support.microsoft.com/kb/2836939
   Step 2: install patch

Wednesday, October 2, 2013

SQL Server: Insert missing Identity Column Values

When we set identity to a particular column, we dont need to pass value for that column.
SQL Server automatically increments its value.

Suppose if we inserts 10 records and deleted last record. Maximum value will be 9 but when we add new record then SQL server takes next value as 11 not it doesn't take 10. Because we already added record in 10th position and deleted after it.
Example:
CREATE TABLE [dbo].[Student](
[Sno] [int] IDENTITY(1,1) NOT NULL,
[Sname] [nvarchar](50) NULL,
[Saddrs] [nvarchar](50) NULL
) ON [PRIMARY]
GO
insert into student(sname,saddrs) values('Abhi','India')
insert into student(sname,saddrs) values('Sourav','Japan')
insert into student(sname,saddrs) values('david','US')
insert into student(sname,saddrs) values('rahul','UK')
insert into student(sname,saddrs) values('gautam','Australia')
insert into student(sname,saddrs) values('jonathan','Germany')
insert into student(sname,saddrs) values('prasad','Pune')
insert into student(sname,saddrs) values('sandeep','Pune')
insert into student(sname,saddrs) values('john','Pune')
insert into student(sname,saddrs) values('sanjay','Pune')

Now we will delete sno=10
delete from student where sno=10
After Record deleted

Now If we add new record then it will take sno=11 but we want to add sno=10

In this case we need to insert value manually by turning off identity feature off. After inserted manually we need to identity turn on  again.
Syntax:
SET IDENTITY_INSERT TABLE-NAME  ON

Example:
SET IDENTITY_INSERT student  ON

Here
SET IDENTITY_INSERT TABLE-NAME  ON :turns off identity increment. Specify table name on which you want to turn off identity

Your Insert Statement, you need to provide identity column value because in previous command you have turned off autoincrement. Provide missing identity values which you want to add.
Example: Student is table in which sno is identity column.
I have 10 records, i have deleted last record i.e sno=10 now i want to add value for sno=10

My query will be
insert into student(Sno,sname,saddrs) values(10,'sachin','Pune')
After Above Query Result will be

Syntax:
SET IDENTITY_INSERT TABLE-NAME  OFF
Example:
SET IDENTITY_INSERT student  OFF

Here
SET IDENTITY_INSERT TABLE-NAME  OFF:turns on identity increment.
Now your autoincrement will be started, you dont need to provide value for identity column.

If you try to provide value then sql server will give you an error.
Example:
insert into student(Sno,sname,saddrs) values(11,'Rakesh','Pune')

Paging In SQL Server with derived table and CTE

First We will create table

create table RecordTable(col1 int, col2 int)

insert into RecordTable
select 1,1
union all
select 1,2
union all
select 1,3
union all
select 1,4
union all
select 1,5
union all
select 1,6
union all

select 1,7

We will do it through in 2 ways.
1. With Derived Table
2. With Common Table Expression.

Mostly performance is same in both ways, in some scenario it may vary.

1.Paging with Derived Table
select * from(
select col1,col2,
ROW_NUMBER() over( order by col1)  as rowno
from RecordTable)
ctedup
where rowno>minNo and rowno<MaxNo

2. Paging with CTE
;with ctedup as(
select col1,col2,
ROW_NUMBER() over( order by col1)  as rowno
from duplicateRecordTable)
select * from ctedup
where rowno>MinNo and rowno<MaxNo

It will gives you records in between min and max no.