fteching data in object datasource

TrialUser
TrialUser used Ask the Experts™
on
The user enters a text in my webpage and clicks on the "search" button. Only on this button click I want to fect data by calling the SQL server stored procedure. I use objectdatasource to bind data to listview or gridview. how can I fecth the data in the object datasource only on the button click. Please help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What type of object it is?
Can you post the code here. I Can help you out.. :)
plz post code for both your class and stored procedure.I can easily help you out..Thank you
Here is a brief summary, followed by a short tutorial.
1. Add your listview or gridview to the page. A gridview will be assumed in this example.
2. Add your textbox and a Search button to your web form.
3. Create a click event for the Search button.
4. Create a method to retrieve the data from the database and bind it to your gridview.
5. Within the button click event, call your grid binding method.

Here is how you can do this:
#1 & #2 - Add your markup to the web form.
<asp:TextBox runat="server" ID="txtSearch" MaxLength="50" />
<asp:Button runat="server" ID="btnSearch" Text="Search" onclick="btnSearch_Click" />
<br /><asp:Label runat="server" ID="labNoData" Text="No results found for this search." Visible="false" />
<asp:GridView runat="server" id="grdResults" AutoGenerateColumns="true" />

Open in new window

#3 - Create a click event for the search button.
To easily generate the code for the button search event, select the Design tab from Visual Studio and double click on the button.  Visual Studio will add the onclick event to the button, and will stub in the method in the code behind, like so:
        protected void btnSearch_Click(object sender, EventArgs e)
        {
            // TODO: Retrieve search data and bind to grid.
        }

Open in new window


#4 - Add method to retrieve data.
Start by adding "using" statements for the following namespaces in your code behind.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Open in new window


Here is a sample method for retrieving the data.  There are numerous ways to do this, but I've found this to be efficient.  The "using" statements are handy because they clean up the objects.  This is deliberately simple, using one stored procedure parameter of @SearchText.
        private DataSet SearchDB(string SearchText)
        {
            DataSet dsOut = null;

            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("spSearchQuery", cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cn.Open();

                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.VarChar, 50)).Value = SearchText;

                    SqlDataAdapter da = new SqlDataAdapter();
                    dsOut = new DataSet();
                    da.SelectCommand = cmd;
                    da.Fill(dsOut);
                }
            }
            return dsOut;
        }

Open in new window


#5 - Bind the grid within your search click event.
Now that your data retrieval code is in place, now it's time to fill in the rest of your button click event.
        protected void btnSearch_Click(object sender, EventArgs e)
        {
            string search = txtSearch.Text.Trim();
            if (search.Length > 0)
            {
                DataSet ds = SearchDB(search);
                grdResults.DataSource = ds;
                grdResults.DataBind();
                labNoData.Visible = (grdResults.Rows.Count == 0);
            }
        }

Open in new window


This code has just one validation check, to verify that a search value was entered.  You may want to put validation on the client side with validation controls, or have additional criteria like formatted dates.  Again, this is deliberately simple to illustrate the core pieces.

After assigning the search text to a string, call the SearchDB() method you wrote in step 4, and assign it to ds.  Then bind ds to your grid.  If the grid doesn't have rows, show the ""No results found for this search" message via labNoData.

For production code you would also want to add a try/catch block to this button search event to display a user-friendly error message if an exception occurred.

Your real-life results page will likely be much more complex, but this should enable you to get rolling.  Good luck.

Author

Commented:
I am sorry I should have been more specific with my question.  I am already doing somehting like what stormack has explained. But I need to move to an object datasource since I am using server side paging.

So I am wondering how and where I do:

DataSet ds = SearchDB(search);
grdResults.DataSource = ds;
grdResults.DataBind();

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial