Search data between startDate and endDate in SilverLight Application.

[AnhTruong]

I am looking for sample code to search the query task data between dates. Any helps is appreciated.

[JenniferNery]

This seem related: http://forums.arcgis.com/threads/26836-QueryTask-for-ORACLE-Date-Field and this might also help: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s500000033000000.htm

[AnhTruong]

Hello,

I looked your links but I could not figure out where the resources I can apply to my issue. My application displays the data from URL ArcGIS service. I would like to filter records based on FINISH_TIME column? Here are my sample codes as following:

MainPage.xaml


<StackPanel Orientation="Horizontal" Canvas.Top="30" Height="Auto" Canvas.Left="644" Width="467" Name="StkPnlSearch" HorizontalAlignment="Center" Margin="0,2,0,0">
<TextBlock Canvas.Top="36" Text="Start Date:" Name="StartDate" Height="18" Width="63" VerticalAlignment="Center" HorizontalAlignment="Left" />
<sdk:DatePicker Canvas.Left="678" Canvas.Top="31" Height="23" Name="startDate" Width="101" HorizontalAlignment="Right" IsDropDownOpen="False" />
<TextBlock Canvas.Left="1795" Canvas.Top="36" Text="End Date:" Name="EndDate" Height="17" Width="56" VerticalAlignment="Center" />
<sdk:DatePicker Canvas.Top="32" Height="22" Name="endDate" Width="100" IsDropDownOpen="False" />
<Button Content="Search" Cursor="Hand" Width="75" Name="btnSearch" HorizontalAlignment="Right" VerticalAlignment="Center" Click="btnSearch_Click" />
</StackPanel>

<slData:DataGrid x:Name="dgRecords" AutoGenerateColumns="False" HeadersVisibility="Column" Background ="White">

<slData:DataGrid.Columns>

<slData:DataGridTextColumn Width="160" Binding="{Binding Attributes[NAME]}" Header="Name"CanUserSort ="True" />

<slData:DataGridTextColumn Width="180" Binding="{Binding Attributes[CLASS]}" Header="Class"CanUserSort ="True" />

<slData:DataGridTextColumn Width="150" Binding="{Binding Attributes[FINISH_TIME]}" Header="Finish Time"CanUserSort ="True" />

</slData:DataGrid.Columns>

</slData:DataGrid>

=====================================================================================

MainPage.xaml.cs
...
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
QueryTask queryTask = new QueryTask(URLService);
queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
queryTask.Failed += QueryTask_Failed;

ESRI.ArcGIS.Client.Tasks.Query query = new ESRI.ArcGIS.Client.Tasks.Query();
query.OutFields.Add("*");

DateTime @StartDate, @EndDate;
@StartDate = startDate.DisplayDate;
@EndDate = endDate.DisplayDate;
query.Where = "FINISH_TIME BETWEEN @StartDate AND @EndDate";
queryTask.ExecuteAsync(query);

}

=====================================================================================


My Silverlight design page has two DateTimePickers for startDate, endDate and one Search button. I run the query but no records return. Anythings wrong when passing parameter to Oracle database? I really appreciate your time to help me out this issue.

[AnhTruong]

Hi,

I am still searching for ArcGIS query expertise, but I can narrow down what I need to help for my search engine as following:

string paramStartDate;
paramStartDate = txtStartName.Text;
           
//string querySearch = "TIME_OPEN >= TO_DATE('7/30/2011 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM')";
           
            DateTime dtStartDate = Convert.ToDateTime(paramStartDate);
            DateTime dtEndDate = Convert.ToDateTime(paramEndDate);

            QueryTask queryTask = new QueryTask(URLService);
            queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
            queryTask.Failed += QueryTask_Failed;

            ESRI.ArcGIS.Client.Tasks.Query query = new ESRI.ArcGIS.Client.Tasks.Query();
            query.OutFields.Add("*");
            //This query cannot return the records even though they existed in database
            string querySearch = "TIME_FINISH >= TO_DATE(dtStartDate, 'MM/DD/YYYY HH:MI:SS AM')";

            //string querySearch = "TIME_FINISH >= TO_DATE('7/30/2011 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM')";
            query.Where = querySearch;
             
            queryTask.ExecuteAsync(query);

[AnhTruong]

Hi,

I am still searching for ArcGIS query expertise, but I can narrow down what I need to help for my search engine as following:

string paramStartDate;
paramStartDate = txtStartName.Text;
           
DateTime dtStartDate = Convert.ToDateTime(paramStartDate);

// if I selected the date from DatePicker is 7/30/2010, I can trace the selected value using VS 2010 //debugging with System.DateTime datatype {7/30/2011 12:00:00 AM}

QueryTask queryTask = new QueryTask(URLService);
queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
queryTask.Failed += QueryTask_Failed;

ESRI.ArcGIS.Client.Tasks.Query query = new ESRI.ArcGIS.Client.Tasks.Query();
query.OutFields.Add("*");
//This query cannot return the records even though they existed in database
   string querySearch = "TIME_FINISH >= TO_DATE(dtStartDate, 'MM/DD/YYYY HH:MI:SS AM')";
// but this query is working if I do hard code
//string querySearch = "TIME_FINISH >= TO_DATE('7/30/2011 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM')";

query.Where = querySearch;             
queryTask.ExecuteAsync(query);

How do I pass actual value to dtStartDate when doing query???

[DominiqueBroux]

//This query cannot return the records even though they existed in database
string querySearch = "TIME_FINISH >= TO_DATE(dtStartDate, 'MM/DD/YYYY HH:MI:SS AM')";

The server doesn't know your 'dtStartDate' variable so you have to replace it by its value:

string querySearch = "TIME_FINISH >= TO_DATE('" +
dtStartDate.ToString("MM/dd/yyyy hh:mm:ss tt") +
"', 'MM/DD/YYYY HH:MI:SS AM')";