Solved

NOT isNull

Posted on 2011-09-27
17
414 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
  • 7
  • 3
  • 3
  • +2
17 Comments
 
LVL 1

Expert Comment

by:jjthomas3
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:DCFC
Comment Utility
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
Comment Utility
You probably requested a numeric from the database and expecting string.
0
 
LVL 28

Expert Comment

by:sybe
Comment Utility


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

Open in new window

0
 
LVL 15

Accepted Solution

by:
pateljitu earned 250 total points
Comment Utility
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
Comment Utility
@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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 28

Expert Comment

by:sybe
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Use Cstr(productId) and same way other variables.

Thanks,
Rikin Shah
0
 

Author Comment

by:DCFC
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

11 Experts available now in Live!

Get 1:1 Help Now