Archive for April, 2012
Sorting a column alphabetically and display the ordered number in the next column – SQL Server
Posted by Rajanihanth in SQL Server on April 29, 2012
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
That’s all! These are pretty easy methods, if we know about these then we can answer quickly.
How to determine XPath of a User Control in InfoPath 2007
Posted by Rajanihanth in InfoPath 2007, VSTA on April 5, 2012
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. 🙂
How to set the default programming language for InfoPath 2007/2010
Posted by Rajanihanth in .Net, InfoPath 2007, InfoPath 2010, VSTA on April 3, 2012
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)
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..!