
Chandran Chandran
Top achievements
Rank 1
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
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
I also tried by replacing the sql command by adding procedure for the same query as given below
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
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
0
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
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
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
0

Chandran Chandran
Top achievements
Rank 1
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
Hope the above mentioned information would help you.
-Thanks
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
0

Chandran Chandran
Top achievements
Rank 1
answered on 03 Sep 2010, 03:49 PM
Hello,
Any suggetions?
-Thanks
Any suggetions?
-Thanks
0

Chandran Chandran
Top achievements
Rank 1
answered on 07 Sep 2010, 01:15 PM
Hello,
0

Chandran Chandran
Top achievements
Rank 1
answered on 07 Sep 2010, 01:15 PM
Hello,
Any opinions would be appreciated
-Thanks
0
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
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
0

Chandran Chandran
Top achievements
Rank 1
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.
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:
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
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
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
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
0

Chandran Chandran
Top achievements
Rank 1
answered on 08 Sep 2010, 11:39 AM
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
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
0

Chandran Chandran
Top achievements
Rank 1
answered on 09 Sep 2010, 05:42 PM
Hi,
Any opinions would be appreciable?
-Thanks
Any opinions would be appreciable?
-Thanks
0
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
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
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
0

Chandran Chandran
Top achievements
Rank 1
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:
CS Code:
Please try out the above mentioned code after adding EID column to Customers table.
Looking for your response
-Thanks
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
>
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
0

Chandran Chandran
Top achievements
Rank 1
answered on 14 Sep 2010, 12:39 PM
Hi Radoslav,
Whether my above mentioned coding snippets would be enough to findout the solution?
Looking forward for your response
-Thanks
Whether my above mentioned coding snippets would be enough to findout the solution?
Looking forward for your response
-Thanks
0
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:
Please give it try and let me know if the issue still persists.
Sincerely yours,
Radoslav
the Telerik team
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
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
0

Chandran Chandran
Top achievements
Rank 1
answered on 15 Sep 2010, 03:02 PM
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
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