asp.net SqlDataSource FilterExpression empty string logic

Question

here's the logic i want to perform.

When the textbox is empty, i want the datagrid showing no record.
When the textbox is not empty, then the datagrid will filter the data.

Right now, when the textbox is empty, it shows all the records.

How can i fix that? Thanks in advance!

here's the code block:

<asp:SqlDataSource ID="dsGridview" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
    SelectCommand="SELECT UserName, gender, age FROM users"
    FilterExpression="UserName like '%{0}%'">
    <FilterParameters>
        <asp:ControlParameter Name="UserName" ControlID="txtSearch" PropertyName="Text" />
    </FilterParameters>
</asp:SqlDataSource>

Adrian Iftode · Accepted Answer · 2012-04-10 16:12:16Z

1

<asp:SqlDataSource ID="dsGridview" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
    SelectCommand="SELECT UserName, gender, age FROM users
                   UserName like '%' +@UserName + '%' and @UserName is not null">
    <SelectParameters>
        <asp:ControlParameter Name="UserName" ControlID="txtSearch" PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>

I noticed FilterExpression behaves a little different (it wraps the parameter name or value with brackets, actually it does SQL escaping), so to check @UserName for null it works with SelectParameters.

edited Apr 10, 2012 at 16:12

answered Apr 10, 2012 at 15:44

Adrian Iftode

15.7k4 gold badges51 silver badges73 bronze badges

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

2 Comments

sbs Over a year ago

it works like a charm! thanks Adrian. Why the first one didn't work for filterExpression? any good article explaining this?

Adrian Iftode Over a year ago

@JohnnySun, I don't know any article, while testing it I found it does some kind of replacement. So it translates "@UserName is not null" to "[value entered in textbox] is not null"..

rick schott · Accepted Answer · 2012-04-10 15:45:32Z

0

Just use a RequiredFieldValidator on txtSearch or on the server side use the below check where appropriate. No need to run a query when you don't want any data returned:

if(String.IsNullOrEmpty(txtSearch.Text))
{
    //don't databind and use validation to tell the user to enter data
}

answered Apr 10, 2012 at 15:45

rick schott

21.1k5 gold badges54 silver badges81 bronze badges

1 Comment

sbs Over a year ago

i'm using ajax on the aspx page, so it won't go to backend code to check whether the textbox is empty. so when the user type something in the textbox, it refresh the GridView without reload the page.

Collectives™ on Stack Overflow

asp.net SqlDataSource FilterExpression empty string logic

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related