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.

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.

Are there no "Computed" column types? For storing expressions (results of other fields

I just read something interesting here: http://www.informit.com/library/content.asp?b=STY_Sql_Server_7&seqNum=101

A column type that helds an expression, and in queries returns the results.

That sounds excelent in my database, to save some code in my client applications. E.g adding price totals, and taxes of an order.

I can't find any info about this in later versions of SQL server. Is this not possible anymore?

not really necessary. . . create a view.

create table LineItem(itemId, price, quantity)

create view LineItemWTax as select itemid, price, quantity, price*quantity*0.045 as Tax

select * from LineItemWTax

you can also create a function:

create function CalculateTax(@.price money, @.quantity int, @.rate as decimal (3,3))
returns money
as
begin
return @.price * @.quantity * @.rate
end
go
select itemid, price, quantity, dbo.CalculateTax(price, quantity, .045) from LineItem

|||Thanks, thats probably a better way i guess :)

Are SOX Auditors focused on the wrong things?

The Security Catalyst had an interesting post last week, The
Psychology of Fraud -Revisited.
In the post, Sarbanes-Oxley auditors are criticized for focusing on
minutia that is costing corporations millions, while the real cause of
fraud is corrupted humans.
In developing this argument, the author draws upon a model developed
in the early 1950's by Dr. Donald Cressey, a criminologist whose
research focused on embezzlers, called the fraud triangle. Where Dr.
Cressey's triangle consisted of pressure, rationalization, and
opportunity, the Security Catalyst altered the points of triangle for
IT fraud to include the following definitions:
- Access. Physical or logical ability to enter, touch, or reach a
resource. In computers, this is often controlled by network rules and
a user id and password.
- Knowledge. To be familiar or have experience with an object or
resource. This means having the concepts and ability on what to do
after you have accessed the resource.
- Intent. The purpose or an anticipated outcome that guides a person's
planned actions. Knowingly causing damage to the resource.
These make a great deal of sense, but the rest of the argument is lost
on me. The author tries to make the case that access rights should not
be scrutinized to the degree they are currently in SOX audits because
it is only a portion of the fraud triangle. Improper access doesn't
necessarily mean that someone has knowledge or intent.
The post even states "You can't audit against knowledge and intent."
Well, if you can't audit knowledge and intent, don't you have to audit
access? In the absence of a way to detect someone with the knowledge
and intent to perpetrate fraud, don't you have to ensure access is
being provided on an as needed basis?
In my opinion, the author's own statements actually justify why
auditors give access rights the demanding scrutiny they do today. If
access rights are poorly documented and managed, the odds are that
much greater that a person who already has the knowledge and intent
will get the golden key that completes their fraud triangle.
SOX does not prevent or reduce corporate fraud. Its requirements exist so
that a prosecuting attorney can presumptively use a corporation's records as
legal evidence against the corporation's management. Viewed in that light,
the requirements make perfect sense.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<jmichaud@.ecora.com> wrote in message
news:1185212726.689666.6170@.n2g2000hse.googlegroup s.com...
> The Security Catalyst had an interesting post last week, The
> Psychology of Fraud -Revisited.
> In the post, Sarbanes-Oxley auditors are criticized for focusing on
> minutia that is costing corporations millions, while the real cause of
> fraud is corrupted humans.
> In developing this argument, the author draws upon a model developed
> in the early 1950's by Dr. Donald Cressey, a criminologist whose
> research focused on embezzlers, called the fraud triangle. Where Dr.
> Cressey's triangle consisted of pressure, rationalization, and
> opportunity, the Security Catalyst altered the points of triangle for
> IT fraud to include the following definitions:
> - Access. Physical or logical ability to enter, touch, or reach a
> resource. In computers, this is often controlled by network rules and
> a user id and password.
> - Knowledge. To be familiar or have experience with an object or
> resource. This means having the concepts and ability on what to do
> after you have accessed the resource.
> - Intent. The purpose or an anticipated outcome that guides a person's
> planned actions. Knowingly causing damage to the resource.
> These make a great deal of sense, but the rest of the argument is lost
> on me. The author tries to make the case that access rights should not
> be scrutinized to the degree they are currently in SOX audits because
> it is only a portion of the fraud triangle. Improper access doesn't
> necessarily mean that someone has knowledge or intent.
> The post even states "You can't audit against knowledge and intent."
> Well, if you can't audit knowledge and intent, don't you have to audit
> access? In the absence of a way to detect someone with the knowledge
> and intent to perpetrate fraud, don't you have to ensure access is
> being provided on an as needed basis?
> In my opinion, the author's own statements actually justify why
> auditors give access rights the demanding scrutiny they do today. If
> access rights are poorly documented and managed, the odds are that
> much greater that a person who already has the knowledge and intent
> will get the golden key that completes their fraud triangle.
>