SqlDataSource Clarification

[Chandran Chandran]

Hello Telerik,

I am using RadGrid and filtering templates functionality to filter the grid. I Used SqlDataSource to bind the respetive filtering combo boxes.
I used the below kind of query to bind the SqlDataSource1

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EmpConnectionString %>"

                SelectCommand="SELECT DISTINCT Position FROM Employee WHERE (EID IS NULL OR EID = @EID) AND (Deleted = 0) AND (Status = 0)">
                 <SelectParameters>
                     <asp:Parameter Direction="Input" Name="EID" />
                 </SelectParameters>
            </asp:SqlDataSource>

When i run the project and select the item from combo to filter the combo, i am getting the following error "Incorrect syntax near the keyword 'WHERE'." from RefreshCombos function

protected void RefreshCombos()
        {
            SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand + " WHERE " + RadEmpList.MasterTableView.FilterExpression.ToString();
 SqlDataSource2.SelectCommand = SqlDataSource2.SelectCommand + " WHERE " + RadEmpList.MasterTableView.FilterExpression.ToString();
            RadEmpList.MasterTableView.Rebind();
        }

I also tried by replacing the sql command by adding procedure for the same query as given below

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EmpConnectionString %>"
                SelectCommand="proc_GetPositions" SelectCommandType="StoredProcedure">
                 <SelectParameters>
                     <asp:Parameter Name="EID" Type="Int32" />
                 </SelectParameters>
            </asp:SqlDataSource>

But no use, i am getting the error from the same function.
I hope you could understand my doubt, so please helpe me to know how to do filtering functionality with the above related query. It would be more helpful if i could know the result in both ordinary query and Procedure of the above query of SqlDataSource

-Thanks

[Radoslav]

Hello Chandran,

Could you please verify that into the filter's combobox items do not exist any special characters. Also could you please post your RadGrid declaration with the related code behind methods.

Looking forward for your reply.

All the best,
Radoslav
the Telerik team

[Chandran Chandran]

Hi,
I believe there is no special characters are exist in the Sql. Herewith i am posting aspx & c# relate to filtering

<FilterTemplate>
                               <telerik:RadComboBox Font-Names="Verdana" Font-Size="8pt" ID="PositionComboBox" DataSourceID="SqlDataSource1"
                                   DataTextField="Position" DataValueField="Position" AppendDataBoundItems="true" Width="100%"
                                   Height="100%" OnClientSelectedIndexChanged="PositionIndexChanged" SelectedValue='<%#((GridItem)Container).OwnerTableView.GetColumn("Position").CurrentFilterValue %>'
                                   runat="server">
                                   <Items>
                                       <telerik:RadComboBoxItem Text="All" />
                                   </Items>
                               </telerik:RadComboBox>
                               <telerik:RadScriptBlock ID="RadScriptBlock1" runat="server">
                                   <script type="text/javascript">
                                       function PositionIndexChanged(sender, args) {
                                           var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                                           tableView.filter("Position", args.get_item().get_value(), "EqualTo");
                                       }
                                   </script>
                               </telerik:RadScriptBlock>
                           </FilterTemplate>

protected void RadEmpList_PreRender(object sender, EventArgs e)
       {

           if (RadEmpList.MasterTableView.FilterExpression != string.Empty)
               RefreshCombos();
       }
       protected void RefreshCombos()
       {
           SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand + " WHERE " + RadEmpList.MasterTableView.FilterExpression.ToString();
           RadEmpList.MasterTableView.Rebind();
       }

Hope the above mentioned information would help you.

-Thanks

[Chandran Chandran]

Hello,

Any suggetions?

-Thanks

[Chandran Chandran]

Hello,

       

[Chandran Chandran]

Hello,

Any opinions would be appreciated 

-Thanks

[Radoslav]

Hi Chandran,

I examined the code you post and it is looks correct, however it is not runnable and I could not reproduce the described issue. Could you please check out this online example and let me know what differs in your case. Additionally it will be helpful if you send us a small runnable project which demonstrates the issue. You could open a formal support ticket from your Telerik account and attach a ZIP file there. Thus we will be able to we debug the project and provide you with more to-the-point answer.

Regards,
Radoslav
the Telerik team

[Chandran Chandran]

Hi Radoslav,
Thanks for your response, I used the same kind of coding scenario in my project as you mentioned in your link.I used only the simple code to bind the combo boxes or to reach my scenario. I am simply demonstrating this as below.If it overcomes then the rest of the thinks will be easy.
The below code is used to bind the combo box in the demo grid. 
 

<asp:SqlDataSource ID="SqlDataSource3" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT City FROM Customers"
            runat="server"></asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource4" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT Country FROM Customers"
            runat="server"></asp:SqlDataSource>

But my project used with one difference I used the condition query or where clause by altering the SqlDataSource to bind the respective combo boxes.
Eg:

