This is a migrated thread and some comments may be shown as answers.

SqlDataSource Clarification

14 Answers 263 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Chandran Chandran asked on 02 Sep 2010, 11:37 AM

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

14 Answers, 1 is accepted

answered on 03 Sep 2010, 12:13 PM

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

answered on 07 Sep 2010, 02:34 PM

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

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

answered on 07 Sep 2010, 03:06 PM

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

answered on 10 Sep 2010, 03:55 PM

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

answered on 15 Sep 2010, 11:01 AM

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