Nulls and the Replace Function in ASP/VBScript

Results 1 to 4 of 4

Thread: Nulls and the Replace Function in ASP/VBScript

  1. #1
    skully Guest

    Default Nulls and the Replace Function in ASP/VBScript

    I am trying to create a script where I read data from a legacy database and insert the data into a new database. Some of the fields contain special characters like &#039 that I want to replace. I am using the Replace function, but when I encounter a Null (varchar) field, I get an error. It&#039s my understanding that Replace will return an error when it encounters a Null value. So I tried to set up an If statement to check for a Null value and skip if the field is Null, or run the Replace function if the field isn&#039t Null. Unfortunately, my If Then statement is not working. Does anyone have any suggestions how I can check for special characters in fields that may or may not be Null?<BR><BR><BR>Here is my statement:<BR><BR> if DataSetsource = "" Then<BR> DataSetsource = ""<BR> Else<BR> DataSetsource = replace(trim(objRecordSet.Fields("Data_Source")), "&#039", "`")<BR> End if

  2. #2
    Steve Cimino Guest

    Default "" <> NULL

    Do this:<BR><BR>If IsNull(DataSource) Then<BR> DataSource = ""<BR>Else<BR> DataSetsource = replace(trim(objRecordSet.Field("Data_Source")), "&#039", "`")<BR>End if<BR><BR><BR><BR>Remember, "" and NULL are not the same values.

  3. #3
    Skully Guest

    Default RE:

    I tried it, but I get this error that points to my SQL insert execute statement. Any suggestions as to what I may be doing wrong?<BR><BR>Microsoft VBScript runtime error &#039 800a005e&#039 <BR><BR>Invalid use of Null: &#039replace&#039 <BR><BR>

  4. #4
    Join Date
    Dec 1969

    Default RE: A hacky trick...

    ...but it works:<BR><BR>DataSetsource = Replace( Trim( " " & objRecordSet("Data_Source") ), "&#039", "`") <BR><BR>See that? I *first* pre-pend a space character to whatever is in the RS field. If the field is NULL, it doesn&#039t matter! The result is then still just a space. The Trim then removes that space (and any others fore or aft). Then the Replace does its work; it may be give just "" to work with, but that is *not* NULL and won&#039t cause an error.<BR><BR>No IF ... THEN ...ENDIF needed, at all!<BR><BR>(Actually, you can use "" instead of " " prepended to the field value, but I think " " makes it clearer.)<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts