Using SqlDataAdapter in an ASP.NET page

Question

I'm writing an ASP.NET 2.0 page in VS2008. In my Page_Load method I have the following:

DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();

using (SqlConnection connection = new SqlConnection(conString)) {
    using (SqlCommand command = new SqlCommand(cmdString, connection)) {
        adapter.SelectCommand = command;
        rowCount = adapter.Fill(table);
    }
}

What have I done wrong?

The first time I execute the page, it works fine (Fill returns one row). If I run (Debug) the page a second time, I get zero rows. Likewise if the page is running, and I modify one of the parameters in the URL so that the cmdString changes, I get zero rows. If I make a trivial code change to force a re-compile, the page will again work.

Answer 1

Have you used this in your Page_Load event?

 if(!Page.IsPostBack)
 {
   YourBindDateMethod();
 }

Answer 2

It sounds like the code above is running in the Page_Load event... and it runs only the first time the page loads... do you have the above code inside an statement like this?

protected void Page_Load(object sender, EventArgs e)
    {

        if (!IsPostBack)
        {
            DataTable table = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();

            using (SqlConnection connection = new SqlConnection(conString))
            {
                using (SqlCommand command = new SqlCommand(cmdString, connection))
                {
                    adapter.SelectCommand = command;
                    rowCount = adapter.Fill(table);
                }
            }

        }
    }

Answer 3

My cmdString had Page scope, and I was filling in a parameter with += instead of String.Replace or (more wisely) using a SqlParameter, so it was mangled on every attempt after the first. I'm assuming that the web server embedded in VS keeps the page object alive even when you stop the debugger, which explains why it would only work once after each code change.

Thanks again to Phaedrus for asking: "Are you positive it contains a value on each load?"