Solved

NOT isNull

Posted on 2011-09-27
17
419 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
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 

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 250 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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…
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 shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 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