Where Clause in the DS

[sitefinitysteve]

How do I configure the OpenAccess DS Control Where clause to not filter  if the RadCombo is Empty?

<telerik:OpenAccessDataSource ID="OpenAccessDataSourceDetail" runat="server" ObjectContextProvider="RTODataLayer.RTOObjectScopeProvider"
        OrderBy="FromDate" TypeName="RTODataLayer.RequestedDate" Where="Request.ProgramID == @Request.ProgramID OR Request.ResidentID == @Request.ResidentID">
        <WhereParameters>
            <asp:QueryStringParameter Name="Request.ProgramID" QueryStringField="programid" DbType="Guid" />
            <asp:ControlParameter ControlID="residentCombo" Name="Request.ResidentID" PropertyName="SelectedValue"  DbType="Guid" ConvertEmptyStringToNull="true" />
        </WhereParameters>
    </telerik:OpenAccessDataSource>

(btw, thanks for fixing the control in SP2 :)

[Serge]

Hi Steve,

 You should modify your where clause to handle this scenario. If your default value is the string "Empty" then you should modify the clause to be something like this : 

Request.ProgramID == @Request.ProgramID OR (@Request.ResidentID != 'Empty' AND Request.ResidentID == @Request.ResidentID)

This should do the trick. Of course this means that you will have to know the default value of the combo box at all times. This however is the case most of the time.

I do hope this helps.

Regards,
Serge
the Telerik team

[sitefinitysteve]

I've had to do this a few times

Is there any way you guys would consider wrapping this into a property by any chance?

[sitefinitysteve]

Also if you don't mind helping...

This is the DS definition

<telerik:OpenAccessDataSource ID="scheduleDS" runat="server" EnableUpdate="False"
                              ObjectContextProvider="Endurex.Model.Context, Endurex.Model"
                              TypeName="Endurex.Model.EndurexSchedule"
                              Where="UserID == @UserID AND @UserID != 'Empty'"  >
    <WhereParameters>
        <asp:ControlParameter ControlID="userCombo" Name="UserID"  PropertyName="SelectedValue" DbType="Guid" ConvertEmptyStringToNull="true" />
    </WhereParameters>
</telerik:OpenAccessDataSource>

line 1:101: unexpected char: '@'
Original Query: DEFINE EXTENT xt FOR Endurex.Model.EndurexSchedule; SELECT * FROM xt AS this WHERE UserID == $1 AND @UserID != 'Empty'

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Telerik.OpenAccess.Exceptions.QueryException: line 1:101: unexpected char: '@'
Original Query: DEFINE EXTENT xt FOR Endurex.Model.EndurexSchedule; SELECT * FROM xt AS this WHERE UserID == $1 AND @UserID != 'Empty'

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[QueryException: line 1:101: unexpected char: '@' Original Query: DEFINE EXTENT xt FOR Endurex.Model.EndurexSchedule; SELECT * FROM xt AS this WHERE UserID == $1 AND @UserID != 'Empty'] Telerik.OpenAccess.RT.ExceptionWrapper.Throw() +13 OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.compileQuery(ImmutableQueryDetails query) +344 OpenAccessRuntime.DataObjects.OpenAccessQueryImp.compile() +110 Telerik.OpenAccess.RT.QueryImpl.Execute(Object[] args) +79 Telerik.OpenAccess.RT.DataSource.OpenAccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1183 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +28 Telerik.Web.UI.Scheduling.DataSourceViewSchedulerProvider.GetAppointments(RadScheduler owner) +171 Telerik.Web.UI.RadScheduler.BindAppointments() +200 Telerik.Web.UI.RadScheduler.PerformSelect() +125 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +105 Telerik.Web.UI.RadScheduler.EnsureDataBound() +16 Telerik.Web.UI.RadScheduler.CreateChildControls(Boolean bindFromDataSource) +81 System.Web.UI.Control.EnsureChildControls() +182 System.Web.UI.Control.PreRenderRecursiveInternal() +60 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222 System.Web.UI.Control.PreRenderRecursiveInternal() +222

[sitefinitysteve]

Ok, so nevermind, I see you beat me to it :)

<telerik:OpenAccessDataSource ID="scheduleDS" runat="server" EnableUpdate="False"
                              ObjectContextProvider="Endurex.Model.Context, Endurex.Model"
                              TypeName="Endurex.Model.EndurexSchedule"
                              AutoGenerateWhereClause="true"  >
    <WhereParameters>
        <asp:ControlParameter ControlID="userCombo" Name="UserID"  PropertyName="SelectedValue" DbType="Guid" ConvertEmptyStringToNull="true" />
    </WhereParameters>
</telerik:OpenAccessDataSource>

[Serge]

Hi Steve,

 Would you mind elaborating a bit. Does the automatic where clause generation solve your problem or does it still persist.

I am looking forward to resolving this issue. 

All the best,
Serge
the Telerik team

[sitefinitysteve]

Automatic where did the trick, thanks ;)