current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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>
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted 
<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.

share|improve this answer
    
@JohnnySun, I made an update –  Adrian Iftode Apr 10 '12 at 16:12
    
it works like a charm! thanks Adrian. Why the first one didn't work for filterExpression? any good article explaining this? –  JohnnySun Apr 10 '12 at 18:44
    
@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".. –  Adrian Iftode Apr 10 '12 at 19:00
add comment
up vote 0 down vote

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
}
share|improve this answer
    
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. –  JohnnySun Apr 10 '12 at 15:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.