Microsoft VBScript runtime error '800a005e' - Invalid use of Null: 'replace'

Hi All,

Getting the following error:

Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'replace'
/web/checkout5.asp, line 241

On this page, i'm opening a recordset....

set rs=openrs("SELECT * FROM affildesc WHERE affiliateid=" & id)
description=rs("description")
terms=rs("terms")
closers()

then sending an email with the values....

  if confemail="ON" and emailcomp<>"None" then
  emailto=email
  emailtoname=name
  emailfrom=email_2
  emailfromname=clerkname
  emailsubject=emailsubject1
  emailbody=emailbody1
  emailbody=replace(emailbody, "##DESC##", description)
  emailbody=replace(emailbody, "##TERMS##", terms)
  call sendemail()
  end if

Error on Line 241 - emailbody=replace(emailbody, "##TERMS##", terms)

Field terms is NULL when i look at it in SQL Server,
1. how do i write the code here to get it to work
2. Have i designed my database (SQL Server) incorrect by allowing NULLs ?
LVL 1
fgictAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hongjunCommented:
if you don't want the errors you can do something like this

emailbody=replace(emailbody, "##TERMS##", "" & terms)


hongjun
0
sforcierCommented:
To answer your second question, based on what this snippet seems to be doing, you probably want to have SQL Server not allow null values. If you have a design requirement (for the application) that each affiliate *must* have their own terms, then you should not allow nulls in the database.

If one or more affiliates may have blank terms then you could consider allowing nulls, but even in this case, I wouldn't do it. In the case where blank terms are allowed I would disallow null and set the default value to an empty string. As you can tell, processing an empty string in your ASP will raise fewer issues when it comes to processing strings and it will saves you a few steps over having to check for a null value in certain situations.

Hope this helps,
Sid
0
nurbekCommented:
check the emailbody variable

Response.Write(emailbody)
it maybe NULL or blank
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

thefritterfatboyCommented:
As nurbek says - this error is from a variable being Null.

Check the variables for nulls before calling replace by using ISNULL(variable)

Do not use hongjun's idea. This will append a null to a string making it a double null-terminated string. Appending anything at a later part of your script will fail as VBScript will terminate the string at the null.
0
thefritterfatboyCommented:
if not isnull(emailbody) then
  emailbody=replace(emailbody, "##DESC##", description)
  emailbody=replace(emailbody, "##TERMS##", terms)
else
  emailbody = ""
end if
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
illCommented:
'instead of
set rs=openrs("SELECT * FROM affildesc WHERE affiliateid=" & id)
description=rs("description")
terms=rs("terms")

' try this. the rest of code stays.
set rs=openrs("SELECT * FROM affildesc WHERE affiliateid=" & id)
description=""
terms=""
if not isnull( rs("description") ) then
  description=rs("description")
end if
if not isnull( rs("terms") ) then
  terms=rs("terms")
end if
0
illCommented:
in generall it's better to get exact result directly from database than check it in VBScript. so if you need only these two fields and you're running mssql execute this query. and do not change anything else in asp.
-----------
set rs=openrs("SELECT ""description""=isnull(description,''),""terms""=isnull(terms,'') FROM affildesc WHERE affiliateid=" & id)
0
thefritterfatboyCommented:
Or use this SQL

"SELECT * FROM affildesc WHERE affiliateid=" & id & " AND description IS NOT NULL AND terms IS NOT NULL"
0
illCommented:
@thefritterfatboy
imo, your query is not correct.  you are removing recods from resultset. this fields are not "a must" according to question.
0
thefritterfatboyCommented:
I see no reason to send an email with no information, but that's my opinion.

Won't your SQL return "undeclared variable" errors unless you use

set rs=openrs("SELECT isnull(description,'') AS description, isnull(terms,'') AS terms FROM affildesc WHERE affiliateid=" & id)

??
0
nurbekCommented:
Error was

Error on Line 241 - emailbody=replace(emailbody, "##TERMS##", terms)

and solution is emailbody is NULL

and the fgict (the author)
should check emailbody values whether is NULL or not

as

thefritterfatboy wrote how to check and replace

:)

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.