NULL and If...Then...Else...End If statement 1

[hkarre]

Hello Everyone,

I am having a little problem with the following code and was wondering if anyone could help me out:

If IsNull(oRS.Fields("content")) Then
    varContent = " "
Else
    varContent = oRS.Fields("content")
End If

When testing the output of varContent with IsNull function they all test out as "True" which definitely should not be the case. What am I not understanding about NULL values in vbscript? Thanks in advance for any help

 

[EBGreen]

Are you sure that the field really has null values? Null is not the same thing as an empty string ("").

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hkarre]

Actually, at this point I am not sure about anything. When I was inserting values into my Access 2000 database tables, I was using a function that would convert empty fields into a string literal "NULL" before insertion into the database. I had found the vbscript function on the net and it seemed like a smart thing to do at the time. I have also tried testing for the string literal "NULL" but it does not work either.

 

[EBGreen]

I would try writing some real quick code to just display the contents of the field. Then you can decide what the If Then needs to test for.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hkarre]

Basically what I am after with this code is the ability to "filter" out values that are "NULL" so that they don't display on the resulting web page. If the variable oRS.Fields("content") is not "NULL" then place it in the variable named varContent for writing to the web page, if oRS.Fields("content") is "NULL" then place a space or something so that I don't get the word NULL displayed with my web content.

 

[hkarre]

OK. I just inserted a simple Response.Write for the oRS.Fields("content") variable and when I run my web page I get pretty much what I expect:

Where there is content to display it displays the content. Where there is not content to display it displays the word NULL.

If it prints out NULL when the script runs, does that mean it is really NULL or does that just mean it is a string literal (i.e. the word NULL) that was designed to confuse me?

 

[PHV]

By the way, your whole If ... End If instruction is server side, as the varContent definition ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hkarre]

Yes, both varContent and oRS.Fields("content") exist server side. Neither one is client side.

 

[hkarre]

OK, this is weird...

I just inserted the following code:

If oRS.Fields("content") = "NULL" Then
    varContent = " "
Else
    varContent = oRS.Fields("content")
End If

And what happens is if oRS.Fields = "NULL" then it does set varContent as a space (i.e. " "). If oRS.Fields <> "NULL" then varContent is empty for some reason. I do not understand this at all.

 

[EBGreen]

Can you post the code along with the portion where you write the contents of varContent?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hkarre]

Yes, here is the code that I am using...don't laugh...beginner here and all that

<%
Sub ShowListing(varSubCategory)

  dim conn
  dim strconn
  dim photoconn
  dim strphotoconn
  
  strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & _
        Server.MapPath("cms/betco/betco.mdb") 'change the path as necessary

  set conn = server.createobject("adodb.connection")
  conn.open strconn

  SQL = "SELECT * FROM content WHERE subid = " & varSubCategory & ""
          
  set oRs = conn.Execute(SQL)

  If oRs.EOF then
    Response.Write "This category is empty"
  Else
    oRS.MoveFirst
		
		Do While Not oRS.EOF
		
				strphotoconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & _
        		Server.MapPath("cms/betco/betco.mdb") 'change the path as necessary
        
        	set photoconn = server.createobject("adodb.connection")
        	photoconn.open strphotoconn
        
        	photoSQL = "SELECT * FROM photos WHERE contentid = " & oRS.Fields("contentid") & ""
        
        	set photoRS = photoconn.Execute(photoSQL)
        
        	If photoRS.EOF then
        		Response.Write "There is no photo for this category"
        	Else
        		photoRS.MoveFirst
                
        			Do While Not photoRS.EOF
       			
        			If oRS.Fields("content") = "NULL" Then
        				varContent = " "
        			Else
        				varContent = oRS.Fields("content")
        			End If
%>
<div>
<div><A HREF="javascript:PopupPic('cms/betco/images/thumbs/large_<% =photoRS.Fields("photoname") %>')">
<img title="<% =varContent %>" src="cms/betco/images/thumbs/small_<% =photoRS.Fields("photoname") %>" border="0" style="border: 1px solid #000000;">
</a></div>
<div><strong><% =photoRS.Fields("caption") %></strong></div>
</div>
<br />
<%
					photoRS.MoveNext
					Loop
			End If
		
		set photoconn = Nothing
		set photoRS = Nothing
		
		' Get next record
		oRS.MoveNext
		Loop
  End If

  Set conn = Nothing
  Set oRs = Nothing
  
End Sub

Sub ShowNews(varNumItems)

	dim newsConn
  	dim newsstrConn
  	
  	newsstrConn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & _
        Server.MapPath("cms/betco/betco.mdb") 'change the path as necessary

  	set newsConn = server.createobject("adodb.connection")
  	newsConn.open newsstrConn
 	
  	if isNumeric(varNumItems) then
  		varNumItems = CInt(varNumItems)
  		newsSQL = "SELECT TOP " & varNumItems & " * FROM press ORDER BY id DESC"
  	else
  		varNumItems = CStr(varNumItems)
  		if varNumItems = "ALL" then
  			newsSQL = "SELECT * FROM press"
  		End If
  	End If	
  	
  	set newsRS = newsConn.Execute(newsSQL)
  	
  	If newsRS.EOF then
    	Response.Write "There are no news items to display"
  	Else
  		Response.Write "<p style=""font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 14px; font-weight: bold; color: #000000;"">"
  		Response.Write "&nbsp;News Releases</p>"
  		newsRS.MoveFirst
    	Do while not newsRS.EOF
    	
    		%>
    		<p style="margin-left: 5px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; font-weight: bold;"><a href="news.asp?id=<% =newsRS.Fields("id") %>"><% =newsRS.Fields("headline") %></a></p>
    		<%
    		    		
    	newsRS.MoveNext
		Loop
    End If
  	
End Sub
%>

 

[PHV]

And what about using photoRS instead of oRS ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hkarre]

Actually, oRS is part of the first recordset that is looped through and photoRS is a second recordset that is looped through while still inside of the oRS recordset loop. The problem is that I can do a Response.Write of all fields and have them print to the screen but I am having trouble with the fact that some of the items are "NULL" and I don't want that to print to the screen, which is what is happening

 

[EBGreen]

When you open betco.mdb and look at the Content field of the Content table (not a very good naming convention by the way), then do you see any 'NULL' string literals?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hkarre]

Yes, there are string literal 'NULL' 's in the database, is this a bad thing?

 

[EBGreen]

Good or bad I could not say. Here is the next thing that I would do.

Make sure that the values in the table are really 'NULL' not ' NULL' or 'NULL '.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hkarre]

It appears that the values in the table are just NULL with no quotes around them or spaces before or after them. I'm not trimming any of the output so if there was a space somewhere it should show up right?

 

[EBGreen]

What if you change this:

If oRS.Fields("content") = "NULL" Then
to
If CStr(oRS.Fields("content")) = "NULL" Then


[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hkarre]

Actually - I just changed the following code from this

If oRS.Fields("content") = "NULL" Then
  varContent = " "
Else
  varContent = oRS.Fields("content")
End If

to this

haystack = oRS.Fields("content")
        			
result = InStr(haystack, "NULL")
        			
If result = 0 Then
  varContent = haystack
Else
  varContent = " "
End If

and it worked! You were probably right in a previous post stating that the ouput contained extra spaces or characters. Thanks for helping me think it through and get on the right track. I thought I was going crazy. I still need to do some research to find out why it happened, maybe the database was adding non-visible control characters to the string or something, anyway thanks for all the help and sticking with me.