Solved

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

Posted on 2004-10-22
4,179 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
Question by:fgict
    11 Comments
     
    LVL 33

    Expert Comment

    by:hongjun
    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
    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
    check the emailbody variable

    Response.Write(emailbody)
    it maybe NULL or blank
    0
     
    LVL 10

    Expert Comment

    by:thefritterfatboy
    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:
    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
    '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
    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
    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
    @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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Shellfire Box VPN + Lifetime Subscription

    The Shellfire Box easily connects all of your devices, even those that don't offer the possibility to establish a safe vpn connection. Access blocked content and surf safely, no matter where in the world you are located.

    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…
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    933 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now