I have a SPQuery that getitems from a document libraries in different sites collections and display the result in listview, this query is generated from database table, everything works fine under 160 items, but when my database table >= 160 items, I have this error message : Value does not fall within the expected range this is a part of my code
private void BindReassessmentSelectorToControl()
{
private const string DROPDOWNLIBRARYFIELDS = "<FieldRef Name='{0}' /><FieldRef Name='{1}' /><FieldRef Name='{2}' /><FieldRef Name='{3}' /><FieldRef Name='{4}' /><FieldRef Name='{5}' /><FieldRef Name='{6}' />";
private const string CAMLEQINPAGID = "<Eq><FieldRef Name='InpAgId'/><Value Type='Text'>{0}</Value></Eq>";
private const string CAMLEQAGENTID = "<Eq><FieldRef Name='AgentId'/><Value Type='Text'>{0}</Value></Eq>";
private const string CAMLWHERE = "<Where>{0}</Where>";
string rootUrl = this.RootSiteUrl;
string sortKey = this.LastSortKey;
SortDirection sortDicection = this.LastSortDirection;
string currentUrlPage = HttpUtility.UrlEncode(HttpContext.Current.Request.Url.ToString());
using (DataTable reassessmentTable = Reassessment.GetReadOnlyReassement(ReassessmentStatus.RECEIVED, string.Empty, string.Empty, null))
{
List<string> conditionsFinancialStatement = new List<string>();
List<string> conditionsAssessment = new List<string>();
if (reassessmentTable != null && reassessmentTable.Rows.Count > 0)
{
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.OPENURL_FIELD, typeof(string));
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.SDCNAME_FIELD, typeof(string));
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.COUNTRY_CODE_FIELD, typeof(string));
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.FSFILEREF_FIELD, typeof(string));
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.FSFILELEAFREF_FIELD, typeof(string));
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.ASSFILEREF_FIELD, typeof(string));
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.ASSFILELEAFREF_FIELD, typeof(string));
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.INPASSDATE_FIELD, typeof(DateTime));
reassessmentTable.Columns.Add(SharePoint_Assessment_Fields.REVIEWTYPE_FIELD, typeof(string));
foreach (DataRow row in reassessmentTable.Rows)
{
string agentId = DataUtilities.GetSafeString(row[ReassessmentsColumns.CODE]);
string reassessmentId = DataUtilities.GetSafeString(row[ReassessmentsColumns.REASSESSMENTID]);
row[SharePoint_Assessment_Fields.OPENURL_FIELD] = SPUrlUtility.CombineUrl(currentWeb.Url, string.Format(OPENURL, reassessmentId, currentUrlPage));
if (!string.IsNullOrEmpty(agentId))
{
if (agentId.Length > 7)
{
agentId = agentId.Substring(0, 7);
}
string conditionFinancialStatement = string.Format(CAMLEQAGENTID, agentId);
string conditionAssessment = string.Format(CAMLEQINPAGID, agentId);
if (!conditionsFinancialStatement.Contains(conditionFinancialStatement))
{
conditionsFinancialStatement.Add(conditionFinancialStatement);
}
if (!conditionsAssessment.Contains(conditionAssessment))
{
conditionsAssessment.Add(conditionAssessment);
}
}
}
string whereFinancialStatement = string.Format(CAMLWHERE, this.BuildConditions(conditionsFinancialStatement));
string whereAssessment = string.Format(CAMLWHERE, this.BuildConditions(conditionsAssessment));
SPSecurity.RunWithElevatedPrivileges(delegate()
{
List<string> sdcNames = new List<string>();
string rootSiteUrl = this.RootSiteUrl;
using (SPSite rootSite = new SPSite(rootSiteUrl))
{
using (SPWeb rootWeb = rootSite.OpenWeb())
{
sdcNames = SummaryReportUtilities.GetSDCList(rootWeb);
}
}
foreach (string sdc in sdcNames)
{
Uri siteUri = new Uri(SPUrlUtility.CombineUrl(rootSiteUrl, SITESURL + sdc));
if (SPSite.Exists(siteUri))
{
using (SPSite sdcSite = new SPSite(siteUri.AbsoluteUri))
{
using (SPWeb sdcWeb = sdcSite.OpenWeb())
{
SPList assessmentList = sdcWeb.GetList(SPUrlUtility.CombineUrl(sdcWeb.ServerRelativeUrl, DocumentLibrary.EXCELASSESSMENTLIBRARY));
SPList dropdownList = sdcWeb.GetList(SPUrlUtility.CombineUrl(sdcWeb.ServerRelativeUrl, DocumentLibrary.DROPDOWN));
SPQuery financialStatementQuery = new SPQuery();
SPQuery assessmentQuery = new SPQuery();
DataTable financialStatement = null;
string webUrl = sdcWeb.Url;
string webUrlServerRelative = sdcWeb.ServerRelativeUrl;
financialStatementQuery.Query = whereFinancialStatement;
financialStatementQuery.ViewFields = string.Format(DROPDOWNLIBRARYFIELDS, SharePoint_Assessment_Fields.AGENTID_FIELD,
SharePoint_Assessment_Fields.SALESFORCECASENUMBER_FIELD, SharePoint_Assessment_Fields.COUNTRY_CODE_FIELD, SharePoint_Assessment_Fields.REVIEWTYPE_FIELD,
SharePoint_Assessment_Fields.FILELEAFREF_FIELD, SharePoint_Assessment_Fields.FILEREF_FIELD, SharePoint_Assessment_Fields.CREATED_FIELD);
try
{
if (dropdownList != null)
{
financialStatement = dropdownList.GetItems(financialStatementQuery).GetDataTable();
}
...
it's the last line in the code : financialStatement = dropdownList.GetItems(financialStatementQuery).GetDataTable();
that generate the error
SPQuery Value does not fall within the expected range.
when I debug financialStatementQuery this is the result :
<View><Query><Where><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7727839</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2321605</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2304400</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2343423</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2364191</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2361004</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2357073</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2354730</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2342336</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2340844</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2319424</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2363126</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2362382</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2360470</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2358355</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2357902</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2354866</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2350622</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2350530</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2349745</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2346783</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2331151</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2302786</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847708</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847291</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9125539</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847659</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6775733</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>5550033</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7823556</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847269</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847250</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847238</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6832028</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847200</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847105</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2360632</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2721369</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6763071</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8210775</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2357202</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2346315</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2358521</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>4725815</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>4720007</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8270463</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8212083</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2301810</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9128671</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8147118</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8147069</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8147023</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2354343</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2341861</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2329891</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2308644</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9123781</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9510017</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6532171</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9147397</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2321762</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2047204</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2023457</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6471071</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9128034</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9127671</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7824575</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7823946</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>1330887</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8211932</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8202600</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8211011</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8477081</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9047121</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9147025</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>1747011</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8147655</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2360050</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7721764</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7720199</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>3730298</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>1330236</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2357633</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2352416</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7728515</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7720024</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>5921628</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2047397</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7828438</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9047127</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7827017</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7037001</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>3847337</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8358244</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7820644</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7825207</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7821019</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6421305</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2022571</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>1531428</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2361553</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7827801</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8780000</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8770002</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6921059</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7720338</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>1436703</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7845335</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>3530898</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8121144</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6920188</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6023058</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7825179</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7825123</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7828237</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2031518</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7820632</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6750441</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6151205</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7721345</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7720306</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7957040</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>5880017</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>1721057</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9247115</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7720995</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9127211</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6021099</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7832103</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7832046</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>3845014</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6992355</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9120451</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8121349</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8121274</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8121260</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8121215</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8121158</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8120248</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8021158</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7847401</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7729075</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7721891</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7721562</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7721204</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7721200</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>5721217</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>3628751</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>2131821</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>1431716</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8847006</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>8820018</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7822543</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6932037</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>9222878</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>7120699</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>6932042</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>5747103</Value></Eq><Or><Eq><FieldRef Name='AgentId'/><Value Type='Text'>3537091</Value></Eq><Eq><FieldRef Name='AgentId'/><Value Type='Text'>5254546</Value></Eq></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or></Where></Query><ViewFields><FieldRef Name='AgentId' /><FieldRef Name='SalesforceCaseNumber' /><FieldRef Name='CountryCode' /><FieldRef Name='ReviewType' /><FieldRef Name='FileLeafRef' /><FieldRef Name='FileRef' /><FieldRef Name='Created' /></ViewFields><RowLimit Paged="TRUE">2147483647</RowLimit></View>
here I have 160 agentId, (for the same query but 159 agentId it works) any idea ? thanks
