Checking for duplicate record with FormView

When we are using FormView control often we need to check for existence of record in database at the time of insert or update.

Suppose we are having table 'CategoryMst' and we want that 'CategoryName' column should be unique.

1. Add Unique key to the column 'CategoryName' in the 'CategoryMst' table.

2. Then just catch the exception and keep the FormView in insert/ edit mode in ItemInserted/ ItemUpdated event of FormView control.

protected void FormView1_ItemInserted(object sender, FormViewInsertedEventArgs e)
{
if (e.Exception != null)
{
if (((SqlException)e.Exception).Number == 2627)
{
e.ExceptionHandled = true;
e.KeepInInsertMode = true;
// Display error message.
}
}
}

WPF vs WPF XBAP vs Silverlight

WPF is becoming more and more popular. But there is always little bit of confusion about whether to use WPF or Silverlight.
So here is the comparison matrix from Michael’s blog post:

Avoid duplicate insertion of record on page refresh by F5

With the help of TimeStamp, we can easily avoid duplicate insertion of record when user refreshes page by pressing F5.
Let's see how easy it is to check whether page is refreshed by pressing F5:

1. First, add a class 'BasePage' to your project and inherit it from System.Web.UI.Page
Here is the detailed code for BasePage class:



public class BasePage : System.Web.UI.Page
//Inherit class from System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Session["RefreshTimeStamp"] = HttpContext.Current.Server.UrlDecode(System.DateTime.Now.ToString());
}
}

protected void Page_PreRender(object sender, EventArgs e)
{
ViewState["RefreshTimeStamp"] = Session["RefreshTimeStamp"];
}

public bool IsRefreshed
{
get
{
if (Convert.ToString(Session["RefreshTimeStamp"]) == Convert.ToString(ViewState["RefreshTimeStamp"]))
{
Session["RefreshTimeStamp"] = HttpContext.Current.Server.UrlDecode(System.DateTime.Now.ToString());
return false;
}
else
{
return true;
}
}
}
}



2. Now on Default.aspx we will check for page refresh.
Inherit Default.aspx from BasePage instead of System.Web.UI.Page.
and using IsRefreshed property in BasePage, we can easily detect page refresh.


public partial class _Default : BasePage
//Inherit page from BasePage
{
protected void Button1_Click(object sender, EventArgs e)
{
if (!IsRefreshed)
{
// Your Code
}
}
}

Finding Nth min/max salary using SQL

To find Nth minimum salary:

1.In SQL Server 2005

SELECT * FROM (SELECT salary,Dense_rank() OVER (ORDER BY Salary) AS Rank FROM employees) t1 WHERE Rank=N

2.In SQL Server 2000/2005

SELECT salary FROM employees e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.salary))
FROM employees e2 WHERE e2.salary <= e1.salary))

To find Nth maximum salary:

1.In SQL Server 2005

SELECT * FROM (SELECT salary,Dense_rank() OVER (ORDER BY Salary desc) AS Rank FROM employees) t1 WHERE Rank=N

2.In SQL Server 2000/2005

SELECT salary FROM employees e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.salary))
FROM employees e2 WHERE e2.salary >= e1.salary))

My first post

Hi

This is my first blog post.

I will be blogging on topics like ASP.Net, AJAX, C#, SQL Server.

So stay tuned...