How to Query a SharePoint List and return DataTable of results
I am trying to create a web part which contains a custom search box to query a list. The list is contained in the same site as the search. I have tried to create a search query using either CAML or SQL syntax without success. The current code attachment uses CAML.
The list is named "_Customer".
The search box is named "txtSearchBox"
If I execute the search without trying to query the specific list name I get a returned results from the entire collection in the DataTable. While this is good that I get a returned results it does not get me the specific targeted list results.
The attached image is two separate images combined together. The purple dotted line segment is the search box. The red dotted line is the "_Customer" list. The returned result DataTable actually is supposed to be displayed underneath the search web part. As you can see from this image it has not been included since it is empty. For clarification we changed the default "Title" column to be renamed to "Account Name" in the "_Customer" list.
What is wrong with the code? What happens now is when a search is done the page refreshes and returns nothing. By this I mean there is no error and the page renders normally as if the search was not performed. During postback it appears the browser thought for a few moments as if it might return a DataTable of results, but when the page comes back there are no results.
My end game is to create a dynamic search criteria using multiple search boxes to query a list or multiple lists for a robust result set. Some of the field names we will be targeting would include: name, phone, zip, account, id, etc.
Thanks.
The list is named "_Customer".
The search box is named "txtSearchBox"
If I execute the search without trying to query the specific list name I get a returned results from the entire collection in the DataTable. While this is good that I get a returned results it does not get me the specific targeted list results.
The attached image is two separate images combined together. The purple dotted line segment is the search box. The red dotted line is the "_Customer" list. The returned result DataTable actually is supposed to be displayed underneath the search web part. As you can see from this image it has not been included since it is empty. For clarification we changed the default "Title" column to be renamed to "Account Name" in the "_Customer" list.
What is wrong with the code? What happens now is when a search is done the page refreshes and returns nothing. By this I mean there is no error and the page renders normally as if the search was not performed. During postback it appears the browser thought for a few moments as if it might return a DataTable of results, but when the page comes back there are no results.
My end game is to create a dynamic search criteria using multiple search boxes to query a list or multiple lists for a robust result set. Some of the field names we will be targeting would include: name, phone, zip, account, id, etc.
Thanks.
using System;
using System.ComponentModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Data;
using Microsoft.Office.Server.Search.Query;
using Microsoft.Office.Server.Search.Administration;
using Microsoft.Office.Server.UserProfiles;
namespace ICR.Search
{
[ToolboxItemAttribute(false)]
public partial class SearchUserControl : UserControl
{
protected Button btnSearch;
protected TextBox txtSearchBox;
protected Label lblResults;
protected GridView gridSearchResults;
protected override void CreateChildControls()
{
txtSearchBox = new TextBox();
this.Controls.Add(txtSearchBox);
lblResults = new Label();
this.Controls.Add(lblResults);
btnSearch = new Button();
btnSearch.Text = "Find";
btnSearch.Click += new EventHandler(btnSearch_Click);
this.Controls.Add(btnSearch);
}
void btnSearch_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtSearchBox.Text))
{
lblResults.Text = "Please enter words to search for";
return;
}
ExecuteKeywordQuery(txtSearchBox.Text);
}
void ExecuteKeywordQuery(string keywordQueryText)
{
string searchword = txtSearchBox.Text;
SPQuery query = new SPQuery();
query.ViewAttributes = "Scope='Recursive'";
SPList list = SPContext.Current.Web.Lists["_Customer"];
query.Query = string.Format("<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>searchword</Value></Eq></Where>");
//SPListItem item = list.GetItems(query)[0];
if (list.Items.Count > 0)
{
DataTable result = new DataTable();
result.TableName = "SearchResults";
PopulateResultsGrid(result);
}
}
private void PopulateResultsGrid(DataTable resultsTable)
{
gridSearchResults = new GridView();
gridSearchResults.DataSource = resultsTable;
gridSearchResults.DataBind();
Controls.Add(gridSearchResults);
}
protected override void Render(HtmlTextWriter writer)
{
writer.Write("<table width='100%'><tr><td>");
txtSearchBox.RenderControl(writer);
writer.Write("</td><td>");
btnSearch.RenderControl(writer);
writer.Write("</td></tr><tr><td colspan='2'>");
lblResults.RenderControl(writer);
writer.Write("</td></tr><tr><td colspan='2'>");
if (gridSearchResults != null)
gridSearchResults.RenderControl(writer);
writer.Write("</td></tr></table>");
}
}
}ASKER
The solution you describe would be ok if targeting only a single column, plus the filtered dataset would be the only included data in a subsequent filter. The option I am attempting would give greater control over the returned data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
After changing the code to your recommended solution ... unfortunately the result set did not return anything. They browser spun for a few moments and the page refreshed, but other than those indications that the app was thinking no other visible changes were made. Also, no grid was returned. Not that I expected one, however, I wanted you to know what was and what was not returned.
I think you might need to create the grid control in the CreateChildControls method and also the PopulateResultsGrid must only have code to bind to the datatable and display the resultset.
ASKER
Sujl17, could you demonstrate your suggestion in a code snippet, please?
ASKER
I have added some debug code with numbering "one" through "eleven" as seen in the newly attached code. When attempting a new search the returned debug sequence was: One, Three, Four, Five, Two. The function "PopulateResultsGrid" was never called as the debug variables "Seven" and "Eight" were never called. So after the debug variable "Five" is called the if statement querying if "listItems" is null or count greater than 0 must return false as this is where the "PopulateResultsGrid" function is called. This leads me to believe that the CAML query may be the culprit ... any suggestions?
using System;
using System.ComponentModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Data;
using Microsoft.Office.Server.Search.Query;
using Microsoft.Office.Server.Search.Administration;
using Microsoft.Office.Server.UserProfiles;
namespace ICR.Search
{
[ToolboxItemAttribute(false)]
public partial class SearchUserControl : UserControl
{
protected Button btnSearch;
protected TextBox txtSearchBox;
protected Label lblResults;
protected GridView gridSearchResults;
public string numone;
public string numtwo;
public string numthree;
public string numfour;
public string numfive;
public string numsix;
public string numseven;
public string numeight;
public string numnine;
public string numten;
public string numeleven;
protected override void CreateChildControls()
{
txtSearchBox = new TextBox();
this.Controls.Add(txtSearchBox);
lblResults = new Label();
this.Controls.Add(lblResults);
btnSearch = new Button();
btnSearch.Text = "Find";
btnSearch.Click += new EventHandler(btnSearch_Click);
this.Controls.Add(btnSearch);
}
void btnSearch_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtSearchBox.Text))
{
lblResults.Text = "Please enter words to search for";
return;
}
numone = "ONE";
Response.Write(numone + "<br />");
ExecuteKeywordQuery(txtSearchBox.Text);
numtwo = "Two";
Response.Write(numtwo + "<br />");
}
void ExecuteKeywordQuery(string keywordQueryText)
{
string searchword = txtSearchBox.Text;
SPQuery query = new SPQuery();
query.ViewAttributes = "Scope='Recursive'";
SPList list = SPContext.Current.Web.Lists["_Customer"];
numthree = "THREE";
Response.Write(numthree + "<br />");
query.Query = string.Format("<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>searchword</Value></Eq></Where>");
numfour = "FOUR";
Response.Write(numfour + "<br />");
SPListItemCollection listItems = list.GetItems(query);
numfive = "FIVE";
Response.Write(numfive + "<br />");
if(listItems != null && listItems.Count > 0)
{
DataTable result = new DataTable();
result = listItems.GetDataTable();
result.TableName = "SearchResults";
PopulateResultsGrid(result);
numsix = "SIX";
Response.Write(numsix + "<br />");
}
}
private void PopulateResultsGrid(DataTable resultsTable)
{
numseven = "SEVEN";
Response.Write(numseven + "<br />");
gridSearchResults = new GridView();
gridSearchResults.DataSource = resultsTable;
gridSearchResults.DataBind();
Controls.Add(gridSearchResults);
numeight = "EIGHT";
Response.Write(numeight + "<br />");
}
protected override void Render(HtmlTextWriter writer)
{
numnine = "NINE";
Response.Write(numnine + "<br />");
writer.Write("<table width='100%'><tr><td>");
txtSearchBox.RenderControl(writer);
writer.Write("</td><td>");
btnSearch.RenderControl(writer);
writer.Write("</td></tr><tr><td colspan='2'>");
lblResults.RenderControl(writer);
writer.Write("</td></tr><tr><td colspan='2'>");
if (gridSearchResults != null)
{
gridSearchResults.RenderControl(writer);
numten = "TEN";
Response.Write(numten + "<br />");
}
writer.Write("</td></tr></table>");
numeleven = "ELEVEN";
Response.Write(numeleven + "<br />");
}
}
}ASKER
I have solved 90% of the problem. The returning result set was previously empty because of the syntax of the CAML. The syntax that is working is:
query.Query = string.Concat("<Where><Eq>
The changes to this query include: "string.Concat()"; and then wrapping the variable from the search field in commas encapsulated with closing and opening quotes: ", searchwork, " so the CAML reads the first section, middle section (searchword) and the last section and concatenates them together.
The final 10% would be allowing wildcard searching and an AND OR search capability. This particular search ONLY returns a result set if the keyword is exactly typed as an existing entry. What would be necessary is to create a second search field to search on another column value. I could then extrapolate it to include all available fields for an appropriate query. A sample query may include: title, first name, last name, work phone, date entered. This would allow the user to search the list with a custom advanced search function.
query.Query = string.Concat("<Where><Eq>
The changes to this query include: "string.Concat()"; and then wrapping the variable from the search field in commas encapsulated with closing and opening quotes: ", searchwork, " so the CAML reads the first section, middle section (searchword) and the last section and concatenates them together.
The final 10% would be allowing wildcard searching and an AND OR search capability. This particular search ONLY returns a result set if the keyword is exactly typed as an existing entry. What would be necessary is to create a second search field to search on another column value. I could then extrapolate it to include all available fields for an appropriate query. A sample query may include: title, first name, last name, work phone, date entered. This would allow the user to search the list with a custom advanced search function.
ASKER
Alright, I have now fixed another 5% of the total problem by adding a solution for wildcard searches. If I modify the CAML query to be:
query.Query = string.Concat("<Where><Con
the operator "<Contains>" allows the wildcard. I removed the previous operator of "<Eq>". Now I need to get a working search of multiple text boxes for searching multiple list columns using an "AND" or "OR" operator.
query.Query = string.Concat("<Where><Con
the operator "<Contains>" allows the wildcard. I removed the previous operator of "<Eq>". Now I need to get a working search of multiple text boxes for searching multiple list columns using an "AND" or "OR" operator.
ASKER
Thank you.
aren't you trying to re-invent the wheel? The described functionality can be achieved with a list view and filter web parts, all out of the box.
cheers, teylyn