SharePoint web part - SQL statement not reflecting change

Question

I recently wrote a SharePoint web part in C# that pulls information from a SQL database with some parameterized queries based on user selections. This involves a list of customers in a dropdownlist that is populated from a datareader like so:

while (dr.Read())
{
    string customerNameAndCity = dr.GetSqlString(1).Value + " - " +
        dr.GetSqlString(2).Value;
    customers.Items.Add(new ListItem(customerNameAndCity, dr.GetSqlString(0).ToString()));
}

Basically, this reads the name and city of each customer and concatenates the output into a string that is added into the Items collection of the dropdownlist (called "customers"). The SQL command it uses is:

SELECT fldCustomer_ID, fldCustomerName, fldCity
FROM dbo.tblCustomers
WHERE fldActive = 'True'
ORDER BY fldCustomerName

Obviously, there is a column called "fldActive," and when this field is set to 'True', it allows this piece of code to add that customer to this dropdownlist.

I haven't had any problems with it thus far. However, this morning I set the fldActive column of a test customer that I had set up to 'True' in order to test some new stored procs and attempted to view that customer's information. No luck. The customer's information is not rendered in my dropdownlist. I then did an iisreset and reset the database (SQL Server 2005). Still no luck. So I opened up the source code and stepped through the method that populates this dropdownlist. When I step through it, I can see that the test customer's data is, in fact, loaded, the text is concatenated, and the Items.Add completes with no exception. After the loop completed, I checked the customers dropdownlist and the entry was, in fact, there. However, once the page loaded, I had the same problem: all of the entries appeared; the test customer simply wasn't there. There was no empty entry or anything; it was simply as if it had never populated.

The only thing that I can think of is that there is some caching going on somewhere, but I just don't know why this is occurring. I cleared the IE cache as well, and I had assumed that between that and the iisreset and database restart, I would be covered. Thanks in advance for any help you can give.

EDIT: I just did a full server restart, and now the information is showing up properly. Clearly, an individual component needed to be restarted in order for this to display properly. Does anyone have any idea as to what that may be? It's obviously not the database or IIS, and I don't want to have to restart the entire server every time data changes.

EDIT: I just had this problem again. This time, I changed a SELECT statement to add one field, changed an ORDER BY statement, updated the web part on the server, and ran it. The ORDER BY was respected, but the new field doesn't show up. Sql Profiler shows me that the new SELECT statement is in the query, Fiddler shows that the HTTP requests are having no problems, and I cleared the IE cache, but that column is just not rendering in the .aspx page. This time, even a full server restart is not helping.

Answer 1

Did you consider browser caching? Seems silly but has cause me problems more times than I can count?

One way to troubleshoot this is to use the free tool Fiddler (or some other http traffic monitor) to watch the content going back & forth between server and browser.

Answer 2

One way to force the browser to reload the page is to add a random query parameter to the URL. Another way is to use Response.Cache.SetCacheability(HttpCacheability.NoCache);