May 8, 2012

CAML Query tutorial for SharePoint

15 comments  
In this article, we will understand the basics of CAML query in SharePoint.
What is CAML?
  Ø  CAML - Collaborative Application Markup Language
  Ø  XML- Extensible Markup Language based query language
  Ø  Used to perform a query operation against SharePoint Lists
How SharePoint List Items are retrieved?
SharePoint List data can be retrieved in any one of the following ways:
1. Using the SharePoint object model – used when code runs on the server (Example: Developing a web part or an application page)
2. Using the SharePoint Lists web service – used when your code doesn’t run on the server where the SharePoint is installed (Example: Developing a windows application)
3. Using Power shell –used mostly by the ADMIN of the SharePoint when they quickly want to retrieve some information from the SharePoint site
How does CAML query looks like?
As I already mentioned, it is XML based query language and it contains tags in it. The root element of the CAML query root element is Query. But it is not necessary to use Query element in the query you form.
Within the Query element you have two elements possible:
1. Where   – to filter the data
2. OrderBy – to categorize the data
A simple structure of the CAML query is as follows:
<Query>
          <Where>
                   <Eq>
                             <FieldRef Name=”FieldName” />
                             <Value Type=”DataType”>Value</Value>
                   </Eq>
          </Where>
          <OrderBy>
                             <FieldRef Name=”FieldName” />
                             <FieldRef Name=”FieldName” />
          </OrderBy>
</Query>
Operators in CAML Query
From the above structure, we came to know that it uses Where and OrderBy elements to retrieve the data from the list.
Let us know about the operators present in the CAML query and its usage:
Inside the Where element
1. Logical Operators - AND, OR
2. Comparison Operators - Listed Below in the table
AND – Which takes two conditions are satisfied
OR – Which takes when either of the conditions is satisfied
Comparison Operators

Inside the OrderBy/GroupBy element
OrderBy – Which orders or sort the data depends upon the field (FieldRef element) given.
GroupBy – Which groups the data depends upon the group by field (FieldRef element) given.
Examples
Logical & Comparison Operators
Use of AND, Gt, Leq
<Query>
<Where>
<And>
<Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
</Gt>
<Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
</Leq>
</And>
</Where>
</Query>
Use of OR, Gt, Leq
<Query>
<Where>
<Or>
    <Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
    </Gt>
                     <Leq>
  <FieldRef Name="Price" />
<Value Type="Number">2000</Value>
    </Leq>
               </Or>
       </Where>
</Query>
Use of BeginsWith, Leq
<Query>
<Where>
<And>
     <BeginsWith>
 <FieldRef Name="Title" />
 <Value Type="Text">M</Value>
     </BeginsWith>
     <Leq>
    <FieldRef Name="Quantity" />
<Value Type="Number">1000</Value>
     </Leq>
</And>
</Where>
<OrderBy>
<FieldRef Name="Price" Ascending="False" />
</OrderBy>
</Query>
OrderBy Operator
<Query>
<Where>
<Or>
   <Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
  </Gt>
    <Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
  </Leq>
</Or>
</Where>
<OrderBy>
<FieldRef Name="Price" Ascending="True" />
</OrderBy>
</Query>
Is it possible to write this queries without any errors manually?
Yes. But we will know the errors only after executing the program. Hence there is a free CAML query builder which will generate the query easily.
To know about where to download and how to use the CAML Query builder read this article.
To know how to use the CAML query in the Microsoft Visual Studio read this article.

May 6, 2012

Creating FAVICON for WSS 3.0 and MOSS 2007

No comments  
In this article, we have learned how to Changing the FavIcon in SharePoint 2010.
But there is no favIcon for WSS 3.0 and MOSS 2007 by default. Then how to create favicon for the lower versions of the SharePoint. Here is the solution.
Steps:
1. Open the SharePoint site (WSS 3.0 or MOSS 2007) in SharePoint designer 2007
2. Edit the master page
3. Add the below tag before end of the HEAD tag in the master page
<link rel=”shortcut icon” href=”/SiteImages /favicon.ico” /> 
4. Now save the master page and Check in the page
5. Refresh the SharePoint page, you will see the FAVICON  in the address bar of the SharePoint site.
To know more about:
1. What is favicon ? 
2. Creating favicon
3. Use of favicon
Read this article.

May 5, 2012

SharePoint site continually prompting for passwords

No comments  
Scenario:
I got a scenario where the SharePoint site is not allowing a user to login to the site even on giving correct username and password.
It is an intranet site and hence it will take the Windows Authentication.
But after 3 consecutive wrong attempts, password should be locked since it is taking the username and password from the Active Directory. It doesn’t happen. I have checked the Active directory and it is unlocked only.

So, what is the problem stopping the user to login to the site? Read the solution below.

Solution:
I have analyzed the problem in the following ways and arrived at the solution. 
The root cause of the issue may be any one of the following issues:
Adding the SharePoint site to the Local Intranet
1. Open IE on your computer, go to TOOLS > INTERNET OPTIONS, and then Select the Security tab. Click on the Local Intranet zone icon, and then the Sites button.
2. You will see a pop up -> Local Intranet -> click Advanced -> Local Intranet -> where you can add the site SharePoint site URL. Example: http://*.domain name – http://Sitename.domainname. Click the Close and OK.

