Hi Corey,

I use FullTextSqlQuery. It doesn't have SelectProperties.Add method. Is there a way to return custom properties for the metadata?

Thanks

Dan

Here's my code-

using (Microsoft.Office.Server.Search.Query.FullTextSqlQuery fquery = new Microsoft.Office.Server.Search.Query.FullTextSqlQuery(src))

       {

           fquery.StartRow = 0;

           fquery.RowLimit = 10;

           fquery.HighlightedSentenceCount = 3;

           fquery.EnableStemming = true;

           fquery.TrimDuplicates = false;

           fquery.KeywordInclusion = KeywordInclusion.AllKeywords;

           fquery.ResultTypes = ResultType.RelevantResults;

           string strcolumns = "Title, IsDocument,WorkId,Title,Author,HitHighlightedSummary,Size,Path,PictureThumbnailURL,CollapsingStatus,SiteName,Write,Description,ContentClass,HitHighlightedProperties";

           fquery.QueryText = "SELECT " + strcolumns + " FROM Scope() WHERE ";

           fquery.QueryText += " \"Scope\" ='All Sites' AND ";

           fquery.QueryText += " FREETEXT(defaultproperties,'Report', 'HR') ) ";

           fquery.QueryText += " ORDER BY Rank Desc";

           ResultTableCollection rtCol = fquery.Execute();

           ResultTable relevantResults = rtCol[ResultType.RelevantResults];

           DataTable dt = new DataTable();

           dt.Load(relevantResults, LoadOption.OverwriteChanges);

           gridSearch.DataSource = dt;

           gridSearch.DataBind();

       }

Hi,

How can I search for both keywords as well as managed properties in keyword query? Is this correct?

keywordQuery.QueryText = string.Concat(keywords + string.Format(" +Color:\"{0}\"","Red"));

where 'keywors' contains the string to be searched in all results where color=red.

How can I use the Earlier Than and Later Tahn expressions in the KeywordQuery.KeywordQueryText syntax?

@Tom Those are only supported in SharePoint 2010.  If you are using SharePoint 2010, you simply use the > or < operators.  For example "write < '6/3/0/2010".

That's a bad news!  

Is there any way to achieve a similar filter effect in SharePoint 2007?  I'm developing a web part that basically creates a property search programmatically - it needs to replicate some of the capability of the advanced search property restrictions - so a typical query would be DocumentDate "Earlier Than" 7/2/2010. (DocumentDate is a custom Managed Property with a datetime data type).

Here is the code I have so far:

using System;

using System.Runtime.InteropServices;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Data;

using System.Drawing;

using System.Xml.Serialization;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

using Microsoft.Office.Server;

using Microsoft.Office.Server.Search.Query;

namespace CustomWebParts

{

   [Guid("01482ed3-b90c-428d-bacd-706267645f89")]

   public class CustomDateSearch : System.Web.UI.WebControls.WebParts.WebPart

   {

       Label lblDateSearch;

       Label lblQueryResults;

       DropDownList ddlYear;

       Button btnSearch;

       DateTime dtToday;

       DateTime dtPastYear;

       DateTime dtPast2Years;

       DateTime dtPast5Years;

       String strPastYear;

       String strPast2Years;

       String strPast5Years;

       String strSelectedValue;

       DataGrid grdResults;

       public CustomDateSearch()

       {

       }

       protected override void CreateChildControls()

       {

           base.CreateChildControls();

           //DateTime Variables

           dtToday = DateTime.Today;

           dtPastYear = dtToday.AddYears(-1);          

           dtPast2Years = dtToday.AddYears(-2);

           dtPast5Years = dtToday.AddYears(-5);

           //String Variables

           strPastYear = dtPastYear.ToString().Remove(10);          

           strPast2Years = dtPast2Years.ToString().Remove(10);

           strPast5Years = dtPast5Years.ToString().Remove(10);

           //Label

           lblDateSearch = new Label();

           lblDateSearch.Text = "Year Range Search: ";

           this.Controls.Add(lblDateSearch);

           //Drop Down List

           ddlYear = new DropDownList();

           ddlYear.Items.Add(new ListItem(" Select a Search Option", "0"));

           ddlYear.Items.Add(new ListItem(" Past Year", strPastYear));

           ddlYear.Items.Add(new ListItem(" Past 2 Years", strPast2Years));

           ddlYear.Items.Add(new ListItem(" Past 5 Years", strPast5Years));            

           this.Controls.Add(ddlYear);

           //Search Button

           btnSearch = new Button();

           btnSearch.Text = "Search";

           btnSearch.Click += new EventHandler(cmdSearch_Click);

           this.Controls.Add(btnSearch);  

           //Test Label

           lblQueryResults = new Label();

           lblQueryResults.ForeColor = Color.Red;

           this.Controls.Add(lblQueryResults);

       }

