Solved

Classic ASP and null values assigned to variables

Posted on 2012-03-16
14
4,207 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:dzirkelb
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 37731061
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
 

Author Comment

by:dzirkelb
ID: 37731065
the value in the db is null, not the rs object.  I already checked that.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37731075
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
 

Author Comment

by:dzirkelb
ID: 37731085
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
 
LVL 28

Assisted Solution

by:sammySeltzer
sammySeltzer earned 166 total points
ID: 37731155
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
 
LVL 95

Assisted Solution

by:Lee W, MVP
Lee W, MVP earned 167 total points
ID: 37731182
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
 
LVL 12

Assisted Solution

by:FarWest
FarWest earned 167 total points
ID: 37731617
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dzirkelb
ID: 37742140
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
 

Author Comment

by:dzirkelb
ID: 37742369
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
 

Author Comment

by:dzirkelb
ID: 37743041
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
 

Author Comment

by:dzirkelb
ID: 37744519
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
 
LVL 12

Expert Comment

by:FarWest
ID: 37745055
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
 

Accepted Solution

by:
dzirkelb earned 0 total points
ID: 37747130
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
 

Author Closing Comment

by:dzirkelb
ID: 37770401
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now