accent search in sql server

Hello All,

Generally in our business application, we need to implement search functionality on database column like title, firstname, lastname, city etc. It will create problem in search result when data contains accent characters and the database collation is accent-sensitive. so we will get wrong result.

Let’s take an example, you need to search the database for movies having a director that contains “Shohei”. So we will write query like below:

select movieTitle from movies where director like '%Shohei%'

Ideally, the result set should include below:
Shohei
Imamura, Shohei
Youssef Chahine,Shôhei Imamura
Amos Gitai,Shohei
Shôhei Imamura,Claude Lelouch

If your database collation is accent-sensitive, the result set includes only:
Shohei
Imamura, Shohei
Amos Gitai,Shohei

Note that directors containing accent characters are excluded from the result set. This is because accent characters are excluded from the query. Unfortunately, this is not the desired result.

Now I will show you how can we search accent characters in sql query. For this, first we need to understand collection in sql.

What is collation in SQL?
A collation in SQL Server is a defined set of rules for sorting and comparing data. This includes rules for case-sensitivity and accent-sensitivity. When you select a collation (for your server, database, column, or expression), you assign specific characteristics to your data that affect the results of database operations.

When we installed SQL Server 2008 R2/SQL Server 2012, the default collation was SQL_Latin1_General_CP1_CI_AS. The collation name is broken into these parts:

  • SQL = indicates a SQL Server collation name
  • Latin1_General = identifies the alphabet whose sorting rules are applied
  • CP1 = identifies Code Page 1252, which is a character encoding of the Latin alphabet
  • CI = defines case-insensitive comparison rules
  • AS = defines accent-sensitive comparison rules

I have already write one blog for the case sensitive search in sql server where we have use COLLATE Latin1_General_CS_AS = ‘Case sensitive Data’

How can we implement accent search in sql server. There are some solution for it.
1. Configure the database so that it is not accent-sensitive; change the collation to SQL_Latin1_General_CP1_CI_AI.

2. Remove and replace accented characters in the query.

select movieTitle from movies where replace(director, 'ô', 'o')like '%Shohei%'

3. Specify an accent-insensitive collation in the query

select movieTitle from movies where director like '%Shohei%' collate SQL_Latin1_General_CP1_CI_AI

Option 1 is simplest from a programmer’s perspective, because it is a permanent solution, server-wide and database-wide, and it requires no programming changes to applications. When you are installing a new SQL Server instance then this is easy to do.
Generally we are hosting application on hosting server so we cannot manage this option because they have already installed database with default option accent-sensitivity.

Options 2 and 3 are simplest from a database administrator’s perspective, because they require no changes to the database. This is useful for shared hosting environment.

Hope this will help.

Gridview paging and sorting example

Hello All,

We are move ahead in grid view example today we will talk about paging and sorting in grid view.

To enable sorting and paging in your grid use “AllowSorting” and “AllowPaging” property to enable it respectively. Make both properties to true for our example.

When you enable paging and sorting at that time write two events to work it. Those events are “OnPageIndexChanging” and “OnSorting”.

Paste below code in aspx page.

          
            
                
                    
                        
                    
                    
                        <%# Eval("BusinessEntityID")%>
                    
                
                
                    
                        First Name
                    
                    
                        <%# Eval("FirstName") %>
                    
                
                
                
                
                
            
        

