Archive for April, 2012

Sorting a column alphabetically and display the ordered number in the next column – SQL Server

Last week, I have faced an interview and I was asked to write a SQL query to display an ordered column in an unsorted table and the next column should be displayed the ranking number. See below:

Unsorted table:                                                                  Sorted table:


Looks like pretty simple no? but in an interview with 3 Interviewers little  bit difficult! I did answer in a different way but there are pretty straight forward methods available in the SQL Server. We will see all the methods here:

Step 1: Create a table

CREATE TABLE [dbo].[Table1](
[Column1] [varchar](10) NOT NULL

Step 2: Insert the values

INSERT INTO [dbo].[Table1]([Column1]) VALUES ('C')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('D')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('B')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('E')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('A')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('F')

Method 1: Getting the ASCII value of the character and then display the number. Eg: ASCII of ‘A’ is 65, so the value is 65-64=1.

SELECT Column1,
 ASCII(Column1)-64 AS Column2
FROM Table1
ORDER BY Column1

Method 2: Using the ROW_NUMBER ( ) function and order by the column. You can check the function here.

SELECT Column1,
FROM Table1

Method 3: Using the RANK ( ) function and order by the column. You can check the function here.

SELECT Column1,
 RANK() OVER (ORDER BY Column1) AS Column2
FROM Table1


That’s all! These are pretty easy methods, if we know about these then we can answer quickly.

Thanks. R./


, , , , ,


How to determine XPath of a User Control in InfoPath 2007

When I was doing customization in InfoPath, I had some difficulties to find the XPath of user controls, after spending several minutes I found a way to determine the XPath in InfoPath 2007. I just wanted to share this in my blog, because there is no any straight forward answer in the web.

Step 1: Create a blank InfoPath template and add an User Control from the Controls Design Tasks (On your right hand side of the InfoPath). In this example I have added two Text Boxes and a Button and re-named them.

Step 2: Select the user control and click the Data Source in the Design Tasks.

Step 3: Now, you can see the selected control’s highlighted name in the Drop Down List.

Step 4: Click the Drop Down arrow and select the Copy XPath in the list.

Step 5: That’s all, you can use the XPath in your code. 🙂

Thanks, R./

1 Comment

How to set the default programming language for InfoPath 2007/2010

Recently I have developed a DIP (Document Information Panel) for a SharePoint template using InfoPath, which is accessing the data from a SQL database. So I got a chance to play around with InfoPath and faced some issues with the trust level security and all, I will post the step by step details later. 🙂

Depend on our requirements we can choose the programming language in InfoPath, there are 4 different languages (According to my knowledge C#, VB.Net, JScript and VBScript) in InfoPath and there are some limitation/difficulties in scripting languages (debugging, not supported in browser forms and lack of resources), so we will stick with our familiar C# or VB.Net. We can use either Visual Studio Tools for Applications (VSTA) or Visual Studio, in this example I used VSTA.

Step 1: Open a InfoPath template which you want to set the programming language.

Step 2: For 2007: Go to Tools menu and select Form Options (Tools –> Form Options)

For 2010:  Go to Developer tab menu and select Language button  (Developer Menu –> Language)

Step 3: On the Form Options dialog box, select the programming on the list, then select the language in the Form template code language drop down list box and click Ok (Programming –> Form template code language)

For 2007:

For 2010:

Step 4: We are ready to rock 🙂  If you want to code in the Form loading event, just go to Tools –> Programming –> Loading Events..



Step 5: Happy Coding..!




Leave a comment