Using CAML Query Builder for SharePoint

To know about the Basics of the CAML query read this article.

Download Link

Use the following link to download the CAML Query builder.

http://www.u2u.be/res/Tools/files/CamlBuilderSetupv4.0.0.0.zip
Update: CAML query builder for 2007, 2010, 2013 and SP Online link

How to use CAML Query Builder to create a query

Following are the steps for using the CAML query builder to create a CAML query

1. Open the CAML query builder. Type the URL to connect to the SharePoint site and check Credentials of the current user to login

2. Once clicked connect, you will see the following window SharePoint site with all the lists and libraries in the site in a tree view structure which is shown below: 

3. Now we will create a new query on a list named “Product List” as shown below:

4. We will start creating a new query which is as follows:

5. You will find the query being created depending upon your selection from the list

6. Clicking on Execute, you will see the query being executed and the result is being displayed in the result tab as shown below:

The Query created is:

<Query>

           <Where>

                       <Gt>

                              <FieldRef Name="Quantity" />

                              <Value Type="Number">0</Value>

                      </Gt>

          </Where>

          <OrderBy>

                       <FieldRef Name="Price" Ascending="True" />

          </OrderBy>

</Query>

Hence, CAML Query Builder allows us to create the queries very easily so that it can be used in the SharePoint programming.

Now, we will have a question, how to use this query in the Visual Studio to retrieve the data from a list.

To know how to use CAML query in Visual Studio programming, read this article.

Read More

Import Excel Sheet to SharePoint List

In this article, we will learn how to export the excel data to the SharePoint list.

Steps:

1. Click on Site Actions -> More Options 

2. Now click on the Lists -> Select Import Spread Sheet from the type -> then Click Create 

3. Give the Name and Description of the List to be created and then browse the Excel Sheet to be uploaded

4. Clicking on Import option, you will find see a popup window opens. Select Range of Cells in the Range Type

5. This option allows you to select the table columns and rows in excel as shown below. After selecting, you will see the selected field’s column and row data being populated in the Select Range field in the popup window

6. Now click on Import in the popup window. Your SharePoint list will be created from the Excel Sheet and the values are also populated as shown below:

You may face some issues while importing the Excel Sheet to SharePoint and the solution is follows:

Error: “Method ‘Post’ of object ‘IOWSPostData’ failed” in SharePoint

Solution:

 Ø  Open the Excel Sheet Add-In EXPTOOWS.XLA which will is available at the following location C:\Program Files\Microsoft Office\Office12\1033 by default.

 Ø  Press Alt+F11 to display the Visual Basic code editor. Locate the form named "PublishForm" under the folder "Forms" and open the code view of this form.

 Ø  Search (Ctrl+F) for the line "lVer = Application.SharePointVersion(URL)" and place a new line "lVer=2" after that line.

 Ø  Now try to import the Excel Sheet to the SharePoint, you will not get any errors.

Error: "The specified file is not a valid spreadsheet or contains no data to import"

Solution:

This error occurs when the site you are opening do not have permission to file system. To give permission follow the below steps:

In Internet Explorer click Tools --> Internet Options -> Click Security tab --> Select Trusted Sites --> In the Sites add your URL here.

Now you will be able to import the file properly.

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

Read More

CAML Query tutorial for SharePoint

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.

Read More

Creating FAVICON for WSS 3.0 and MOSS 2007

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.

Read More

SharePoint site continually prompting for passwords

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.

Read More

Changing Favicon in SharePoint 2010

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. 

Read More

Programmatically get SharePoint Users with Group Name

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.

Read More

RegisterForEventValidation can only be called during Render()

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.

Read More

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

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.

Read More

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

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.

Read More