       void cmdSearch_Click(object sender, EventArgs e)

       {

           strSelectedValue = ddlYear.SelectedValue.ToString();

           if (strSelectedValue != "0")

           {

               lblQueryResults.Text = " ";

               keywordQueryExecute(strSelectedValue);

           }

           else

           {

               lblQueryResults.Text = "  *You Must Select a Search Option!";

           }          

       }

       private void keywordQueryExecute(string strQueryText)

       {

           KeywordQuery kRequest = new KeywordQuery(ServerContext.Current);

           string strQuery = "DocumentDate:" + "6/29/2009";

           kRequest.SelectProperties.Add("Title");

           kRequest.SelectProperties.Add("SiteName");

           kRequest.SelectProperties.Add("HitHighlightedProperties");

           kRequest.SelectProperties.Add("Path");

           kRequest.SelectProperties.Add("Author");

           kRequest.SelectProperties.Add("Write");

           kRequest.SelectProperties.Add("DocumentDate");

           kRequest.SortList.Add("DocumentDate", Microsoft.Office.Server.Search.Query.SortDirection.Ascending);

           kRequest.QueryText = strQuery;

           //Configure the query to return relevant results

           kRequest.ResultTypes |= ResultType.RelevantResults;

           //Execute the query

           ResultTableCollection resultsTbls = kRequest.Execute();

           if ((int)ResultType.RelevantResults != 0)

           {

               ResultTable tblResults = resultsTbls[ResultType.RelevantResults];

               if (tblResults.TotalRows == 0)

               {

                   lblQueryResults.Text = " *No Search Results Returned!";

               }

               else

               {

                   //lblQueryResults.Text = tblResults.TotalRows.ToString();

                   ReadResultsTable(tblResults);

               }

           }

       }

       void ReadResultsTable(ResultTable rt)

       {

           //Create a Data Table

           DataTable relResultsTbl = new DataTable();

           relResultsTbl.TableName = "Relevant Results";

           //Create a Data Set, add the Data Table to the Data Set, Load Data into the Data Table

           DataSet ds = new DataSet("resultsset");

           ds.Tables.Add(relResultsTbl);

           ds.Load(rt, LoadOption.OverwriteChanges, relResultsTbl);

           //Add the Data Set to the Data Grid (Display)

           fillResultsGrid(ds);

       }

       private void fillResultsGrid(DataSet grdDs)

       {

           //Create an instance of the DataGrid and set its

           //DataSource property to the supplied DataSet

           grdResults = new DataGrid();

           grdResults.DataSource = grdDs;

           //Set the display properties in the data grid

           grdResults.GridLines = GridLines.None;

           grdResults.CellPadding = 4;

           grdResults.Width = Unit.Percentage(100);

           grdResults.ItemStyle.ForeColor = Color.Black;

           grdResults.ItemStyle.BackColor = Color.White;

           grdResults.ItemStyle.Font.Size = FontUnit.Smaller;

           grdResults.ItemStyle.Font.Name = "Tahoma";

           //Set the display properties in the data grid Header

           //grdResults.HeaderStyle.BackColor = Color.Navy;

           //grdResults.HeaderStyle.ForeColor = Color.White;

           //grdResults.HeaderStyle.Font.Bold = true;

           //grdResults.HeaderStyle.Font.Name = "Tahoma";

           //grdResults.HeaderStyle.Font.Size = FontUnit.Medium;

           //Turn off AutoGenerate for the columns so that not all columns are returned

           //grdResults.AutoGenerateColumns = false;

           grdResults.AutoGenerateColumns = true;

           //DataGrid configured to display only the desired columns

           //Site Name

           HyperLinkColumn colSite = new HyperLinkColumn();

           //colSite.HeaderText = "Site Name";

           colSite.DataTextField = "Title";

           colSite.DataNavigateUrlField = "SiteName";

           grdResults.Columns.Add(colSite);

           //***Hit HighlightedProperties is not showing up!!! - Bug Fix

           //Highlight

           BoundColumn colHighLight = new BoundColumn();

           //colHighLight.HeaderText = "HitHighlightedProperties";

           colHighLight.DataField = "HitHighlightedProperties";

           grdResults.Columns.Add(colHighLight);

           //Path

           HyperLinkColumn colPath = new HyperLinkColumn();

           //colPath.HeaderText = "Path";

           colPath.DataTextField = "Path";

           colPath.DataNavigateUrlField = "Path";

           colPath.ItemStyle.ForeColor = Color.Green;

           grdResults.Columns.Add(colPath);

           //Author

           BoundColumn colAuthor = new BoundColumn();

           //colAuthor.HeaderText = "Author";

           colAuthor.DataField = "Author";

           colAuthor.ItemStyle.ForeColor = Color.Gray;

           grdResults.Columns.Add(colAuthor);

           //Write - Created Date

           BoundColumn colWrite = new BoundColumn();

           //colWrite.HeaderText = "Write";          

           colWrite.DataField = "Write";

           colWrite.ItemStyle.ForeColor = Color.Gray;

           colWrite.DataFormatString = "{0:d}";

           grdResults.Columns.Add(colWrite);

           //DocumentDate

           BoundColumn colDocDate = new BoundColumn();

           //colDocDate.HeaderText = "DocumentDate";

           colDocDate.DataField = "DocumentDate";

           colDocDate.ItemStyle.ForeColor = Color.Gray;

           colDocDate.DataFormatString = "{0:d}";

           grdResults.Columns.Add(colDocDate);

           //Bound the data to the DataGrid

           grdResults.DataBind();

           Controls.Add(grdResults);

       }

   }

}