SELECT DISTINCT Country FROM Customers WHERE EID=@EID
SELECT DISTINCT City FROM Customers WHERE EID=@EID

So in my above code i manage to get the country and city details based on the EID, which would be supplied from the session during page_Load of the form

SqlDataSource1.SelectParameters["EID"].DefaultValue = Session["EID"].ToString();

So i am not getting error in bind the combos and running the project, but when i chooing the any of item from the combo to filter the grid, i am getting the error in the RefreshCombos() method section.The error is "Incorrect syntax near the keyword 'WHERE'

So i hope the above mentioned code would be enough to catch the issue.

-Thanks

[Chandran Chandran]

Dear Telerik,

I hope the above mentioned information would be enough to reach out the solution. If not so please let me know for further assisstance.

Regards,
Chandran

[Chandran Chandran]

Hi,
Any opinions would be appreciable?

-Thanks

[Radoslav]

Hello Chandran,

Could you please try using the SQL Server Profiler tool and inspect and post, into the forum, the query which is executed on the SQL server when you choose some of the combobox items.  
Looking forward for your reply.

All the best,
Radoslav
the Telerik team

[Chandran Chandran]

Dear Radoslav,
As my support period has been expired, i could not open a support ticket at any cause. So herewith i am giving the runtime code for your reference. I made one difference in my "Customers" table of "Northwind". I added one column "EID" into the Customer table and filled with "EID" column with the value of "1000" for atleaset 15 rows.

This may help you to find out the solution from my below mentioned code example.

ASPX:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="exFilter.aspx.cs" Inherits="exFilter" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
     <telerik:RadScriptManager ID="RadScriptManager1" runat="server" />
        <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
            <AjaxSettings>
                <telerik:AjaxSetting AjaxControlID="RadGrid1">
                    <UpdatedControls>
                        <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
                    </UpdatedControls>
                </telerik:AjaxSetting>
                <telerik:AjaxSetting AjaxControlID="RadGrid2">
                    <UpdatedControls>
                        <telerik:AjaxUpdatedControl ControlID="RadGrid2" LoadingPanelID="RadAjaxLoadingPanel1" />
                    </UpdatedControls>
                </telerik:AjaxSetting>
            </AjaxSettings>
        </telerik:RadAjaxManager>
        <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server" />
        <telerik:RadGrid ID="RadGrid1" Width="100%" DataSourceID="SqlDataSource1" AllowFilteringByColumn="True"
            AllowSorting="True" AllowPaging="True" PageSize="7" runat="server" AutoGenerateColumns="False"
            OnPreRender="RadGrid1_PreRender" ShowStatusBar="true" EnableLinqExpressions="false">
            <MasterTableView DataKeyNames="CustomerID">
                <Columns>
                    <telerik:GridBoundColumn UniqueName="ContactName" DataField="ContactName" HeaderText="Contact name"
                        AllowFiltering="false" HeaderStyle-Width="200px" />
                    <telerik:GridBoundColumn UniqueName="ContactTitle" DataField="ContactTitle" HeaderText="Contact title"
                        HeaderStyle-Width="200px">
                        <FilterTemplate>
                            <telerik:RadComboBox ID="RadComboBoxTitle" DataSourceID="SqlDataSource2" DataTextField="ContactTitle"
                                DataValueField="ContactTitle" Height="200px" AppendDataBoundItems="true" SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("ContactTitle").CurrentFilterValue %>'
                                runat="server" OnClientSelectedIndexChanged="TitleIndexChanged">
                                <Items>
                                    <telerik:RadComboBoxItem Text="All" />
                                </Items>
                            </telerik:RadComboBox>
                            <telerik:RadScriptBlock ID="RadScriptBlock1" runat="server">

                                <script type="text/javascript">
                                    function TitleIndexChanged(sender,args) {
                                        var tableView=$find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                                        tableView.filter("ContactTitle",args.get_item().get_value(),"EqualTo");

                                    }
                                </script>

                            </telerik:RadScriptBlock>
                        </FilterTemplate>
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn UniqueName="City" DataField="City" HeaderText="City" HeaderStyle-Width="200px">
                        <FilterTemplate>
                            <telerik:RadComboBox ID="RadComboBoxCity" DataSourceID="SqlDataSource3" DataTextField="City"
                                DataValueField="City" Height="100px" AppendDataBoundItems="true" SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("City").CurrentFilterValue %>'
                                runat="server" OnClientSelectedIndexChanged="CityIndexChanged">
                                <Items>
                                    <telerik:RadComboBoxItem Text="All" />
                                </Items>
                            </telerik:RadComboBox>
                            <telerik:RadScriptBlock ID="RadScriptBlock2" runat="server">

                                <script type="text/javascript">
                                    function CityIndexChanged(sender,args) {
                                        var tableView=$find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                                        tableView.filter("City",args.get_item().get_value(),"EqualTo");
                                    }
                                </script>

                            </telerik:RadScriptBlock>
                        </FilterTemplate>
                    </telerik:GridBoundColumn>
                    <telerik:GridTemplateColumn DataField="Country" HeaderText="Country" UniqueName="Country"
                        HeaderStyle-Width="200px" SortExpression="Country">
                        <FilterTemplate>
                            <telerik:RadComboBox ID="RadComboBoxCountry" DataSourceID="SqlDataSource4" DataTextField="Country"
                                DataValueField="Country" Height="100px" AppendDataBoundItems="true" SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("Country").CurrentFilterValue %>'
                                runat="server" OnClientSelectedIndexChanged="CountryIndexChanged">
                                <Items>
                                    <telerik:RadComboBoxItem Text="All" />
                                </Items>
                            </telerik:RadComboBox>
                            <telerik:RadScriptBlock ID="RadScriptBlock3" runat="server">

                                <script type="text/javascript">
                                    function CountryIndexChanged(sender,args) {
                                        var tableView=$find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                                        tableView.filter("Country",args.get_item().get_value(),"EqualTo");
                                    }
                                </script>

                            </telerik:RadScriptBlock>
                        </FilterTemplate>
                        <ItemTemplate>
                            <img src='Img/<%# Eval("Country") %>.gif' alt="" style="vertical-align: middle; margin-right: 7px;" /><%# Eval("Country") %>
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                </Columns>
            </MasterTableView>
        </telerik:RadGrid>
        <asp:SqlDataSource ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            ProviderName="System.Data.SqlClient" SelectCommand="SELECT CustomerID, ContactName, ContactTitle, City, Country FROM Customers Where EID=1000"
            runat="server">
            <SelectParameters>
                <asp:Parameter Direction="Input" Name="EID" DefaultValue="1000" />
            </SelectParameters>
            </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT ContactTitle FROM Customers Where EID=@EID"
            runat="server">
            <SelectParameters>
                <asp:Parameter Direction="Input" Name="EID" DefaultValue="1000" />
            </SelectParameters>
            </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource3" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT City FROM Customers Where EID=@EID"
            runat="server">
            <SelectParameters>
                <asp:Parameter Direction="Input" Name="EID" DefaultValue="1000" />
            </SelectParameters>
            </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource4" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT Country FROM Customers Where EID=@EID"
            runat="server">
            <SelectParameters>
                <asp:Parameter Direction="Input" Name="EID" DefaultValue="1000" />
            </SelectParameters>
            </asp:SqlDataSource>
    </form>
