Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

NOT isNull

Posted on 2011-09-27
17
Medium Priority
?
432 Views
Last Modified: 2012-05-12
Hello All,

I'm trying to run an IF statement to check to only display the "list price" field if it is populated.

I have the following code:
<% if not isNull ds("listprice") then %>
<p><strong>List Price: </strong>$<%=listprice%></p>
            <% else %>
            <%end if%>

Open in new window


But I get the following error on the page:  

Microsoft VBScript compilation error '800a03f9'

Expected 'Then'

/product_details.asp, line 214

if not isNull ds("listprice") then
--------------^

I want to display the List Price detail if there is a value, otherwise, don't display the Label or the field if it's Null.

Any help?
0
Comment
Question by:DCFC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +2
17 Comments
 
LVL 1

Expert Comment

by:jjthomas3
ID: 36714002
Your if comparison needs to be in parens, try

<% if (not isNull ds("listprice")) then %>

 
<% if (not isNull ds("listprice")) then %>
<p><strong>List Price: </strong>$<%=listprice%></p>
            <% else %>
            <%end if%>

Open in new window

0
 

Author Comment

by:DCFC
ID: 36714014
I still get an error:

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/product_details.asp, line 214

if (not isNull ds("listprice")) then
---------------^

Can I not call the field from the SQL query here?  Earlier I have this:  

listprice	=ds("listprice")

Open in new window


Do I have to use the variable now?

0
 
LVL 15

Expert Comment

by:pateljitu
ID: 36714110
Please use this syntax:
<% if (not isNull(ds("listprice"))) then %>

Also atricle related to isNull function:
http://www.w3schools.com/vbScript/func_isnull.asp

SQL syntax:
select isNull(listprice, 0) as listprice from sometable

In the above SQL statement if the value of listprice is NULL in database, isNull call will replace that value as 0.

Article:
http://www.w3schools.com/sql/sql_isnull.asp
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DCFC
ID: 36714241
OK, I made that change so now my code reads:  
           <% if (not isNull(ds("listprice"))) then %>
            <p><strong>List Price: </strong>$<%=listprice%></p>
           <% else %>
            <%end if%>

Open in new window


but now I get the following error:

Microsoft VBScript runtime error '800a000d'

Type mismatch

/product_details_working.asp, line 214

Any thoughts?
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 36714855
You probably requested a numeric from the database and expecting string.
0
 
LVL 28

Expert Comment

by:sybe
ID: 36716239


<% if not isNull(ds("listprice").Value) then %>

Open in new window

0
 
LVL 15

Accepted Solution

by:
pateljitu earned 1000 total points
ID: 36716906
In your code you need to display listPrice from database and not local variable so try code as provided:

 <% if (not isNull(ds("listprice"))) then %>
            <p><strong>List Price: </strong>$<%=ds("listprice")%></p>
           <% else %>
            <%end if%>
0
 

Author Comment

by:DCFC
ID: 36812651
@sybe

OK, I changed the line to add .Value like you stated but I still get the mismatch error.  

Is there another way I should be doing this?

I simply want to only display the field "listprice" if it is populated in my DB.....

0
 
LVL 28

Expert Comment

by:sybe
ID: 36813981
You should not get a mismatch on an IsNull(something) .

Post more of your code and make clear which line of your code gets the error.
If you error does not change after changing the code, then you might have an error on a totally unrelated line of code.
0
 

Author Comment

by:DCFC
ID: 36814326
Here is my Query:

SQLQuery = " select distinct(pd.productID),pd.sku,pd.base_sku,pd.active,pd.discontinued, pd.listprice, style.tagname as styletagname ,style.styleID as styleID , midgroup.tagname as midgrouptagname , pd.trim, "
SQLQuery = SQLQuery & " grp.tagname as grouptagname , grp.groupID , collect.tagname as collectiontagname , collect.active as active, collect.collectionID , pd.w,pd.h,pd.l,pd.wgt , "
SQLQuery = SQLQuery & "(select name 		from tblcaption where type=7  and languageId = " & langID & "  and captionID = pd.productID) as capproductname , "
SQLQuery = SQLQuery & "(select description  from tblcaption where type=7  and languageId = " & langID & "  and captionID = pd.productID) as capproductdesc , "
SQLQuery = SQLQuery & "(select name 		from tblcaption where type=8  and languageId = " & langID & "  and groupid= " & groupID & " and captionID = style.styleID) as capstylename, "
SQLQuery = SQLQuery & "(select description  from tblcaption where type=8  and languageId = " & langID & "  and groupid= " & groupID & " and captionID = style.styleID) as capstyledesc, "
SQLQuery = SQLQuery & "(select Concat(name,html_code) from tblcaption where type=2  and languageId = " & langID & "  and groupid= " & groupID & " and captionID = pd.collectionID) as capcollection, "
SQLQuery = SQLQuery & "(select name 		from tblcaption where type=4  and languageId = " & langID & "  and captionID = pd.fitId) as capfit , "
SQLQuery = SQLQuery & "(select name 		from tblcaption where type=3  and languageId = " & langID & "  and captionID = pd.finishId) as capfinish ,  "
SQLQuery = SQLQuery & "(select name 		from tblcaption where type=17 and languageId = " & langID & "  and captionID = pd.subcollectionID) as capSubcollection, "
SQLQuery = SQLQuery & "(select name 		from tblcaption where type=1  and languageId = " & langID & "  and captionID = pd.categoryID ) as capcategory, "
SQLQuery = SQLQuery & "(select name 		from tblcaption where type=11 and languageId = " & langID & "  and groupid= " & groupID & " and captionID = midgroup.midgroupID ) as capmidgroup "
SQLQuery = SQLQuery & "from tblproduct pd " 
SQLQuery = SQLQuery & "  left  join  tblproduct_style pdstyle  ON pd.productID = pdstyle.productID "
SQLQuery = SQLQuery & "  left join  tblstyle style            ON style.styleId = pdstyle.styleID "
SQLQuery = SQLQuery & "  inner join  tblproduct_midgroup pdmid ON pd.productID = pdmid.productID "
SQLQuery = SQLQuery & "  inner join  tblmidgroup midgroup      ON pdmid.midgroupID = midgroup.midgroupID "
SQLQuery = SQLQuery & "  inner join  tblgroup grp  			  ON grp.groupID  = midgroup.groupId "
SQLQuery = SQLQuery & "  inner join  tblcollection collect     ON pd.collectionID = collect.collectionID "
SQLQuery = SQLQuery & " and pd.sku='" & productID & "' LIMIT 1"  

