SqlDataSource not being populated with parameter

2 posts, 0 answers
  1. Bill
    Bill avatar
    175 posts
    Member since:
    Apr 2010

    Posted 21 Jan 2013 Link to this post

    I am executing an SSRS rpt and need to duplicate this in a web project with sqldatasource id's.

    The ssrs rpt parameters work perfectly as far as being populated, but when I try to get the same thing done in the web version, the second parameter list never gets populated.

    The Ship parameter depends upon what is selected in the CruiseLine parameter.

    The parameters in the web version are as follows:

    <tr>
                    <td>
                        Select a CruiseLine:
                        <telerik:radcombobox id="RadComboBox1" runat="server" checkboxes="True" enablecheckallitemscheckbox="False"
                            skin="Web20" sort="Ascending" allowcustomtext="True" datasourceid="SqlDataSource1"
                            datatextfield="CruiseLine" datavaluefield="CruiseLine">
                        </telerik:radcombobox>
                        <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator3" ValidationGroup="A"
                            ControlToValidate="RadComboBox1" ErrorMessage="Choose a CruiseLine!"></asp:RequiredFieldValidator>
                    </td>
                    <td>
                        Select a Ship:
                        <telerik:radcombobox id="RadComboBox2" runat="server" checkboxes="True" enablecheckallitemscheckbox="False"
                            skin="Web20" sort="Ascending" allowcustomtext="True" datasourceid="SqlDataSource2"
                            datatextfield="Ship" datavaluefield="Ship">
                        </telerik:radcombobox>
                        <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator2" ValidationGroup="A"
                            ControlToValidate="RadComboBox2" ErrorMessage="Choose a Ship!"></asp:RequiredFieldValidator>
                    </td>
                </tr>

    Here are the corresponidng SQL data sources:
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PrepaidConnectionString %>"
                SelectCommand="SelectCruiseLine" DataSourceMode="DataSet" EnableCaching="true"
                SelectCommandType="StoredProcedure"></asp:SqlDataSource>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PrepaidConnectionString %>"
                SelectCommand="SelectShip" DataSourceMode="DataSet" EnableCaching="true" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:Parameter Name="CruiseLine" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>

    The first sqldatasource executes the following sql (from a stored procedure and populates the list with no problem:
    SELECT DISTINCT CruiseLine
            FROM dbo.ShipProductPrices
            ORDER BY CruiseLine

    The second sqldatasource executes the following sql (also from a stored procedure):
    ALTER PROCEDURE [dbo].[SelectShip] 
    @CruiseLine NVARCHAR(MAX)
    AS
    BEGIN
        SET NOCOUNT ON;
      
        SELECT DISTINCT Ship
            FROM dbo.ShipProductPrices
            WHERE CruiseLine IN (SELECT * FROM dbo.SplitParameterValues(@CruiseLine, ','))
            ORDER BY Ship
    END

    For now, I'm just selecting one cruiseline and I expect that when I pull down the dropdownlist for the second combobox, it should run a query similiar to the following and have the Ship combobox populated:
    SELECT DISTINCT Ship
            FROM dbo.ShipProductPrices
            WHERE CruiseLine IN (SELECT * FROM dbo.SplitParameterValues('CCL', ','))
            ORDER BY Ship

    Notice the parameter type of the Ship combobox and the type of the SelectParameter of the SelectShip datasource. They are a little bit different. Don't know if this makes a difference or not.

    The SelectShip SPROC executes a UDF which again is part of the sproc that I ran above manually with data coming out in sql management studio.

    What do I need to do in order to populate the second combobox?

  2. Hristo Valyavicharski
    Admin
    Hristo Valyavicharski avatar
    975 posts

    Posted 24 Jan 2013 Link to this post

    Hi Wlliam,

    Try to add the following changes:

    • Add AutoPostBack="true", this will cause postback when you select item from RadComboBox1
    <telerik:RadComboBox ID="RadComboBox1" runat="server" CheckBoxes="True" EnableCheckAllItemsCheckBox="False"
                            Skin="Web20" Sort="Ascending" AllowCustomText="True" DataSourceID="SqlDataSource1"
                            DataTextField="CruiseLine" DataValueField="CruiseLine" AutoPostBack="true">
    </telerik:RadComboBox>
    • Change the Select Paramter of SqlDataSource2 to be ControlParameter. It will get the selected value from RadComboBox1 and will filter SqlDataSource2  
      <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PrepaidConnectionString %>"
              SelectCommand="SelectShip" DataSourceMode="DataSet" EnableCaching="true" SelectCommandType="StoredProcedure">
              <SelectParameters>
                  <asp:ControlParameter ControlID="RadComboBox1" Name="CruiseLine" PropertyName="SelectedValue" Type="String" />
              </SelectParameters>
          </asp:SqlDataSource>

    I hope this helps.

    Kind regards,
    Hristo Valyavicharski
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  3. UI for ASP.NET Ajax is Ready for VS 2017
Back to Top