it will allow to paging and sorting. To write there event in .cs file use below code

    protected void gvData_Sorting(object sender, GridViewSortEventArgs e)
    {
        if (e.SortExpression.Trim().ToLower() == this.gvData_SortBy)
        {
            this.gvData_IsAsc = !this.gvData_IsAsc;
        }
        else
        {
            this.gvData_SortBy = e.SortExpression.Trim().ToLower();
            this.gvData_IsAsc = true;
        }
        //this.ResetPagerControl();
        this.BindData();
    }

    protected void gvData_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        //this.gvData_PageIndex = e.NewPageIndex;
        this.BindData();
        this.gvData.PageIndex = e.NewPageIndex;
        this.gvData.DataBind();
    }

    // To Bind Data
    private void BindData()
    {
        try
        {
            //int rp = 100;
            int rowPage = rp;
            
            if (rp == 0)
                rp = 100;

            int start = ((this.gvData_PageIndex - 1) * rp);

            if (start != 0)
            {
                rp = start + rp;
                start = start + 1;
            }

            string limit;
            limit = String.Format(" WHERE row >= {0} and row <= {1}", start, rp);

            DBM.SqlCommandEx sqlCmd = new DBM.SqlCommandEx(
                    String.Format("SELECT * FROM(SELECT Person.BusinessEntityID, FirstName, LastName, AddressLine1, City, PostalCode, ROW_NUMBER() OVER (ORDER BY {0}) as row " +
                    " FROM Person.Person" +
                    " INNER JOIN Person.BusinessEntity on BusinessEntity.BusinessEntityID = Person.BusinessEntityID" +
                    " INNER JOIN Person.BusinessEntityAddress on BusinessEntityAddress.AddressID = BusinessEntity.BusinessEntityID" +
                    " INNER JOIN Person.Address on Address.AddressID = BusinessEntityAddress.AddressID) T {1} ORDER BY row"
                    , this.gvData_SortBy + " " + (this.gvData_IsAsc == true ? "ASC" : "DESC"), limit));

            gvData.DataSource = sqlCmd.GetDataTable();
            gvData.DataBind();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Thats it you are ready with paging and sorting in gridview.

I am attaching sample with this article. Hope you will enjoy this!!
GridView Example With Paging Sorting

Cannot add duplicate collection entry of type ‘add’ with unique key attribute ‘value’

Hello,

Once I have transferred site from shared hosting to dedicated hosting and i got error “Cannot add duplicate collection entry of type add with unique key attribute value“.

see the below screenshot of error:
error

I have checked web.config file and IIS settings for this error.
I have seen below code in web.config file

 
    
      
        
        
        
        
        
        
      
    
 

This is for default document.

After that I have open IIS and check the site details, I have found below entries for the default documents:
iisdefault

So it occurs because of multiple entry of default page.

How can we solve it:To resolve this problem, first you must delete the duplicate entry that is defined at the highest level. The highest level may be the IIS server. After you delete the duplicate entry, the feature configuration page for the Web site or for the application should be displayed correctly.

Same event handler for multiple link buttons

Hello,

Today I am going to represent How to handle single event for multiple Link buttons in asp.net.

This is very useful concept and we will use it in our web application where we need to implement same event for multiple link buttons.

lets take an example, Suppose we want to implement alphabetical search in our web application then we will create link buttons for A,B,C…..Z.

see below code


..........................
..........................




...
...
...
...


..........................
..........................

Here, I have defined multiple link buttons for each alpha but all alpha contains same event handler.

Now How can we decide which link button has been clicked by user.
see below code

protected void Alpha_Click(object sender, EventArgs e)
{
     LinkButton btn = sender as LinkButton;
     if (btn == null) { return; }
     
     Response.Write(btn.Text);
}

Now we can use this in our query for finding records on the base of clicked link button.

how to detect mobile browser in javascript

Hello All,

Today I am going to describe how to detect mobile browser. Now a days mobile application is common and we arise this situation to detect the mobile browser.

we can easily implement this functionality in different language like java, asp.net, cold-fusion, c# etc.

Generally we can use javascript easily in different languages. so today i am going to descibe this functionality using javascript.

You know that navigator.userAgent provides the information related to browser. so i have used it for browser detection.

see the below javascript function for mobile browser detection.

 function DetectMobileOS()
        {
            var deviceWebos = "webos";
            var deviceIphone = "iphone";
            var deviceIpad = "ipad";
            var deviceIpod = "ipod";
            var devicePalm = "palm";
            var deviceS60 = "series60";
            var deviceSymbian = "symbian";
            var deviceAndroid = "android";
            var deviceWinMob = "windows ce";
            var deviceWinPhone = "windows phone";
            var deviceBB = "blackberry";
            var deviceZuneWP7 = "ZuneWP7";
            var deviceIEMobile = "IEMobile";
            var deviceOperaMini = "Opera Mini";
            var deviceBada = "bada";

            var uagent = navigator.userAgent.toLowerCase();
            
            if (
                    uagent.search(deviceWebos) > -1 ||
                    uagent.search(deviceIphone) > -1 ||
                    uagent.search(deviceIpad) > -1 ||
                    uagent.search(deviceIpod) > -1 ||
                    uagent.search(devicePalm) > -1 ||
                    uagent.search(deviceS60) > -1 ||
                    uagent.search(deviceSymbian) > -1 ||
                    uagent.search(deviceAndroid) > -1 ||
                    uagent.search(deviceWinMob) > -1 ||
                    uagent.search(deviceWinPhone) > -1 ||
                    uagent.search(deviceBB) > -1 ||
                    uagent.search(deviceZuneWP7) > -1 ||
                    uagent.search(deviceIEMobile) > -1 ||
                    uagent.search(deviceOperaMini) > -1 ||
                    uagent.search(deviceBada) > -1
                )
            {
                return true;
            }
            else
            {
                return false;
            }

        }

How can we use it in our code?

 if (DetectMobileOS())
 {
     //write code for the mobile browser
 }
 else
 {
    //write other code
 }

Hope this will help.

Example of gridview binding

Hello Friends,

Today i want to show you how to bind gridview? I have created one simple example of gridview binding.
To create example i have used VS 2010 and Adventures database.

First create one web site/web application and create one page. Paste below code in your aspx page.



       
       
       
       
       
       


Now, in .cs file use following code to bind grid view.

            try
            {
                DBM.SqlCommandEx sqlCmd = new DBM.SqlCommandEx(
                        "SELECT TOP 10 Person.BusinessEntityID, FirstName, LastName, AddressLine1, City, PostalCode" +
                        " FROM Person.Person" +
                        " INNER JOIN Person.BusinessEntity on BusinessEntity.BusinessEntityID = Person.BusinessEntityID" +
                        " INNER JOIN Person.BusinessEntityAddress on BusinessEntityAddress.AddressID = BusinessEntity.BusinessEntityID" +
                        " INNER JOIN Person.Address on Address.AddressID = BusinessEntityAddress.AddressID" +
                        " ORDER BY FirstName");

                gvData.DataSource = sqlCmd.GetDataTable();
                gvData.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }

Now, you are ready. Hope it will help to beginner to bind data grid.
I am attaching example and other needed file with this article.
Download it from here GridViewExample.rar

How to redirect non www to www in asp.net

Hello All,

Today I am going to describe most common problem that we are encounter in these days.

Today we seen that most of the domains are work both ways i.e with and without “www” prefix.

Redirection of Non-WWW domain to WWW (301 Redirection – permanent) is very important for ranking on search engine like Google.

In SEO, The problem is that the Google search consider http://domain.com to be a different domain than http://www.domain.com. so it makes the difference in search engine back links because you have made it with and without www prefix.

for SEO rankings; It is better to have every link use exactly the same form of your domain. for this purpose, it is common to redirect request from non-www to www.

Here I am going to describe how to Redirect non www to www in ASP.NET

we can do it in asp.net by many ways like using IIS, web.config, URL rewrite, Global.asax etc.
1. IIS : you can make a URL rewrite rule in IIS for the non www to www redirection
2. web.config : It is most common practice to do the redirection from non-www to www because we don’t have the IIS access on shared hosting environment.
see the below code for the redirection

.........................
.........................
.........................
.........................
<system.webServer>
  <rewrite>
    <rules>
    <clear />
    <rule name="WWW Rewrite" enabled="true">
      <match url="(.*)" />
        <conditions>
          <add input="{HTTP_HOST}" negate="true"
            pattern="^www\.([.a-zA-Z0-9]+)$" />
        </conditions>
        <action type="Redirect" url="http://www.{HTTP_HOST}/{R:0}"
          appendQueryString="true" redirectType="Permanent" />
      </rule>
    </rules>
  </rewrite> 
<system.webServer>  
.........................
.........................
.........................
.........................

3. URL rewrite : You can make your own HTTP module for the redirection
4. Global.asax : You can add your own code in Application_BeginRequest event
see below example

if (!HttpContext.Current.Request.Url.ToString().ToLower().Contains("http://www.domain.com"))
{
   string postedURL = System.Web.HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority);
   HttpContext.Current.Response.Status = "301 Moved Permanently";
   HttpContext.Current.Response.AddHeader("Location", Request.Url.ToString().ToLower().Replace(postedURL,"http://www.domain.com"));
}

Hope this will help you.