Odd results from query

The environment is ASP.Net accessing the SQL server through IIS.
While, I've been told that .Net does not create a cache the behavior I'm
observing for intensive purposes looks as if there is a cache. I'm at a bit
of a loss to explain this behavior. Also, if I wait five or ten minutes, and
refresh I get the correct value.
I have a stored procdure that returns a series of rows, from which I sum a
particular number.
There is an action which causes another row to be added.
However, when I make this change to the database the change is not returned
in a query immediatly following the update. I have simultaneously run sql
query analyzer and verified that the table was updated correctly.
When the form is first displayed and then shortly after I perfomr the update
the value is recalculated via the following mechanism:
Using the global connection string a new SqlConnection object is built. Then
a new SqlCommand object is built using the new SqlConnection. The command
type is stored procedure. After the SqlCommand is setup (the command text and
parameters filled in), a new SqlDataAdapter is built using the SqlCommand.
The SqlDataAdapter fills a new DataSet, via the Fill method.
When I say that the object is new I mean that literally, as in
SqlCommand cmd = new SqlCommand (ConnectionString);
I would very much like to get the correct result right away.
Thanks,
Dave
I think you should share more details about the nature of your stored proc.
Are any parameters being passed to the procedure? When you say 5 or 10
minutes have passed before it returns the correct sum, are you using the same
active browser window, or are you starting a new browser (thus starting a new
session, and perhaps refreshing the state with the server, thus passing new
parameters to the procedure).
Just a guess - I'm probably way off and you probably already thought of all
that
Brian
"Dave" wrote:

> The environment is ASP.Net accessing the SQL server through IIS.
> While, I've been told that .Net does not create a cache the behavior I'm
> observing for intensive purposes looks as if there is a cache. I'm at a bit
> of a loss to explain this behavior. Also, if I wait five or ten minutes, and
> refresh I get the correct value.
> I have a stored procdure that returns a series of rows, from which I sum a
> particular number.
> There is an action which causes another row to be added.
> However, when I make this change to the database the change is not returned
> in a query immediatly following the update. I have simultaneously run sql
> query analyzer and verified that the table was updated correctly.
> When the form is first displayed and then shortly after I perfomr the update
> the value is recalculated via the following mechanism:
> Using the global connection string a new SqlConnection object is built. Then
> a new SqlCommand object is built using the new SqlConnection. The command
> type is stored procedure. After the SqlCommand is setup (the command text and
> parameters filled in), a new SqlDataAdapter is built using the SqlCommand.
> The SqlDataAdapter fills a new DataSet, via the Fill method.
> When I say that the object is new I mean that literally, as in
> SqlCommand cmd = new SqlCommand (ConnectionString);
> I would very much like to get the correct result right away.
> Thanks,
> Dave
sql

Odd ASP buffer/cache problem?

I am working with code I did not write for a web app that used to run
on an Access database. Due to performance problems I'm trying to move
the app to a different server running SQL Server 2000 SP4, running on
Server 2003 with MDAC 2.8 SP2.
On the new server, one of the queries is failing in an odd way.
Roughly, the query is
select * from a, b where a.1 = b.1 and b.2 = x
This is old fashioned ASP code using ADO.Recordsets. One of the columns
I should be getting from the query is called "Number_Sections".
sql = the query
set cRS = dbCon.exectue( sql )
somevar = cRS("Number_Sections")
cRS("Number_Sections") is returning NULL even though the column in the
database is not null. If I run the query using the SQL Analyer on the
server itself, the column is indeed populated as I expect.
cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
also return NULL.
However, while trying to debug I threw in this code:
For zz = 0 to cRS.Fields.Count-1
response.write cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).value & "<br>"
Next
After running through this loop, which prints out all 47 column names
and values *correctly*, cRS("Number_Sections") suddenly works. Take out
the loop and it stops working again.
This sounds like some kind of buffering problem to me, but I'm not
familiar enough with Microsoft products to know where I should be
looking for some setting to fix the problem. Is it ASP and IIS? Is it
SQL Server? I haven't been able to find any settings that look like
they address this issue and searching the MS Knowledge base hasn't
turned up anything helpful either.
Has anyone else seen this behavior? Can anyone point me in the right
direction to find a more graceful solution that leaving the for loop in
and setting the string to some dummy variable instead of sending it to
the response object?
Thanks.
-SeanStop using SELECT *. Do you really need 47 columns? If this column exists
in both a and b, then either alias it or only include it once. Another
thing you can try is somevar = cRS(n) where n is the 0-based ordinal
position of that column (which will be fun to figure out if you continue to
insist using SELECT *).
I have never seen this issue but without a better example, DDL/sample
data/code and a repro, it's tough to guess at what is happening.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
>I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>|||First, what you are describing sounds like an ASP coding issue, and not a
SQL Server issue. You would have better luck posting on an ASP newsgroup.
However, if you post more of your code, starting with where you define
dbCon(less the actual connection string of course), and ending with closing
your connection, folks will be able to offer more advice.
It sounds to me like you are not properly looping through your dataset, but
I can only guess without seeing the actual code.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
> I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>