locked
SQLDataSource with 2 Different ControlParameters RRS feed

  • Question

  • User505831643 posted

    Hello everyone,...

    I am trying to pass a DropDownList SelectedValue as the Database Table Field in which to search, and also a TextBox Text as the value in which to search the particular Field in which I specified... The problem is that when I hit the submit button, it doesn't return any results... Is there something to do with PostBack or something or is my SQLDataSource coding wrong or what?

    Looks like this...

        <asp:Label ID="SearchLabel" runat="server" Text="Search By"></asp:Label>
        <asp:DropDownList ID="SearchParameterDropDownList" runat="server">
            <asp:ListItem Value="AssetID">Asset ID</asp:ListItem>
            <asp:ListItem Value="AssetType">Asset Type</asp:ListItem>
            <asp:ListItem Value="AssetManufacturerMake">Manufacturer</asp:ListItem>
            <asp:ListItem Value="AssetModelNoModel">Model</asp:ListItem>
            <asp:ListItem Value="AssetLocation">Location</asp:ListItem>
            <asp:ListItem Value="AssetAssignedTo">Assigned To</asp:ListItem>
        </asp:DropDownList>
        <asp:TextBox ID="SearchCriteriaTextBox" runat="server"></asp:TextBox>
        <asp:Button ID="SearchButton" runat="server" Text="Search" />

    Here is what my SQLDataSource looks like...

        <asp:SqlDataSource ID="GetAssetsSqlDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:bbfcapzi_tocodeindotnetConnectionString %>" 
            SelectCommand="SELECT [AssetID], [AssetType], [AssetManufacturerMake], [AssetModelNoModel], [AssetLocation], [AssetAssignedTo] FROM [Assets] WHERE (@SearchParameterDropDownList = @SearchCriteriaTextBox) ORDER BY [AssetID]">
            <SelectParameters>
                <asp:ControlParameter ControlID="SearchParameterDropDownList" Name="SearchParameterDropDownList" 
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="SearchCriteriaTextBox" Name="SearchCriteriaTextBox" 
                    PropertyName="Text" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>
    Sunday, December 25, 2011 6:17 PM

Answers

  • User-231977777 posted

    Hi,

    after spending some time in this Problem , i found that SQLDataSource doesn't Resolve SELECT Command to Replace Column name wth the Value comming from TextBox/Dropdownlist.

    So,why dynamiclly Construct SELECT command  usign C# Code,Here is my Solution:-

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlDataSource1.SelectCommand = string.Format("SELECT * FROM [authors] WHERE {0}='{1}'", TextBox1.Text, DropDownList1.SelectedValue);
        }

    but I found that the Select Command will be populated the first Time Like this : "Select ........................from ... Where=" , as you see here there is no condition.so it will throw a Syntax Error.

    to avoid this , we can tell him that we have a parameters like this :-

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ............................... >
                <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="ColName" PropertyName="Text" />
                    <asp:ControlParameter ControlID="DropDownList1" Name="au_id" 
                        PropertyName="SelectedValue"  />
                </SelectParameters>
            </asp:SqlDataSource>

    and is worked for me ,

    and I Found  another solution : using FilterExpression ,but need to be implemented a lot.

    "SELCT * FROM ........... "  FilterExpression="{0}='{1}'"

    <filterExpression>

    <asp:Control........................................./>

    <asp:Control........................................./>

    </filterExpression>

    Hope this Helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 26, 2011 7:46 AM

All replies

  • User-231977777 posted

    Hi,

    your Code Looks Good , and it should Work ,BUT it doesn't make sens , Specially this Part :-

    WHERE (@SearchParameterDropDownList = @SearchCriteriaTextBox)

    that means when DropDownlist Selected Value is the same Value of TextBox , So ,it doesn't related to your Table.

    I Mean that , All Records in your Table Will be in the Result Set if the condition is True(do you need that ?, if so , your code works good).

    Hope this Helps

    Sunday, December 25, 2011 9:03 PM
  • User505831643 posted

    That's the part that is giving me trouble, that I can't figure out... I want the user to be able to select which Database Table Column he wants to search from, from the DropDownList ... and then enter what it is he wants to search for in the TextBox ...

    I need to be able to make the Column/Field name dynamic instead of static...

    In PHP/MySQL I would have simply wrote

    SELECT * FROM Assets WHERE $TableColumn = $SearchValue

    Is there no way to make the WHERE COLUMN a variable?

    Sunday, December 25, 2011 9:21 PM
  • User-231977777 posted

    mmmmmmmmmhhhhh,

    you need to do the following :

    Where ([@SearchParameterDropDownList] = @SearchCriteriaTextBox )
    Sunday, December 25, 2011 9:26 PM
  • User505831643 posted

    When I do that, I get this error... "Exception Details: System.Data.SqlClient.SqlException: Invalid column name '@SearchParameterDropDownList'."

    So I thought about removing the one ControlParameter, because I have 2 in there now, but I get the same error...

    <asp:ControlParameter ControlID="SearchParameterDropDownList" Name="SearchParameterDropDownList"
             PropertyName="SelectedValue" Type="String" />

    Sunday, December 25, 2011 9:45 PM
  • User-231977777 posted

    Hi,

    after spending some time in this Problem , i found that SQLDataSource doesn't Resolve SELECT Command to Replace Column name wth the Value comming from TextBox/Dropdownlist.

    So,why dynamiclly Construct SELECT command  usign C# Code,Here is my Solution:-

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlDataSource1.SelectCommand = string.Format("SELECT * FROM [authors] WHERE {0}='{1}'", TextBox1.Text, DropDownList1.SelectedValue);
        }

    but I found that the Select Command will be populated the first Time Like this : "Select ........................from ... Where=" , as you see here there is no condition.so it will throw a Syntax Error.

    to avoid this , we can tell him that we have a parameters like this :-

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ............................... >
                <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="ColName" PropertyName="Text" />
                    <asp:ControlParameter ControlID="DropDownList1" Name="au_id" 
                        PropertyName="SelectedValue"  />
                </SelectParameters>
            </asp:SqlDataSource>

    and is worked for me ,

    and I Found  another solution : using FilterExpression ,but need to be implemented a lot.

    "SELCT * FROM ........... "  FilterExpression="{0}='{1}'"

    <filterExpression>

    <asp:Control........................................./>

    <asp:Control........................................./>

    </filterExpression>

    Hope this Helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 26, 2011 7:46 AM
  • User505831643 posted

    Thank you very much for all of  your time and energy you put into finding me a solution to this problem, I very much appreciate it...

    Monday, December 26, 2011 12:32 PM
  • User-231977777 posted

    Thank you very much for all of  your time and energy you put into finding me a solution to this problem, I very much appreciate it...

    You Are Welcome ,

    Monday, December 26, 2011 12:49 PM