@Tom Try using a FullTextSqlQuery instead.  It allows you to do comparisons, but it may take some trial and error on your part.

www.dotnetmafia.com/.../how-to-use-the-moss-enterprise-search-fulltextsqlquery-class.aspx

Is there a way to programmatically invoke your wild card search web part?  I was thinking of doing a wild card search - like "DocumentDate: *" and then filtering the results with a for loop before displaying in a Datagrid.

@Tom I don't think you could invoke it that way.  I think your best bet for the wildcard search is to use the FullTextSqlQuery and then you can bind it to your datagrid.

Hey Corey,

You were right, I used the FullTextSQL Query Class along with the DATEADD function and I got the results I wanted.  My updated code is below:

using System;

using System.Runtime.InteropServices;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Data;

using System.Drawing;

using System.Xml.Serialization;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

using Microsoft.Office.Server;

using Microsoft.Office.Server.Search.Query;

namespace CustomWebParts

{

   [Guid("01482ed3-b90c-428d-bacd-706267645f89")]

   public class CustomDateSearch : System.Web.UI.WebControls.WebParts.WebPart

   {

       Label lblDateSearch;

       Label lblQueryResults;

       DropDownList ddlYear;

       Button btnSearch;

       DateTime dtToday;

       DateTime dtPastYear;

       DateTime dtPast2Years;

       DateTime dtPast5Years;

       String strToday;

       String strPastYear;

       String strPast2Years;

       String strPast5Years;

       String strSelectedValue;

       String strSelectedItem;

       public CustomDateSearch()

       {

       }

       protected override void CreateChildControls()

       {

           base.CreateChildControls();

           //DateTime Variables

           dtToday = DateTime.Today;

           dtPastYear = dtToday.AddYears(-1);          

           dtPast2Years = dtToday.AddYears(-2);

           dtPast5Years = dtToday.AddYears(-5);

           //String Variables

           strToday = dtToday.ToString().Remove(9);

           strPastYear = dtPastYear.ToString().Remove(9);          

           strPast2Years = dtPast2Years.ToString().Remove(9);

           strPast5Years = dtPast5Years.ToString().Remove(9);

           //Label

           lblDateSearch = new Label();

           lblDateSearch.Text = "Year Range Search: ";

           this.Controls.Add(lblDateSearch);

           //Drop Down List

           ddlYear = new DropDownList();

           ddlYear.Items.Add(new ListItem("Select a Search Option", "0"));

           ddlYear.Items.Add(new ListItem("Past Year", strPastYear));

           ddlYear.Items.Add(new ListItem("Past 2 Years", strPast2Years));

           ddlYear.Items.Add(new ListItem("Past 5 Years", strPast5Years));            

           this.Controls.Add(ddlYear);

           //Search Button

           btnSearch = new Button();

           btnSearch.Text = "Search";

           btnSearch.Click += new EventHandler(cmdSearch_Click);

           this.Controls.Add(btnSearch);  

           //Test Label

           lblQueryResults = new Label();

           lblQueryResults.ForeColor = Color.Red;

           this.Controls.Add(lblQueryResults);

       }

       void cmdSearch_Click(object sender, EventArgs e)

       {

           strSelectedValue = ddlYear.SelectedValue.ToString();

           strSelectedItem = ddlYear.SelectedItem.ToString();

           if (strSelectedValue != "0")

           {

               lblQueryResults.Text = " ";

               fullTextSqlQueryExecute(strSelectedItem);

           }

           else

           {

               lblQueryResults.Text = "  *You Must Select a Search Option! " + strSelectedItem;

           }          

       }

       private void fullTextSqlQueryExecute(string strQueryText)

