SOLVED

CAML Query to filter library not working as expected

Copper Contributor

below the is the CAML query i am using in my code for fetching data and binding to DATALIST control...

 

 _query.Query = "<View><Query><Where><And><Eq><FieldRef Name='Description0' /><Value Type='Text'>" + myParam.ToString() + "</Value></Eq></And><And><Geq><FieldRef Name='Modified' /><Value IncludeTimeValue='FALSE' Type='DateTime'><Now /></Value></Geq></And></Where><OrderBy><FieldRef Name='Modified' Ascending='True' /></OrderBy></Query></View>";

 

from above query i am getting result but not as expected , means my condition to fetch based on datetime not working and also on description.

 

please help.

 

17 Replies

@badar_000 

 

You should use two condition in AND tag and here you need only one AND tag according to your requirement. Please use below query and it should work

 

<View>
	<Query>
		<Where>
			<And>
				<Eq>
					<FieldRef Name='Description0' />
					<Value Type='Text'>You Value</Value>
				</Eq>
				<Geq>
					<FieldRef Name='Modified' />
					<Value IncludeTimeValue='FALSE' Type='DateTime'>
						<Now />
					</Value>
				</Geq>
			</And>
		</Where>
		<OrderBy>
			<FieldRef Name='Modified' Ascending='True' />
		</OrderBy>
	</Query>
</View>

 

CAML Query Documentation: https://docs.microsoft.com/en-us/sharepoint/dev/schema/query-schema

 

Hope it will help to you. 

 

 

 

@kalpeshvaghela 

 

I tried below after your comments but result is same , thanks for your reply..

 

_query.Query = "<View><Query>" +
"<Where><And>" +
"<Eq><FieldRef Name='Description0' /><Value Type='Text'>" + myParam.ToString() + "</Value></Eq>" +
"<Geq><FieldRef Name='Modified'/><Value IncludeTimeValue='FALSE' Type='DateTime'><Now /></Value></Geq>" +
"</And>" +
"</Where>" +
"<OrderBy><FieldRef Name ='Created' Ascending = 'True' /></OrderBy></Query></View>";

 

 

@badar_000 

 

What is the type of Description0 field? if it's multiline of text then use below query.

 

<View>
	<Query>
		<Where>
			<And>
				<Eq>
					<FieldRef Name='Description0' />
					<Value Type='Note'>You Value</Value>
				</Eq>
				<Geq>
					<FieldRef Name='Modified' />
					<Value IncludeTimeValue='FALSE' Type='DateTime'>
						<Now />
					</Value>
				</Geq>
			</And>
		</Where>
		<OrderBy>
			<FieldRef Name='Modified' Ascending='True' />
		</OrderBy>
	</Query>
</View>

@kalpeshvaghela 

 

Description is single line of text, pls find below screenshot

 

badar_000_0-1659937741076.png

 

Could you share screen shot of your list or library and show which record is not being displayed according to condition because ideally query is correct and it should show result

@kalpeshvaghela 

as requested, please find below screenshot of library...

badar_000_0-1659940394975.png

 

@badar_000 

 

There could be two reasons

1. Make sure that you are using correct internal name of the column in CAML Query because in CAML Query it's being used as "Description0"

2. Make sure that there is not white space in the column value.

 

It would be also good if you can share your exact generated query as well to find exact root cause of the issue. I want to see what exactly being replaced for myParam.ToString()  

@kalpeshvaghela 

 

below is the string assigned to myParam - parameter.

string myParam = "test9"; 

 

also i have gone through values , spaces are not there.

 

regards

@badar_000 

  1. You are using Geq (greater than or equal to) for you date time comparison (Modified column).
  2. In screenshot you provided above, Modified column value is from yesterday. 

So, you will not get the "GulfNews" file in your results as Modified column is not greater than or equal to current date. 
Are you trying to get the files which are modified today or before today? In this case, use Leq operator instead of Geq


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Hi Ganesh, I am just trying to get the values , the condition is not working for <Eq> or <Geq> or <Leq> , the issue is with the condition , query is giving me result also.

