Sqldatasource where parameter

Question

I have a sqldatasource, in select command i have @status parameter. The parameter take the value from the textbox at runtime.

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            SelectCommand="SELECT * FROM [project_details] WHERE ([status] = @status)" 
            FilterExpression="title='{4}'" 
            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
            EnableCaching="True">
 <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="status" PropertyName="Text" ConvertEmptyStringToNull="false"
                    Type="String" />
            </SelectParameters>

        </asp:SqlDataSource>

my problem is that when i run the page without entring the parameter in text box sqldatasource is not returing any row.

Answer 1

Looking at the documentation on MSDN, you have to alter how you have setup the SqlDataSource.

Try this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT * FROM [project_details]" 
        FilterExpression="title='{0}'" 
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
        EnableCaching="True">
    <FilterParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="status" PropertyName="Text" ConvertEmptyStringToNull="false" Type="String" />
    </FilterParameters>
</asp:SqlDataSource>

I have removed the Where clause from the query as this will be applied by the filter expression. Also, I have changed the filter expression from title='{4}' to title='{0}'. The documentation states that the number is a placeholder to an item in the FilterParameters collection.

Updated I have also changed the SelectParameters to FilterParameters

Update 2

I have created a working example to finish of this answer. This will filter the Title column using the text from the text box. If this text box is empty it will return all the rows from the table (a scary thought but OK for this example). It is querying the AdventureWorks database for which I set a connection string called AWorks.

<asp:SqlDataSource ID="SqlDataSource1"
    ConnectionString="<%$ ConnectionStrings:AWorks %>"
    SelectCommand="SELECT ContactId, Title, FirstName, LastName FROM Person.Contact"
    FilterExpression="Title='{0}'"
    runat="server">
 <FilterParameters>
    <asp:ControlParameter Name="Title" ControlID="txtTitle" PropertyName="Text" />
 </FilterParameters> 
</asp:SqlDataSource>

<asp:TextBox runat="server" Id="txtTitle"></asp:TextBox>
<asp:Button runat="server" UseSubmitBehavior="true" Text="Submit" />

<asp:GridView 
    DataSourceID="SqlDataSource1"
    AutoGenerateColumns="false"
    runat="server">
<Columns>
    <asp:BoundField Visible="false" DataField="ContactId"></asp:BoundField>
    <asp:BoundField Visible="true" DataField="Title"></asp:BoundField>
    <asp:BoundField Visible="true" DataField="FirstName"></asp:BoundField>
    <asp:BoundField Visible="true" DataField="LastName"></asp:BoundField>
</Columns>
</asp:GridView>

Answer 2

try a condition like this:

(@Status is null or @Status ='' Or Status = @Status)

Answer 3

set ConvertEmptyStringToNull="true" and then try....

Answer 4

Add CancelSelectOnNullParameter="false" to your SqlDataSource.