Rad Grid Rebind issue with multiple control parameters

[Alexander]

I have a rad grid that is selecting fields from 2 different tables. If i have one control parameter in the SQL data source, the grid rebinds fine and shows the data. When i add an OR clause to my SQL statement in the data source configurator for another control parameter. The grid no longer rebinds. But if i test the query from the SQL data source "Test Query", the statement works fine and pulls the data it is supposed to. Any reason as to why the grid would have an issue with the SQL statement having multiple control parameters? I can post code if you need me to.

[Angel Petrov]

Hi Alexander,

We have not been contacted so far with such an issue. I tried reproducing the problem in a sample project but to no avail. Could you please review the test website and tell us what differs in your case? If you run the sample and enter Marseille or Brandenburg, in the text boxes below the grid and page you should be able to extract the new data. I recommend that you show us your markup and code-behind so we could investigate further. Additionally please elaborate more on the product version used in the application.

Regards,
Angel Petrov
Telerik

[Alexander]

I am not sure how to run the test website but nothing stands out in my code from the code that you have posted.
The version I am using is v.2011.2.915.40

Here is the markup page

<%@ Page Title="Weekly Timesheet Report" Language="vb" AutoEventWireup="false" MasterPageFile="~/Site.Master" CodeBehind="WeeklyTimesheetReport.aspx.vb" Inherits="PayrollWebApp.WeeklyTimesheetReport" %>
<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
    <style type="text/css">
        .page
        {
            min-width: 1600px;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <telerik:RadScriptManager ID="RadScriptManager1" runat="server">
        <Scripts>
            <asp:ScriptReference Assembly="Telerik.Web.UI"
                Name="Telerik.Web.UI.Common.Core.js">
            </asp:ScriptReference>
            <asp:ScriptReference Assembly="Telerik.Web.UI"
                Name="Telerik.Web.UI.Common.jQuery.js">
            </asp:ScriptReference>
            <asp:ScriptReference Assembly="Telerik.Web.UI"
                Name="Telerik.Web.UI.Common.jQueryInclude.js">
            </asp:ScriptReference>
        </Scripts>
    </telerik:RadScriptManager>
    <h1>Weekly Timesheet Report</h1>
    <br />
    <br />
    <div id="reporthead">
    <br />

    Location:
    <asp:DropDownList ID="ddlLocation" runat="server">
    </asp:DropDownList>

    Week 1 Start Date:
        <telerik:RadDatePicker ID="week1StartDate" runat="server" Culture="en-US">
        </telerik:RadDatePicker>

    Week 2 Start Date:
        <telerik:RadDatePicker ID="week2StartDate" runat="server" Culture="en-US">
        </telerik:RadDatePicker>

    Week 3 Start Date:
        <telerik:RadDatePicker ID="week3StartDate" runat="server" Culture="en-US">
        </telerik:RadDatePicker>

        <asp:Button ID="btnRefresh" runat="server" Text="Search" />

    <asp:ImageButton ID="ImageButton2" ImageUrl="~/Resources/file-extension-xls-html-icon.png"
    runat="server" CssClass="ImageButtons"/>
    <asp:ImageButton ID="ImageButton3" ImageUrl="~/Resources/file-extension-pdf-icon.png"
    runat="server" CssClass="ImageButtons"/>
    <asp:ImageButton ID="ImageButton4" ImageUrl="~/Resources/file-extension-doc-icon.png"
    runat="server" CssClass="ImageButtons" />
    <asp:ImageButton ID="ImageButton5" ImageUrl="~/Resources/file-extension-csv-icon.png"
    runat="server" CssClass="ImageButtons"/>
    <br />
    <br />
    </div>
    <telerik:RadGrid ID="timesheetReport" runat="server" CellSpacing="0"
        DataSourceID="timeReport" GridLines="None" AllowFilteringByColumn="True"
        AllowPaging="True" AllowSorting="True">
        <ClientSettings AllowColumnsReorder="True" ReorderColumnsOnClient="True">
            <Selecting AllowRowSelect="True" />
        </ClientSettings>
<MasterTableView AutoGenerateColumns="False" DataSourceID="timeReport">
<CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>

<RowIndicatorColumn FilterControlAltText="Filter RowIndicator column">
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>

<ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>

    <Columns>
        <telerik:GridBoundColumn DataField="emp_name"
            FilterControlAltText="Filter emp_name column" HeaderText="Name"
            SortExpression="emp_name" UniqueName="emp_name">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="adp_number"
            FilterControlAltText="Filter adp_number column" HeaderText="ADP #"
            SortExpression="adp_number" UniqueName="adp_number">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="location"
            FilterControlAltText="Filter location column" HeaderText="Location"
            SortExpression="location" UniqueName="location">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="dept_num"
            FilterControlAltText="Filter dept_num column" HeaderText="Department #"
            SortExpression="dept_num" UniqueName="dept_num">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="week_start_date" DataType="System.DateTime"
            FilterControlAltText="Filter week_start_date column"
            HeaderText="Week Start Date" SortExpression="week_start_date"
            UniqueName="week_start_date" DataFormatString="{0:M/d/yyyy}">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="total_hours" DataType="System.Decimal"
            FilterControlAltText="Filter total_hours column" HeaderText="Total Hours"
            SortExpression="total_hours" UniqueName="total_hours">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="regular_hours" DataType="System.Decimal"
            FilterControlAltText="Filter regular_hours column" HeaderText="Regular"
            SortExpression="regular_hours" UniqueName="regular_hours">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="overtime_hours" DataType="System.Decimal"
            FilterControlAltText="Filter overtime_hours column" HeaderText="Overtime"
            SortExpression="overtime_hours" UniqueName="overtime_hours">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="vacation_hours" DataType="System.Decimal"
            FilterControlAltText="Filter vacation_hours column" HeaderText="Vacation"
            SortExpression="vacation_hours" UniqueName="vacation_hours">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="holiday_hours" DataType="System.Decimal"
            FilterControlAltText="Filter holiday_hours column" HeaderText="Holiday"
            SortExpression="holiday_hours" UniqueName="holiday_hours">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="funeral_hours" DataType="System.Decimal"
            FilterControlAltText="Filter funeral_hours column" HeaderText="Funeral"
            SortExpression="funeral_hours" UniqueName="funeral_hours">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="sick_hours" DataType="System.Decimal"
            FilterControlAltText="Filter sick_hours column" HeaderText="Sick"
            SortExpression="sick_hours" UniqueName="sick_hours">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="personal_hours" DataType="System.Decimal"
            FilterControlAltText="Filter personal_hours column" HeaderText="Personal"
            SortExpression="personal_hours" UniqueName="personal_hours">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="other_hours" DataType="System.Decimal"
            FilterControlAltText="Filter other_hours column" HeaderText="Other"
            SortExpression="other_hours" UniqueName="other_hours">
        </telerik:GridBoundColumn>
    </Columns>

<EditFormSettings>
<EditColumn FilterControlAltText="Filter EditCommandColumn column"></EditColumn>
</EditFormSettings>
</MasterTableView>

<FilterMenu EnableImageSprites="False"></FilterMenu>

<HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default"></HeaderContextMenu>
    </telerik:RadGrid>
    <asp:SqlDataSource ID="timeReport" runat="server"
        ConnectionString="<%$ ConnectionStrings:Payroll_TimesheetConnectionString %>"
        SelectCommand="SELECT timesheets.emp_name, employees.adp_number, timesheets.location, employees.dept_num, timesheets.week_start_date, timesheets.total_hours, timesheets.regular_hours, timesheets.overtime_hours, timesheets.vacation_hours, timesheets.holiday_hours, timesheets.funeral_hours, timesheets.sick_hours, timesheets.personal_hours, timesheets.other_hours FROM employees INNER JOIN timesheets ON employees.emp_name = timesheets.emp_name WHERE ((timesheets.location = @location) AND (timesheets.week_start_date = @week1StartDate)) OR ((timesheets.location = @location) AND (timesheets.week_start_date = @week2Start)) OR ((timesheets.location = @location) AND (timesheets.week_start_date = @week3Start))">
        <SelectParameters>
            <asp:ControlParameter ControlID="ddlLocation" Name="location"
                PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="week1StartDate" Name="week1StartDate"
                PropertyName="SelectedDate" />
            <asp:ControlParameter ControlID="week2StartDate" Name="week2Start"
                PropertyName="SelectedDate" />
            <asp:ControlParameter ControlID="week3StartDate" Name="week3Start"
                PropertyName="SelectedDate" />
        </SelectParameters>
    </asp:SqlDataSource>
</asp:Content>

Here is the code behind in VB:

Public Class WeeklyTimesheetReport
    Inherits System.Web.UI.Page
    Public Shared menu As New Menu

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If (Page.User.IsInRole("CN-Payroll") Or Page.User.IsInRole("CN-IT Applications")) Then
            ddlLocation.Items.Add("Brampton")
            ddlLocation.Items.Add("Canton")
            ddlLocation.Items.Add("Clarksville")
            ddlLocation.Items.Add("Controls")
            ddlLocation.Items.Add("Lebanon")
            ddlLocation.Items.Add("Mitchell")
            ddlLocation.Items.Add("Somerset")
            ddlLocation.Items.Add("Queretaro")
            ddlLocation.SelectedIndex = 1
        End If
        If Page.User.IsInRole("BR-Timesheet View") Then
            ddlLocation.Items.Add("Brampton")
        End If
        If Page.User.IsInRole("CK-Timesheet View") Then
            ddlLocation.Items.Add("Clarksville")
        End If
        If Page.User.IsInRole("LB-Timesheet View") Then
            ddlLocation.Items.Add("Lebanon")
        End If
        If Page.User.IsInRole("MT-Timesheet View") Then
            ddlLocation.Items.Add("Mitchell")
        End If
        If Page.User.IsInRole("SS-Timesheet View") Then
            ddlLocation.Items.Add("Somerset")
        End If
        If Page.User.IsInRole("QT-Timesheet View") Then
            ddlLocation.Items.Add("Queretaro")
        End If


    End Sub

    Protected Sub btnRefresh_Click(sender As Object, e As EventArgs) Handles btnRefresh.Click
        timesheetReport.MasterTableView.SortExpressions.Clear()
        timesheetReport.MasterTableView.GroupByExpressions.Clear()
        timesheetReport.MasterTableView.Rebind()
    End Sub

    Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ImageButton2.Click
        ConfigureExport()
        timesheetReport.MasterTableView.ExportToExcel()
    End Sub

    Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ImageButton3.Click
        ConfigureExport()
        timesheetReport.MasterTableView.ExportToPdf()
    End Sub

    Private Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ImageButton4.Click
        ConfigureExport()
        timesheetReport.MasterTableView.ExportToWord()
    End Sub

    Private Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ImageButton5.Click
        ConfigureExport()
        timesheetReport.MasterTableView.ExportToCSV()
    End Sub

    Public Sub ConfigureExport()
        timesheetReport.ExportSettings.ExportOnlyData = True
        timesheetReport.ExportSettings.IgnorePaging = True
        timesheetReport.ExportSettings.FileName = ddlLocation.SelectedValue & " Timesheet Report " & week1StartDate.SelectedDate & " & "
    End Sub
End Class

Let me know if you need any other code.

[Angel Petrov]

Hello Alexander,

From the code provided I did not notice anything that can cause the problem on hand. I have modified the previously provided sample in order to filter the data by using the selected dates from the pickers but I did not experience any problems retrieving the information from the database. It would be best to open a formal support ticket and attach a project in which the problem is reproducible. Another option would be to upload the project and provide us with a download link. Once we are able to replicate the issue we should be able to provide a resolution. In attachments you can find the modified website which I have used for testing(if you add the Telerik.Web.UI assembly in the bin folder you should be able to run the app).

Regards,
Angel Petrov
Telerik

[Kiranmayee]

Hi,

I have a radgrid that has a child table using the expandcollapse column. The data in this child table is bound using SqlDataSource. I used one control parameter and it works fine but when I wanted to add one more control parameter it would show any child table.

 

ASPX:

<telerik:RadGrid ID="rgSID" AllowAutomaticUpdates="true" AutoGenerateColumns="false" AllowSorting="TRUE" ShowHeaderWhenEmpty="true" EnableEmbeddedskins="True" GridLines="None" 
                runat="server" Skin="Black" ShowFooter="True"  AllowPaging="True" pagesize ="20" OnNeedDataSource="rgSID_NeedDataSource"  MasterTableView-ExpandCollapseColumn-HeaderStyle-Width="5px">
                    <HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Black">
                    </HeaderContextMenu>
                <MasterTableView AutoGenerateColumns="False" DataKeyNames="INBQ_REF, SID, DUNS, STATUS, OUTBQ_REF, TRAILER_ID, LOC_ID, ROUTE, DOCK, DST_FCS, OB_TRAILER_ID, SHIP_DAY, SYS_DT_TM, TOTAL_WGT" 
                    DataSourceID="" EnableHierarchyExpandAll="true"  TableLayout="Fixed">
                <EditFormSettings>
                     <EditColumn CancelImageUrl="Cancel.gif" EditImageUrl="Edit.gif" InsertImageUrl="Update.gif" UpdateImageUrl="Update.gif">
                     </EditColumn>
                </EditFormSettings>
                    <DetailTables>
                 <telerik:GridTableView AutoGenerateColumns="false" DataKeyNames="SID, DUNS, STATUS, LOAD_ID" DataSourceID="SqlDataSource5"  AllowSorting="true" EnableHierarchyExpandAll="true"  >
                         <ParentTableRelation>
                             <telerik:GridRelationFields DetailKeyField="SID, DUNS" MasterKeyField="SID, DUNS" ></telerik:GridRelationFields>
                        </ParentTableRelation>
                           <ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column"  /> 
                               <Columns>

                               </Columns>

  </telerik:GridTableView>
                         </DetailTables>
                    <Columns>

                        <telerik:GridBoundColumn DataField="SID"  DefaultInsertValue="" 
                                HeaderText="SID" ReadOnly="True" SortExpression="SID" UniqueName="SID" Visible="TRUE" AllowSorting="true" HeaderTooltip="SID/ASN number">
                                <HeaderStyle Font-Size="8pt" Width="25px" />
                                <ItemStyle Font-Size="10pt" ForeColor="WHITE" HorizontalAlign="LEFT"  />
                         </telerik:GridBoundColumn>

                        <telerik:GridBoundColumn DataField="DUNS"  DefaultInsertValue="" 
                                HeaderText="DUNS" ReadOnly="True" SortExpression="DUNS" UniqueName="DUNS" Visible="TRUE" AllowSorting="true" HeaderTooltip="Suppliers DUNS number">
                                <HeaderStyle Font-Size="8pt" Width="25px" />
                                <ItemStyle Font-Size="10pt" ForeColor="WHITE" HorizontalAlign="LEFT"  />
                        </telerik:GridBoundColumn>

</Columns>

 </MasterTableView>

 </telerik:RadGrid>

 

 

 

 <asp:Label ID="Label11" runat="server" Visible="false" TEXT='<%# Eval("SID")%>'></asp:Label>
 <asp:Label ID="Label19" runat="server" Visible="false" Text='<%# Eval("DUNS")%>'></asp:Label>

<asp:SqlDataSource ID="SqlDataSource5" runat="server"  ConnectionString="<%$ ConnectionStrings:FCSConnectionString %>" 
                SelectCommand="SELECT LOAD_SID.SYS_DT_TM, LOAD_SID.LOAD_ID, LOAD_SID.SID, LOAD_SID.PART_NUM, LOAD_SID.SID_QTY, LOADS.DUNS AS DUNS, LOADS.STATUS FROM LOAD_SID INNER JOIN LOADS ON LOAD_SID.LOAD_ID = LOADS.LOAD_ID  WHERE SID = @SID AND DUNS=@DUNS">
            <SelectParameters>
                  <asp:ControlParameter Name ="SID" ControlID="Label11"  PropertyName ="Text"/>
                  <asp:ControlParameter Name ="DUNS" ControlID="Label19"  PropertyName ="Text"/>
            </SelectParameters>
 </asp:SqlDataSource>

 

 

 

Thank you.

 

[Eyup]

Hello Kiranmayee,

I am sending 2 RadGrid web sites to demonstrate how you can achieve this requirement.
I hope this will prove helpful.

Regards,
Eyup
Progress Telerik