Classic ASP and null values assigned to variables

I have encountered a strange issue that has just started in the past couple days.  Up until recently, I would sometimes assign a value to a variable from a query.  If the value of the query results was null in teh table itself, then my .asp pages treated that variable the same as if it was a blank value.  So, for example, here is what I'm using to test:

<%
set rs = Server.CreateObject ("adodb.Recordset")
ssql = "SELECT Field FROM Table WHERE (ID = 123)"
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly

if not rs.eof then
      strField = rs("Field")
end if

rs.close
set rs=nothing

if IsNull(strField) then      
      response.write("test1")
end if

if strField = "" then
                  response.write("test2")
end if

dbc.close
set dbc=nothing
%>

In the table itself, if I make the value for the field null, then the page prints out "test1".  If I make the value blank, then it spits out "test2".

before recently, if the value was null, it would spit out "test2", and I don't know if it would spit out "test1" or not because I have never had to code for it.

Has anyone else experienced this change?  The web server is 2008 r2, and the sql database is sql-2000 on server 2000.  I have change nothing in terms of code or connection properties or table structures.  The last updates were installed on the 4th of march that I can see.  This could have been happening since that time, but I doubt it.

The one change I have done recently that I know of is I added a new web site on the web server.  The new web site on the server points to the same physical location as another web site, but it has no authentication checked.  Is it possible the newly created site with no authentication is messing up with the existing site with authentication?  meaning, both sites point to d:\inetpub\xyz, one site has Windows Authentication turned on, another has no authentication and allows anon.  I can't remove it at this time as it is being used, but I can remove it on Monday.

The servers, all of them, will reboot over the weekend, so I'll wait to see if that fixes it also.

Anyone else experienced this recently?
dzirkelbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FarWestCommented:
before go with any assumptions; you have to determine why strField  is null, is it because the original value in db is null, or because the rs object is null, ... etc

you should do furtehr debugging,

write debug message in you code
0
dzirkelbAuthor Commented:
the value in the db is null, not the rs object.  I already checked that.
0
sammySeltzerCommented:
Is there really any reason to spit out test1 and test2?

Why not just:

If isnull(strField) or StrField = '' then
   do something
else
   do something else
end if
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dzirkelbAuthor Commented:
There is no reason and that is not my question.

My question is what has changed, or if anyone has had the same issue.

Of course that is the correct result going forward, but I have over 1000 pages that are now affected by this, and I am looking to a solution that does not involve changing 1000 pages code, let alone however many times within each page I need to change the code.

Bottom line is this used to work:

if strField = "" then
do something
end if

when strField was null, and now it doesn't.
0
sammySeltzerCommented:
Well, I have not had that issue but unfortunately, you will have to protect against extra space.

To do so, you will to trim it:

if trim(strField) = "" then
   do something
end if

and I don't think you will be getting unpredictable results.
0
Lee W, MVPTechnology and Business Process AdvisorCommented:
My best guess is a patch the fixed something in IIS/ASP or SQL.  Or something changed in the data.  You could avoid fixing the code by running an update:

UPDATE TableName SET Field = '' WHERE Field IS NULL

(I would suggest some testing first before actually updating all your fields with that UPDATE statement.
0
FarWestCommented:
what is the database engine you are using?
and did you conduct any database upgrade, (or maybe the other application),
please note it is maybe realted to ANSI NULLS configuration in the database server
0
dzirkelbAuthor Commented:
I generally do a trim of the variable, but again, that is not the issue.  The issue is null values in the database, not blank values or values with just a space.

Updating everything to a blank space is not feesible as with the over 1000 pages, there are probably over 100 tables, with at least 20 text fields in each one.

The DB engine is sql -2000 running on 2003 server std.

I have not performed any upgrades minus windows updates, which I am not seeing anything really changing there, but wouldn't doubt if that was the case.  I do not know enough about sql server to know how to change ANSI NULLS, so I definately didn't do the change, but am interested in knowing where to look to see if that could resolve the problem.
0
dzirkelbAuthor Commented:
I have tested this issue and it is present on the following connection string:

Set dbc = Server.CreateObject("ADODB.Connection")
dbc.Open "Provider=SQLOLEDB; Data Source = SQLServer; Initial Catalog = Catalog; User Id = abc; Password=xyz"

strConn = "dsn=AccessDSN;uid=;pwd="
Set dbc = Server.CreateObject("ADODB.Connection")
-This one has linked tables from the access database to the sql database

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:/aaa/DatabaseName.accdb;Persist Security Info=False;"
Set dbc = Server.CreateObject("ADODB.Connection")
-This one has linked tables from the access database to the sql database

strConn="Driver={SQL Server};Server=111.222.333.444;Database=DatabaseName;Uid=xyz;Pwd=abc;"
Set dbc = Server.CreateObject("ADODB.Connection")

I don't know if that helps or not, but that is what has been tested.
0
dzirkelbAuthor Commented:
I have also confirmed this to be the case when dealing with a null value in an access table, at least a 2007 access table.  So, this leads me to believe it is an IIS issue, and not sql server specific.

I have uninstalled all updates pertaining to office 2010 as they were a bunch installed on 3/16, but that requires a reboot, so we will see how that works overnight.  I am not confident about that change, however.

I am thinking it has something to do with the web site I added as it is just too much of a coincidence it started around the same time.  Maybe a permissions deal?

Does IIS do any sort of system backups for its settings?  I could try to restore that from early March, but I am not aware of anything like that.
0
dzirkelbAuthor Commented:
More testing.

I restored my entire web server to a virtual vm.  The date of the backup was 2/24, so should be well before this problem started.

Results are the same, still have to use IsNull opposed to = "".

I know this is new because I have never coded using IsNull in around 10 years.

I'm at a loss now as I fully expected the restore of the server to shed some light, but it just the same deal.
0
FarWestCommented:
sorry I was away for sometime,
check this for ANSI Null settings
http://msdn.microsoft.com/en-us/library/aa933268(v=sql.80).aspx

I hope it will work,
please note that in sqlserver setting instance compatability level can do that effect,
(ANSI NULL beheavior change)
0
dzirkelbAuthor Commented:
I changed ANSI Nulls from off to on, still doesn't work, then from on to off, and still doesnt' work.

I have confirmed the issue to be present when Access is the back end database also, an Access 2007 table.  So, I am at a real loss now.  I suspect I will have to change all of my code.  Which, in the long run is better anyways I spose.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dzirkelbAuthor Commented:
I have gone in and adjusted the code for all of my pages.  Surprisingly, there was only 100 or so to change, so it didn't take all that long.  Thanks guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.