@badar_000 Which values/files are you trying to fetch exactly? 

 

Can you explain your requirements & conditions in detail with the expected results/files from above screenshot? 


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Hi Ganesh,

Thanks for your reply,

My requirement is based on the query below ,

query.Query = "<View><Query>" +
"<Where><And>" +
"<Eq><FieldRef Name='Description0' /><Value Type='Text'>" + myParam.ToString() + "</Value></Eq>" +
"<Geq><FieldRef Name='Modified'/><Value IncludeTimeValue='FALSE' Type='DateTime'><Now /></Value></Geq>" +
"</And>" +
"</Where>" +
"<OrderBy><FieldRef Name ='Created' Ascending = 'True' /></OrderBy></Query></View>";

after executing above query i need to get the data based on description and modified date , thats it.

regards

@badar_000 

 

I have faced some time issue like "<Now/> is not working properly in CAML Query.

Could you try below instead of <Now/> in caml query?

 

 

DateTime.Now.Date.ToString("yyyy-MM-ddTHH:mm:ssZ")

 


Hope it will helpful to you.

 

@kalpeshvaghela 

 

i used below query as mentioned by you , but result is same..not as expected...date is not working..

 

_query.Query = "<View><Query>" +
"<Where>" +

"<Gt>" +
"<FieldRef Name='Modified'/>" +
"<Value IncludeTimeValue='TRUE' Type='DateTime'>DateTime.Now.Date.ToString('yyyy - MM - ddTHH:mm: ssZ')</Value>" +
"</Gt>" +

"</Where>" +
"<OrderBy><FieldRef Name ='Created' Ascending = 'True' /></OrderBy></Query></View>";

@badar_000 


It seems that you have added space in format string?

With space: DateTime.Now.Date.ToString('yyyy - MM - ddTHH:mm: ssZ')

 

Can you try without space?

Without space: DateTime.Now.Date.ToString('yyyy-MM- ddTHH:mm: ssZ')

 

 

Hi, as mentioned i modified and used below query, still result is same...its not fetching based on date...
_query.Query = "<View><Query>" +
"<Where>" +
"<Gt>" +
"<FieldRef Name='Modified'/>" +
"<Value IncludeTimeValue='TRUE' Type='DateTime'>DateTime.Now.Date.ToString('yyyy-MM-ddTHH:mm:ssZ')</Value>" +
"</Gt>" +
"</Where>" +
"<OrderBy><FieldRef Name ='Created' Ascending = 'True' /></OrderBy></Query></View>";
best response confirmed by badar_000 (Copper Contributor)
Solution

@kalpeshvaghela @ganeshsanap 

 

Dear All, I used below format and it worked, thanks for your support... I am sharing below CAML code...

string Iso8601Today = XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Local);

 

_query.ViewXml = "<View><Query>" +
"<Where>" +

"<Leq>" +
"<FieldRef Name='testDate'/>" +
"<Value IncludeTimeValue='FALSE' Type='DateTime'>"+Iso8601Today+"</Value>" +
"</Leq>" +
"</Where>" +
"<OrderBy><FieldRef Name ='Created' Ascending = 'True' /></OrderBy></Query></View>";

 

I got the required result, please add your comment for more optimization ...

 

regards

Badar

1 best response

Accepted Solutions
best response confirmed by badar_000 (Copper Contributor)
Solution

@kalpeshvaghela @ganeshsanap 

 

Dear All, I used below format and it worked, thanks for your support... I am sharing below CAML code...

string Iso8601Today = XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Local);

 

_query.ViewXml = "<View><Query>" +
"<Where>" +

"<Leq>" +
"<FieldRef Name='testDate'/>" +
"<Value IncludeTimeValue='FALSE' Type='DateTime'>"+Iso8601Today+"</Value>" +
"</Leq>" +
"</Where>" +
"<OrderBy><FieldRef Name ='Created' Ascending = 'True' /></OrderBy></Query></View>";

 

I got the required result, please add your comment for more optimization ...

 

regards

Badar

View solution in original post