0

Context: I'm designing a simple ASP.NET webpage where I have a search box where the user can enter a User ID (int) and when they click a search button, a GridView control displays the results of a SQL SELECT query that checks if an entry with the specified User ID exists. This functionality all works and I believe is fairly simple.

I've added another button that I would like to execute a different SQL SELECT query that returns all entries in the table.

Here is the first SELECT query, which is the SelectCommand of the SqlDataSource that the GridView uses: SELECT * FROM [tblUser] WHERE [UserID] = @UserID

Here is the new SELECT query that I want to execute when a user clicks the new button: SELECT * FROM tblUser

Question: How can I use the "OnClick" event (in the code behind) to execute this new query and display the results in the same GridView?

What I've Tried: This seems like a pretty logical way of executing the new statement: change the SelectCommand of the SqlDataSource that the GridView control uses, execute that SelectCommand, then display the results of that command in the GridView. Unfortunately it just results in an empty GridView, no column labels or anything.

    string selectAll = "SELECT * FROM tblUser";        //new query to execute
    string oldSelect = SqlDataSource1.SelectCommand;    //stores old query in temp variable
    SqlDataSource1.SelectCommand = selectAll;           //sets the SelectCommand of the DataSource to the new query
    SqlDataSource1.Select(DataSourceSelectArguments.Empty);    //executes the SelectCommand
    GridView1.DataBind();                              //binds the GridView to the DataSource
    SqlDataSource1.SelectCommand = oldSelect;          //returns the select command to its original value
Improve this question

3 Answers 3

Reset to default
2

The problem is that you forgot to assign the DataSource and DataSourceID to the GridView after changing the SelectCommand:

        string selectAll = "SELECT * FROM tblUser";        //new query to execute
        string oldSelect = SqlDataSource1.SelectCommand;    //stores old query in temp variable
        SqlDataSource1.SelectCommand = selectAll;           //sets the SelectCommand of the DataSource to the new query
        SqlDataSource1.Select(DataSourceSelectArguments.Empty);    //executes the SelectCommand

        GridView1.DataSourceID = "";
        GridView1.DataSource = SqlDataSource1;

        GridView1.DataBind();                              //binds the GridView to the DataSource
        SqlDataSource1.SelectCommand = oldSelect;

And to return to your old select, in a different button click event:

SqlDataSource1.Select(DataSourceSelectArguments.Empty);    //executes the SelectCommand
        GridView1.DataSourceID = "";
        GridView1.DataSource = SqlDataSource1;
        GridView1.DataBind();
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Ah, you're right! However, just changing this didn't do it. I needed to also remove the SelectParameters for the "Show All" functionality to work for some odd reason. Thanks for your help though!
1

EDIT

considering you use entity framework,

in codebehind:

Public Shared Function getUsers(optional byval userid as integer = 0) as list(of myusers)
    Using db as new your_user_entity
        dim users = (from u in db.Users select u)
            if userid = 0 then
                return users.toList()
            else
                return users.where(function (x) x.userid = userid).toList()
        End If
    End Using
End

after this, you can call this function on one button click like:

gridview.datasource = getUsers() ' will give you all users
gridview.databind

on another button:

gridview.datasource = getUsers(10) ' will only give you the user with id=10
girdview.databind

change your logic a little bit. In code behind, create two functions

  1. getSingleUserDetails
  2. getAllUsers

both functions should return List(of Your_user)

then in button click events for both buttons,

e.g. btnGetSingle_Click

gridview.datasource = getSingleUserDetails(10)
gridview.databind

btnGetAllUsers_Click

gridview.datasource = getAllUsers
gridview.databind

Note: I am omitting some facts such as you can use overloaded functions, entity framework etc..

Improve this answer

4 Comments

I'm a bit confused: do you recommend I create two separate data sources, one for each button click function? How do I get the functions to return the data as a List? Finally (Sorry for the long winded comment), how do I get that list into the GridView?
i am not saying you should create two data sources but i am suggesting you 'shape' your query so that you have more functional one. not even mentioning less and re-usable code.. see my edited answer in a minute...
Ok, please see the edit above. Don't worry about long comments or anything, please do say or ask whatever is needed to get you going. Although it is now quite late at this part of the world and I am now off to counting sheep, there will be a lot more people to help you here..
Thanks, I appreciate your help! I figured it out with some help from @Norbeto's answer.
0

This is what I had to do:

1) Assign the DataSource and DataSourceID of the GridView after changing the SelectCommand.

2) Remove the SelectParameter of the search textbox before executing the new SelectCommand, then add it back before doing the regular search.

Here's my code for my button click event for the "Show All" button:

    protected void ShowAllUsers(object sender, EventArgs e)
{
    UserIDTextBox.Text = "";
    SqlDataSource1.SelectCommand = selectAll;
    SqlDataSource1.SelectParameters.Remove(SqlDataSource1.SelectParameters["UserID"]);

    GridView1.DataSourceID = "";
    GridView1.DataSource = SqlDataSource1;
    GridView1.DataBind();

    SqlDataSource1.SelectCommand = selectUser;        
}

And then for my regular search button:

        if (SqlDataSource1.SelectParameters.Count == 0)
        {
            SqlDataSource1.SelectParameters.Add("UserID", UserIDTextBox.Text);
        }
        SqlDataSource1.SelectCommand = selectUser;

selectUser and selectAll are the strings holding the SQL querys.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.