       {

           FullTextSqlQuery ftQuery= new FullTextSqlQuery(ServerContext.Current);

           if (strQueryText == "Past Year")

           {

               ftQuery.QueryText = "SELECT Title, Description, Write, HitHighlightedProperties, Author, Path, DocumentDate FROM SCOPE() WHERE DocumentDate >=DATEADD (Year, -1, GETGMTDATE())";

           }

           else if (strQueryText == "Past 2 Years")

           {

               ftQuery.QueryText = "SELECT Title, Description, Write, HitHighlightedProperties, Author, Path, DocumentDate FROM SCOPE() WHERE DocumentDate >=DATEADD (Year, -2, GETGMTDATE())";

           }

           else if (strQueryText == "Past 5 Years")

           {

               ftQuery.QueryText = "SELECT Title, Description, Write, HitHighlightedProperties, Author, Path, DocumentDate FROM SCOPE() WHERE DocumentDate >=DATEADD (Year, -5, GETGMTDATE())";

           }

           ftQuery.ResultTypes = ResultType.RelevantResults;

           ResultTableCollection oResultCollection = ftQuery.Execute();

           if ((int)ResultType.RelevantResults != 0)

           {

               lblQueryResults.Text = " Results Returned: ";

           }

           else

           {

               lblQueryResults.Text = " No Results Returned ";

           }

           // Execute the query and load the results into a datatable //

           ResultTable oResult = oResultCollection[ResultType.RelevantResults];

           DataTable dtResults = new DataTable();

           dtResults.TableName = "Result";

           dtResults.Load(oResult, LoadOption.OverwriteChanges);

           DataSet ds = new DataSet("All_Results");

           ds.Tables.Add(dtResults);

           DataGrid datagrid = new DataGrid();

           datagrid.DataSource = ds.Tables[0];

           //General Datgrid Formatting

           datagrid.GridLines = GridLines.None;

           datagrid.CellPadding = 4;

           datagrid.Width = Unit.Percentage(100);

           datagrid.ItemStyle.ForeColor = Color.Black;

           datagrid.ItemStyle.Font.Size = FontUnit.Smaller;

           datagrid.ItemStyle.Font.Name = "Tahoma";

           datagrid.AutoGenerateColumns = false;

           //Document Name Link

           HyperLinkColumn colDocNameLink = new HyperLinkColumn();

           //colSite.HeaderText = "Site Name";

           colDocNameLink.DataTextField = "Title";

           colDocNameLink.DataNavigateUrlField = "Path";

           datagrid.Columns.Add(colDocNameLink);

           //Document Name

           BoundColumn colDocName = new BoundColumn();

           //colSite.HeaderText = "Title";

           colDocName.DataField = "Title";

           datagrid.Columns.Add(colDocName);

           //Highlight (No Data Shows!)

           BoundColumn colHighLight = new BoundColumn();

           //colHighLight.HeaderText = "HitHighlightedProperties";

           colHighLight.DataField = "HitHighlightedProperties";

           datagrid.Columns.Add(colHighLight);

           //Path

           HyperLinkColumn colPath = new HyperLinkColumn();

           //colPath.HeaderText = "Path";

           colPath.DataTextField = "Path";

           colPath.ItemStyle.ForeColor = Color.Green;

           colPath.DataNavigateUrlField = "Path";

           datagrid.Columns.Add(colPath);

           //Author

           BoundColumn colAuthor = new BoundColumn();

           //colAuthor.HeaderText = "Author";

           colAuthor.DataField = "Author";

           colAuthor.ItemStyle.ForeColor = Color.Gray;

           datagrid.Columns.Add(colAuthor);

           //Document Date

           BoundColumn colDocDate = new BoundColumn();

           //colAuthor.HeaderText = "Author";

           colDocDate.DataField = "DocumentDate";

           colDocDate.DataFormatString = "{0:d}";

           colDocDate.ItemStyle.ForeColor = Color.Gray;

           datagrid.Columns.Add(colDocDate);

           //Bind and Add

           datagrid.DataBind();

           Controls.Add(datagrid);

       }      

   }

}

Thanks Corey!

Tom

@Tom Excellent!  Glad I could help.

Hey Corey,

Do you know how to add the document Icon to the search results\data grid?  

Tom

@Tom I'm not exactly sure how the CRWP gets it, but it uses a field called imageurl.  It's not listed as a managed proeprty but it may be reserved/hidden.  Otherwise try PictureThumbnailURL and see if that gets you anywhere.

Hey Corey,

Using SearchCoder I can tell that PictureThumbnailURL just returns a null value.  This seems to be one of those many unsolved problems with MOSS customization as evidenced by the many unanswered Blogs out there, like these two below:

www.eggheadcafe.com/.../custom-search-document-ic.aspx

www.itweb.co.za/.../index.php

This blog seems to provide an answer, but I'm not sure how to incorporate the answer provided into the datagrid I'm using, here's the blog:

social.msdn.microsoft.com/.../a16429ce-345b-444f-a386-92718675536f

Anyway, I'm still stumped on this one, any code examples would be greatly appreciated.

Tom