</body>
</html>

CS Code:

protected void RadGrid1_PreRender(object sender, System.EventArgs e)
   {
       if (RadGrid1.MasterTableView.FilterExpression != string.Empty)
       {
           RefreshCombos();
       }
   }
   protected void RefreshCombos()
   {
       SqlDataSource2.SelectCommand = SqlDataSource2.SelectCommand + " WHERE " + RadGrid1.MasterTableView.FilterExpression.ToString();
       SqlDataSource3.SelectCommand = SqlDataSource3.SelectCommand + " WHERE " + RadGrid1.MasterTableView.FilterExpression.ToString();
       SqlDataSource4.SelectCommand = SqlDataSource4.SelectCommand + " WHERE " + RadGrid1.MasterTableView.FilterExpression.ToString();
       RadGrid1.MasterTableView.Rebind();
   }

Please try out the above mentioned code after adding EID column to Customers table.
Looking for your response

-Thanks

[Chandran Chandran]

Hi Radoslav,

Whether my above mentioned coding snippets would be enough to findout the solution?
Looking forward for your response

-Thanks

[Radoslav]

Hello Chandran,

When you concatenate the SqlDataSources' SelectCommand into the RadGrid.PreRender the result query contains 2 WHERE clauses. To achieve the desired functionality you need to replace the second where clause with "AND" operator:

protected void RefreshCombos()
{
   SqlDataSource2.SelectCommand = SqlDataSource2.SelectCommand + " AND " + RadGrid1.MasterTableView.FilterExpression.ToString();
   SqlDataSource3.SelectCommand = SqlDataSource3.SelectCommand + " AND " + RadGrid1.MasterTableView.FilterExpression.ToString();
   SqlDataSource4.SelectCommand = SqlDataSource4.SelectCommand + " AND " + RadGrid1.MasterTableView.FilterExpression.ToString();

    RadGrid1.MasterTableView.Rebind();
}

Please give it try and let me know if the issue still persists.

Sincerely yours,
Radoslav
the Telerik team

[Chandran Chandran]

Hi Radoslav,
I implemeneted already the same logic your are explained below by replacing 'WHERE'.  But i felt an doubt that whether that was the right way to achieve the scenario. I thought there mught be another way to solve the issue. So that i was waiting till the days of your response.

If this is the right way i will follow the same code.
Suppose is there any other way without modifying the code,please let me know

-Thanks