DCFC
asked on
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:
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?
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%>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?
ASKER
I still get an error:
Can I not call the field from the SQL query here? Earlier I have this:
Do I have to use the variable now?
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")Do I have to use the variable now?
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
<% 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
ASKER
OK, I made that change so now my code reads:
but now I get the following error:
Any thoughts?
<% if (not isNull(ds("listprice"))) then %>
<p><strong>List Price: </strong>$<%=listprice%></p>
<% else %>
<%end if%>but now I get the following error:
Microsoft VBScript runtime error '800a000d'
Type mismatch
/product_details_working.asp, line 214
Any thoughts?
You probably requested a numeric from the database and expecting string.
<% if not isNull(ds("listprice").Value) then %>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.....
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.....
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.
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.
ASKER
Here is my Query:
Then here I declare all my variables from the query:
Then I have this section with my IF statement:
And then when I run the page, I get this error:
Line 214 is my IF statement above.
Is there a better way to do this than NOT isNull????
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)Then here I declare all my variables from the query:
if ds.eof then response.Redirect("/")
istrim=""
colname =ds("capcollection")
ocolname = colname & "™"
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 = nothingThen 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%>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????
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
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
Use Cstr(productId) and same way other variables.
Thanks,
Rikin Shah
Thanks,
Rikin Shah
ASKER
I execute the query and it runs fine.
The field 'ListPrice' is a VarChar...it's not an INT. Does that make a difference?
The field 'ListPrice' is a VarChar...it's not an INT. Does that make a difference?
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%></
<% else %>
<%end if%>
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%></
<% else %>
<%end if%>
ASKER
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%>ASKER
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.
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.
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)
> 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)
<% if (not isNull ds("listprice")) then %>
Open in new window