[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-10-22
11
Medium Priority
?
4,496 Views
Last Modified: 2008-03-10
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 ?
0
Comment
Question by:fgict
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 12379269
if you don't want the errors you can do something like this

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


hongjun
0
 
LVL 6

Expert Comment

by:sforcier
ID: 12379383
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
 
LVL 13

Expert Comment

by:nurbek
ID: 12379439
check the emailbody variable

Response.Write(emailbody)
it maybe NULL or blank
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 12379652
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
 
LVL 10

Accepted Solution

by:
thefritterfatboy earned 2000 total points
ID: 12379665
if not isnull(emailbody) then
  emailbody=replace(emailbody, "##DESC##", description)
  emailbody=replace(emailbody, "##TERMS##", terms)
else
  emailbody = ""
end if
0
 
LVL 12

Expert Comment

by:ill
ID: 12379931
'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
 
LVL 12

Expert Comment

by:ill
ID: 12379981
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
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 12380698
Or use this SQL

"SELECT * FROM affildesc WHERE affiliateid=" & id & " AND description IS NOT NULL AND terms IS NOT NULL"
0
 
LVL 12

Expert Comment

by:ill
ID: 12380819
@thefritterfatboy
imo, your query is not correct.  you are removing recods from resultset. this fields are not "a must" according to question.
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 12380959
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
 
LVL 13

Expert Comment

by:nurbek
ID: 12381040
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

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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