ASP data access difficulties

I'm using ASP pages to access a Microsoft SQL 2005 SP1 database server for information that populates dropdown menus. I ran into an interesting problem. We can query all existing data without a problem (all expected rows return and correctly populate the dropdowns).

I manually add a new row to the database table for a dropdown. I can manually query the database from the SQL2005 management tool, and the new records are included in the results.

I load the ASP page, and the newly added records never appear in the dropdown... only the old, existing data.

What could cause this? The queries from ASP are the same as the queries I make directly from the SQL server.

Could you please post the part of your code that is filling the dropdown boxes ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

-relevant snip-

<% SQL = "sp_GetVMFarmList"
set rs = conn.execute(SQL) %>
<select name="VMFarm" class="InputBox">
<option></option>
<% while not rs.eof
strSelected = ""
if intFarmID <> "" then
if cint(intFarmID) = cint(rs("FarmID")) then
strSelected = "Selected"
end if
end if %>
<option <%=strSelected %> value="<%=rs("FarmID")%>"><%=rs("FarmName")%></option>

<% rs.movenext
wend %>
</select>

|||

Any page directives or server settings to cache the pages ? BTW you should not use sp_ prefixes as SQL will *always* do a recompilation to the procedures as the prefix is reserved for system procedures. Better use usp_Something or spSomething.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

No caching, nothing on the web server (IIS 6) has changed. I restarted the IIS service several times to make sure it wasn't trying to hold onto any data|||

Probably a silly question, but have you checked from another computer to make sure your computer isn't caching the data?

|||

Oh yes... in fact it was several other users reporting that they couldn't see the new data I added to the tables. they all use different PCs.

|||

I assume you've made a backup of your data If not, that would be my first step right now. Now I'm going to go into what I call "guess mode because I don't really know what's going on"....

Then I would start playing with it, see if the website even recognizes any sort of change, such as removing some rows. If nothing is responding I would drop the table and reload it and see if that helps. However, like I said, you definitely need to have a backup of your data because you never know how the reloading stage might go.

|||When you say "I can manually query the database from SQL2005 management tool", you mean you run sp_GetVMFarmList, or you are doing the query that is defined in the stored procedure?

|||

Both

|||

try Response.Expires = 0 at the top of the ASP page, to make sure you don't have some additional caching. But probably you should now examine your update code, instead of the dropdown code.