NOT isNull

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?
DCFCAsked:
Who is Participating?
 
pateljituCommented:
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
 
jjthomas3Commented:
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
 
DCFCAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
pateljituCommented:
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
 
DCFCAuthor Commented:
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
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
You probably requested a numeric from the database and expecting string.
0
 
sybeCommented:


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

Open in new window

0
 
DCFCAuthor Commented:
@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
 
sybeCommented:
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
 
DCFCAuthor Commented:
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
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
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
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Use Cstr(productId) and same way other variables.

Thanks,
Rikin Shah
0
 
DCFCAuthor Commented:
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
 
pateljituCommented:
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
 
DCFCAuthor Commented:
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
 
DCFCAuthor Commented:
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
 
sybeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.