Wednesday, July 30, 2008 6:35 AMhi,
i need to show created, created by and document name into gridview based on created date.
for ex: I have two textbox one for "from date" and other for "to date" and a button. when user click on the button what are the documents available in document library between the two date.
i have tried this way,
SPQuery oQuery = new SPQuery();
oQuery.ViewFields = "<FieldRef Name='Created'/>" + "<FieldRef Name='FileLeafRef'/>" + "<FieldRef Name='Author'/>";
DateTime strFromDate = Convert.ToDateTime(txtFromDate.Text);
DateTime strToDate = Convert.ToDateTime(txtToDate.Text);
oQuery.Query = "<Where><FieldRef Name='Created'><Value Type=\"DateTime\" StorageTZ='TRUE'>" + strFromDate + "</Value><FieldRef Name='Created'><Value Type=\"DateTime\" StorageTZ='TRUE'>" + strToDate + "</Value></Where>";
SPListItemCollection items = splist.GetItems(oQuery);
DataTable dt = items.GetDataTable();
i am new to this technology.
i don't know how to use the spquery for this scenario.
please anyone can help me.......
thanks in advace........
Wednesday, July 30, 2008 7:03 AMyou need to use AND condition and the "less than" and "greater than" operators to get the result:
your query should look something like this :
at the start comment the oQuery.ViewFields line to be sure that there are no syntax mistakes in it.
check if this helps..
Wednesday, July 30, 2008 7:10 AMHi,
In order to make sure that your CAML Query works as it should, you can (and in my opinion, you should) test it with the U2U CAML Query Builder: http://www.u2u.info/SharePoint/U2U%20Community%20Tools/Forms/AllItems.aspx
What this tool does, is generate queries for your automatically and of course lets you try your existing queries.
If the query is successful in the Query Builder, it should be successfull in your code aswell.
And as previous posted mentioned, try commenting out the viewfields line to make sure there's no errors in there.
- Proposed As Answer by Tobias Zimmergren [MVP]MVP Wednesday, July 30, 2008 7:11 AM
Wednesday, July 30, 2008 10:16 AMDateTime values have to be converted before you can use them in a CAML query. You can use the SPUtility.CreateISO8601DateTimeFromSystemDateTime() method for this.
Also check this blog entry if you notice the time part of your DateTime values are ignored.
Thursday, July 31, 2008 4:13 AMhi All,
Thanks for your response.
after executing the following code, i am getting "A field or property with the name 'Created' was not found on the selected data source" error.
SPSitespsite = new SPSite(http://localhost); SPWeb spweb = spsite.OpenWeb(); SPList splist = spweb.Lists["Shared Documents"]; SPQuery oQuery = new SPQuery();
DateTime strFromDate = Convert.ToDateTime(txtFromDate.Text); DateTime strToDate = Convert.ToDateTime(txtToDate.Text); string convertedTime = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(strFromDate); string convertedTime1 = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(strToDate); string Query = @" <Where><And>
<FieldRef Name='Created' />
<Value Type='DateTime'>"+ convertedTime1 + @"</Value>
<FieldRef Name='Created' />
<Value Type='DateTime'>"+ convertedTime + @"</Value>
oQuery.Query = Query;SPListItemCollection items = splist.GetItems(oQuery);
DataTable dt = items.GetDataTable(); return dt;
First Time it was executing fine.
after that the Created column will be vanished.
please help me...
Friday, August 01, 2008 7:15 AM
I don't see you define any ViewFields in your query. The returned SPListItems only contains fields if you explicitly ask for them.