Radgrid and Entity Framework - AutoGenerateWhereClause cannot be true if Where is defined

[Richard]

Hello all.

Brand new to Telerik and developing in general so be gentle!

I'm using EF to display product information within a gridview. I have managed to hook up the entity data source and radgrid so that when a user inserts a product ID, the associated information is displayed upon post back.

Everything works fine i.e. sorting, paging, editing, updating.

The only problem I have is with filtering where upon I attempt to filter and receive the subject title...nasty.

If I change AutoGenerateWhere clause to false, I then can't even get the radgrid to bind.

Any chance someone could point me in the right direction. My code is as follows:

 

 

 

<telerik:RadGrid ID="RadGrid1" runat="server" AllowFilteringByColumn="True"

AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"

DataSourceID="edsTelerikWeights" GridLines="None"

AutoGenerateEditColumn="True" AllowAutomaticInserts="True"

AllowAutomaticUpdates="True" ondatabound="RadGrid1_DataBound">

<MasterTableView DataKeyNames="PriKey" DataSourceID="edsTelerikWeights">

<RowIndicatorColumn>

<HeaderStyle Width="20px"></HeaderStyle>

</RowIndicatorColumn>

<ExpandCollapseColumn>

<HeaderStyle Width="20px"></HeaderStyle>

</ExpandCollapseColumn>

<Columns>

<telerik:GridBoundColumn DataField="Productpriority" DataType="System.Int32"

HeaderText="Productpriority" SortExpression="Productpriority"

UniqueName="Productpriority" ReadOnly="true">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="MemberId" HeaderText="MemberId"

SortExpression="MemberId" UniqueName="MemberId" ReadOnly="true">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="MemberName" HeaderText="MemberName"

SortExpression="MemberName" UniqueName="MemberName" ReadOnly="true">

</telerik:GridBoundColumn>

<telerik:GridCheckBoxColumn DataField="Retail" DataType="System.Boolean"

HeaderText="Retail" SortExpression="Retail" UniqueName="Retail" ReadOnly="true">

</telerik:GridCheckBoxColumn>



<asp:EntityDataSource ID="edsTelerikWeights" runat="server"

ConnectionString="name=PDC_VDSOREntities"

DefaultContainerName="PDC_VDSOREntities" EnableFlattening="False" EntitySetName="tblWeights"

AutoGenerateWhereClause="True">

<WhereParameters>

<asp:SessionParameter Name="MemberId"

SessionField="VDS_MemberID" />

<asp:SessionParameter Name="LocationId"

SessionField="VDS_LocationID" />

<asp:ControlParameter ControlID="tbxAutoCompleteMulti" DefaultValue="Null"

Name="UnitId" PropertyName="Text" />

</WhereParameters>

</asp:EntityDataSource>

Any help gratefully received.

 

 

 

 

[Tsvetoslav]

Hello Richard,

When Filtering the RadGrid control bound to a EntityDataSource, behind the scene RadGrid sets the WhereClause of the EntityDataSource control to the corresponding filter expression. And since you have defined AutoGenrateWhereClause=true this will lead to the conflict described by the thrown exception. This behavior can be corrected by attaching an event handler to the EntityDataSource' Selecting event and modifying the where clause by adding the additional conditions to it. In this way, you will not need the where parameters. I am attaching a small sample demonstrating this approach.

Hope it helps.

Best wishes,
Tsvetoslav
the Telerik team

[Richard]

Thanks Tsvetoslav  - I shall have play with that now.

Sorry to be a pain - how would you go about manipulating the code behind so that you can incorporate multiple parameters. Apologies for my ignorance.

Is it a case of inserting an additional e.DataSource.Where += for each of the parameters?

I have attempted to insert two parameters but fialing miserably with an error occurring on the

e.DataSource.Where +=

 

" AND ";

 

 line.

Thanks for your help and advice, it is appreciated.

[Tsvetoslav]

Hi Richard,

Yes, that's the way to go about it - using the AND operator. Could you give more details on the error you are getting and possibly some code snippets?

Regards,
Tsvetoslav
the Telerik team

[Richard]

Hello Tsvetoslav

Apologies for the delay, I've been away.

Ok, I have the following error on the OnPreRender line
The query syntax is not valid. Near keyword 'AND', line 6, column 2.

With my code as follows:

public partial class WebForm1 : System.Web.UI.Page
    {
        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            Session["VDS_MemberID"] = "FM00003";
            Session["VDS_LocationID"] = "1";


            edsWeights.Selecting += new EventHandler<EntityDataSourceSelectingEventArgs>(edsWeights_Selecting);
        }



        protected void Page_Load(object sender, EventArgs e)
        {
            RadGrid1.ItemCommand += new Telerik.Web.UI.GridCommandEventHandler(RadGrid1_ItemCommand);
        }

        protected void edsWeights_Selecting(object sender, EntityDataSourceSelectingEventArgs e)
        {
            if (e.DataSource.Where != string.Empty)
            {
                e.DataSource.Where += " AND ";
            }

            e.DataSource.Where += "it.tblWeights.MemberID == " + Session["VDS_MemberID"].ToString();
            e.DataSource.Where += "it.tblWeights.LocationID == " + Session["VDS_MemberID"].ToString();
            e.DataSource.Where += "it.tblWeights.BaseMaterialName == " + ddlMaterial.SelectedValue.ToString();
        }
        protected void RadGrid1_ItemCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)
        {

        }
        protected override void OnPreRender(EventArgs e)
        {

            base.OnPreRender(e);
          }
    }
}

I thought it could be that old chesnut, case sensitivity, but I've triple checked that.

I'm pretty new to all of this programming malarky so I've probably done something really stupid.

Thank you for your help.

[Tsvetoslav]

Hi Richard,

You need an AND after the addition of each one of the conditions:

e.DataSource.Where += "it.tblWeights.MemberID == " + Session["VDS_MemberID"].ToString() + " AND ";
e.DataSource.Where += "it.tblWeights.LocationID == " + Session["VDS_MemberID"].ToString() + " AND ";
e.DataSource.Where += "it.tblWeights.BaseMaterialName == " + ddlMaterial.SelectedValue.ToString();

with the exception of the last one.

Regards,
Tsvetoslav
the Telerik team

[Scott]

I've been beating my head on this Where and AutoGenerateWhereClause issue for days. Thank you so much. It makes perfect sense now. If I could +1 this I would...

[Bary]

Thanks for this post, it helped me a lot.

[Dhamodharan]

Hi Scott,

Can we insert data into 2 tables?. i am using EntityDataSource with "Inclue" option. so i can display 2 tables data. but how we can insert and update 2 tables?

Thanks in Advance,
Dhamu