0

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 corresponding 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?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
sagesky36
  • 4,542
  • 19
  • 82
  • 130
  • datatextfield="CruiseLine" datavaluefield="CruiseLine" Looks wrong. If you do not use the value, like an ID field, then the parameter will populate but will pass invalid data to the cascading sproc, which in turn will not run properly. – Volvox Jan 22 '13 at 03:07
  • Volvox - the only data required to populate the cascading ddl is just the text CruiseLine value. Notice the SPROC. It works fine in SSRS and running the sql statement manually, but I can't get it to populate via a sql – sagesky36 Jan 22 '13 at 14:08

0 Answers0