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