3. Click OK to exit the Internet Options box, and close all Internet Explorer windows. Open a new Explorer window and try to browse to your SharePoint site. You should be logged in automatically using your Windows credentials.

If you are still unable to login prompt to the site, then follow the next instruction.

Removing the stored old username and password in the control panel
Reason for removing this password:
When we are checking the remember password option while logging into the SharePoint site, then the password is stored in the control panel under 
STORED USER NAMES AND PASSWORDS.
Hence, the SharePoint site is using the older password rather than the newer one (when the password is reset or changed for the particular account). So removing this would help us in resolving this issue.

To remove the saved password, follow the below steps:
In Windows XP, the path is: CONTROL PANEL > STORED USERNAMES AND PASSWORDS.
If you do not have the option you need in the Control Panel, there is a way to bring up the box via the Run box.
Go to START -> RUN and type the following: 
rundll32.exe keymgr.dll,KRShowKeyMgr 
Here you can remove the stored username and password. Now try logging in with the username and password and it will work as expected.

Hope this helps you! Please free to comment and share this post.

May 3, 2012

Changing Favicon in SharePoint 2010

3 comments  
What is Favicon?
All the top websites in the internet will have their favicon in the address bar. Similarly SharePoint 2010 sites also have its favicon in the address bar as shown below:
What is the use of Favicon?
When we are saving a site as a favorite then this icon will be saved for the reference with the site name starting in the favorites bar in any web browser. For example: Take Google you will see the Google symbol in the address bar. 
How to create Favicon (.ico file)?
I have used this website http://www.coolutils.com/online/image-converter/ for converting the image to .ico file which is favicon file. You can upload your company’s logo and convert it into .ico file in the mentioned site and download it from the site. (You can use any free convertors site online to create the icon file) 
How to set Favicon in the SharePoint 2010 site?
SharePoint 2010 displays an orange Favicon and it is present in the following folder C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\IMAGES\favicon.ico 
It will look like as follows:
Now, navigate to the above path and search for favicon.ico and rename the file to FaviconBackup.ico.
Now copy your favicon (.ico file) to this folder and make sure it is named as "favicon.ico".
Now refresh your SharePoint site and you will see the new favicon displayed in the address bar.
How to display different Favicons for different sites in the server farm?
The only way is we will change the favicon in the master page inorder to set different favicons for different sites.
Hence, edit the V4.master page file in the SharePoint designer and check out the file and look for the following in the code view:
<SharePoint:SPShortcutIcon runat="server" IconUrl="/_layouts/images/favicon.ico"/>
Now,upload the favicon file in the image library or store the file with different name in this folder. C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\IMAGES\newfavicon.ico
<SharePoint:SPShortcutIcon runat="server" IconUrl="/_layouts/images/ newfavicon.ico "/>
Give thenew URL in the tag and save the master page and check in the file. 
Refresh the SharePoint page and now you can set the different favicon for more than one site in the SP farm.


To know about creating FAVICON for WSS 3.0 and MOSS 2007 read this article. 

Programmatically get SharePoint Users with Group Name

6 comments  
In this article, we will come to know how to list all the users from the SharePoint site using c#.
Scenario:
We have to retrieve all the Users from the SharePoint site with the Group Name using object model.
Solution:
I have created a console application which will group the users with the Group Name and also display the User details.
Code (.cs):
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace UserPermissions_Console
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {                
                    GetUsersGroups();                
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error Occured: " + ex.Message);
            }
        }
        private static void GetUserRoles()
        {
            using (SPSite site = new SPSite("http://sitename"))
            {

                SPWeb web = site.OpenWeb();
                Console.WriteLine("\n\n Roles Assignments:");

                foreach (SPRoleAssignment roleA in web.RoleAssignments)
                {
                    Console.WriteLine("The following Role definition bindings exist for" + roleA.Member.Name);                    
                    foreach (SPRoleDefinition roledef in roleA.RoleDefinitionBindings)
                    {
                        Console.WriteLine(roledef.Name);                    
                    }
                }
                Console.ReadLine();
            }
        }
        private static void GetUsersGroups()
        {   
            using (SPSite site = new SPSite("http://sitename"))
            {

                SPWeb web = site.OpenWeb();
                SPGroupCollection groupCollection = web.SiteGroups;
                
                foreach (SPGroup group in groupCollection)
                {
                    SPUserCollection userCollection = group.Users;
                    Console.WriteLine("Group Name :" + group.Name+"\n");
                    
                    foreach (SPUser user in userCollection)
                    {
                        Console.WriteLine("User Name: " + user.Name + " Email: " + user.Email + " Login: " + user.LoginName);
                    }
                }
                //Iterate the owners group
                SPGroup ownerGroup = web.AssociatedOwnerGroup;                
                foreach (SPUser ownerUser in ownerGroup.Users)
                {
                    Console.WriteLine("User Name: " + ownerUser.Name + " Email: " + ownerUser.Email + " Login: " + ownerUser.LoginName);
                }
            }
            Console.ReadLine();
        }

    }
}
You can download the full solution using this link.
See Also
Check if current user is member of a SharePoint group using JQuery
Hope this helps you! Please free to comment and share this post.

