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,
 ROW_NUMBER()OVER (ORDER BY Column1) AS Column2
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

Output:

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

Thanks. R./

Advertisements

, , , , ,

3 Comments

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..

2007:

2010:

Step 5: Happy Coding..!

References:

1. http://office.microsoft.com/en-us/infopath-help/change-the-programming-language-of-a-form-template-HA010203470.aspx

2. http://office.microsoft.com/en-us/infopath-help/set-the-scripting-language-for-a-form-template-HA010173444.aspx

Leave a comment