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.

Keith Bloom · Accepted Answer · 2011-07-06 15:29:15Z

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>

edited Jul 6, 2011 at 15:29

answered Jul 6, 2011 at 13:59

Keith Bloom

2,4493 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

saurabh goyal Over a year ago

hey thanks but here i forgot to mention my filter parameter.

Keith Bloom Over a year ago

Good point. I have updated the answer and change the SelectParameters to a FilterParameters collection

saurabh goyal Over a year ago

i totally agree with you Keith .it should work .but i dont know why even after adding your code it is not working

saurabh goyal Over a year ago

conclusion filter expression sucks i am working with where statement to solve my problem

mslliviu · Accepted Answer · 2011-07-06 13:50:21Z

0

try a condition like this:

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

answered Jul 6, 2011 at 13:50

mslliviu

1,1383 gold badges12 silver badges30 bronze badges

Comments

david.s · Accepted Answer · 2012-11-17 12:58:00Z

0

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

edited Nov 17, 2012 at 12:58

david.s

11.4k6 gold badges54 silver badges83 bronze badges

answered Jul 6, 2011 at 12:30

Ovais Khatri

3,21118 silver badges15 bronze badges

Comments

Laurel · Accepted Answer · 2016-07-19 01:36:53Z

-1

Add CancelSelectOnNullParameter="false" to your SqlDataSource.

edited Jul 19, 2016 at 1:36

Laurel

6,23114 gold badges35 silver badges60 bronze badges

answered Jul 18, 2016 at 23:11

Paul Marangoni

1591 gold badge1 silver badge5 bronze badges

Collectives™ on Stack Overflow

Sqldatasource where parameter

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related