May 1, 2012

RegisterForEventValidation can only be called during Render()

No comments  
To know about creating a Grid view with Paging and Exporting the view to Word and Excel documents read this article.
The following is the screen shot which you get due to this error:


This error occurs whenever we are trying to render control to response. To fix this problem I have added EnableEventValidation="false" to @Page directive of aspx page.
This will look like as follows:
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" EnableEventValidation="false" %>
This code will fix this issue.

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server

No comments  
To know about creating a Grid view with Paging and Exporting the view to Word and Excel documents read this article.
The following is the screen shot which you get due to this error:


This error occurs whenever you are trying to export the grid view data to excel or word. This occurs due to the reason that the compiler thinks that the control is not added in the form. Hence to resolve this issue, add the overriding function in the code
public override void VerifyRenderingInServerForm(Control control)
{
  /* Verifies that the control is rendered */
}

This code will fix this issue.

Grid View with Paging into Excel and Word in ASP.Net

1 comment  
After reading this article, you will come to know the following:
   1.   Using grid view control in the ASP.Net applications
   2.   Creating and connecting the SQL database in the ASP.Net applications
   3.   Creating PAGING in the grid view
   4.   Tabbed navigation in the ASP.Net application with single GRIDVIEW
   5.   Converting the grid view with paging to WORD and EXCEL document

Scenario:
   1.   We have to use grid view to display the details from the SQL database
   2.   We have to use single grid view control to display details from two different tables from the database
   3.   We have to enable PAGING in the grid view control
   4.   We have to convert the details displayed in the grid view to word and excel document

Solution:
   1.   I have created an ASP.Net Web application using Microsoft Visual Studio 2005.   
   2.   I have placed two buttons (as tabbed navigation) for displaying the details from two different tables in two tabs.
   3.   Also, you will find the solution for paging in the grid view control.
   4.  Then, after displaying the details, we can convert the details to word and excel document.
    
Code (.cs file):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
public partial class _Default : System.Web.UI.Page
{ 
    String strDetails = String.Empty;
    SqlConnection conn = new SqlConnection("Data Source=ServerName;Initial 
    Catalog=Database Name;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
   {
        if (!IsPostBack)
        {
            BindData(strDetails);
        }       
        GridView1.PageIndexChanging += new GridViewPageEventHandler(GridView1_PageIndexChanging);
    }        
    protected void btn1_Display_Click(object sender, EventArgs e)
    {
        strDetails = "";
        GridView1.PageIndex = 0;
        BindData(strDetails);
    }
    protected void btn2_Dipslay_Click(object sender, EventArgs e)
    {
        strDetails = "UserGroup";
        GridView1.PageIndex = 0;
        BindData(strDetails);
    }    
    protected void btn_ExportExcel_Click(object sender, EventArgs e)
   {              
        Response.Clear();
        Response.AddHeader("content-disposition","attachment;filename=FileName.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite =new HtmlTextWriter(stringWrite);
        //turn off paging before exporting the details
        GridView1.AllowPaging = false;
        strDetails = ViewState["strDetailsValue"].ToString();
        BindData(strDetails);
        GridView1.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
        //turn the paging on again after writing the values to the excel document
        GridView1.AllowPaging = true;
        BindData(strDetails);
        Response.Flush();
   }
    protected void btn_ExportWord_Click(object sender, EventArgs e)
   {
        Response.Clear();
        Response.AddHeader("content-disposition", attachment;filename=FileName.doc");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-word ";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        //turn off paging before exporting the details
        GridView1.AllowPaging = false;
        strDetails = ViewState["strDetailsValue"].ToString();
        BindData(strDetails);
        GridView1.RenderControl(hw);
        Response.Write(sw.ToString());
        Response.End();
        //turn the paging on again after writing the values to the excel document
        GridView1.AllowPaging = true;
        BindData(strDetails);
        Response.Flush();        
    }
    private void BindData(String strDetails)
    {
        DataSet ds = new DataSet();
        conn.Open();
        SqlCommand cmd;
        if (strDetails == "UserGroup")
        {
            cmd = new SqlCommand("Select * from table1", conn);
        }
        else
        {
            cmd = new SqlCommand("Select * from table2", conn);
        }
        SqlDataAdapter sqlAd = new SqlDataAdapter(cmd);
        sqlAd.Fill(ds);
        GridView1.Controls.Clear();
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
        GridView1.Visible = true;
        ViewState.Add("strDetailsValue", strDetails);
        conn.Close();     
    }
   //Handling the paging event in the grid view control 
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        strDetails = ViewState["strDetailsValue"].ToString();
        BindData(strDetails);
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
      /* Verifies that the control is rendered */
    }
  }
Hope this helps you! Please free to comment and share this post.
If you are getting the following error:
RegisterForEventValidation can only be called during Render();
Read this article to fix the error.
If you are getting the following error:
Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.
Read this article to fix the issue.