set ds = objConn.execute(SQLQuery)

Open in new window


Then here I declare all my variables from the query:

if ds.eof then response.Redirect("/")
istrim=""
colname		=ds("capcollection")
ocolname 	= colname & "&trade;"
ocolname 	= colname 
subcolname = ds("capSubcollection")
prodname	=ds("capproductname")
sku	 		=ds("sku")
prodfin		=ds("capfinish")
proddesc	=ds("capproductdesc")
listprice	=ds("listprice")
w			=ds("w") 
h			=ds("h")
l			=ds("l")
wgt			=ds("wgt") 
base_sku	=ds("base_sku")
collectionID= ds("collectionID")
capname		=ds("capstylename")
capstyle 	= ds("styleID")
istrim		= ds("trim")
active      = ds("active")
discontinued= ds("discontinued")
sRelate=getRelatedProducts(ds("base_sku"))

ds.close
set ds = nothing

Open in new window


Then I have this section with my IF statement:

 <% if not isNull(ds("listprice").Value) then %>
            <p><strong>List Price: </strong>$<%=listprice%></p>
           <% else %>
            <%end if%>

Open in new window


And then when I run the page, I get this error:

Microsoft VBScript runtime error '800a000d'

Type mismatch

/product_details_working.asp, line 214

Line 214 is my IF statement above.

Is there a better way to do this than NOT isNull????
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 36814507
Check SQLQuery by
Response.Write(SQLQuery) and
Response.End()
statements and run it on SQL to check the query built is right. If error occurs before that there is some problem in SQL building and type conversions for groupID, productId and other variables.

Let us know your findings after that.

Regards,
Rikin Shah
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 36814520
Use Cstr(productId) and same way other variables.

Thanks,
Rikin Shah
0
 

Author Comment

by:DCFC
ID: 36814607
I execute the query and it runs fine.

The field 'ListPrice' is a VarChar...it's not an INT.  Does that make a difference?
0
 
LVL 15

Expert Comment

by:pateljitu
ID: 36816210
In your code you are closing the recordset which is this line:
ds.close
set ds = nothing


and later trying to access the same recordset which cannot be performed, so in your code IF statement would look like this as you are already assigning database value that listprice variable:

<% if(not isNull(listprice)) then %>
            <p><strong>List Price: </strong>$<%=listprice%></p>
           <% else %>
            <%end if%>
0
 

Author Comment

by:DCFC
ID: 36892323
OK, I figured it out....MySQL was not returning a NULL value for an empty field.  So that is why it wasn't working.  I did this and now it works fine.

<% if len(listprice) >0 then %>
				<p><strong>List Price: </strong>$<%=listprice%></p>
            <%end if%>

Open in new window

0
 

Author Comment

by:DCFC
ID: 36894354
I've requested that this question be closed as follows:

Accepted answer: 0 points for DCFC's comment http:/Q_27344983.html#36892323
Assisted answer: 83 points for pateljitu's comment http:/Q_27344983.html#36716906
Assisted answer: 84 points for sybe's comment http:/Q_27344983.html#36813981
Assisted answer: 83 points for rikin_shah's comment http:/Q_27344983.html#36814507

for the following reason:

Everyone helped me out.
0
 
LVL 28

Expert Comment

by:sybe
ID: 36894355
pateljitu: gave the right solution, accept that answer please.

> MySQL was not returning a NULL value for an empty field

Probably the contents is an empty string, if you can do a len() on it. That means your field isn't empty (an empty string is different from Null)

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

704 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