asked on

Removing VB Script control or hidden characters

I am working with an existing MS SQL database that stores all the data submitted from a form into a single field:
(text output from the database)

Mr.
|¬°
Fred
|¬°
Simpson
|¬°
President
|¬°
SARtech Technologies Ltd
|¬°
39 Pinegrove Crescent
|¬°

As you can see, the from fields are delimited in the database field using: |¬°

I wish to retrieve this information from the database and redisplay it.

Data = Split(RS("submissiondata"), "|¬°")

This was leaving some extra characters in the array elements though that could not be removed using trim. I guessed that there might have been a carriage return in there and so I changed the array to

Data = Split(RS("submissiondata"),"|¬°" &vbcrlf)

This removed 2 of the extra characters but there are still 2 extra characters left. I tried:
Split(RS("submissiondata"), vbcrlf &"|¬°" &vbcrlf) and
Split(RS("submissiondata"), "|¬°" &vbcrlf &vbcrlf)

These both returned only a single array element.

Any idea what other characters could be occupying these two spaces. I verified that there are still two hidden characters by using Len(Data(i)). I would like to get ride of these hidden character so that I can use a conditional statement such as

If Data(i) = "" then ...

I know that I could always use

If Len(Data(i)) <> 2 then ...

but it bugs me not to be able to sort it out.

Frosty555

If you need to figure out "what" those characters are.... use the ASC() function, which, when passed a single character, returns the ascii code of that character.

You'll have to do some tricks to get a substring of th exact character causing you the problem. Probably you'll need to use the Mid() function to extract the offending character

Also , in you string "|¬°".... it probably isn't necessary but I would suggest using CHR(xx) & CHR(yy) & CHR(zz) for whatever those characters actually are in ascii. Again use ASC() to figure that out. It's not a good idea to put non-standard characters like that directly into a hardcoded string in VB. Much less VBScript.

Emad Gawai

first of all, copy one record and past it in text editor..like notepad, or textpad so u will know

cgcmq

ASKER

The characters that I put in my opening post were from notepad. I will try Frosty555's suggestion and let you know. It may take awhile to do.

Are there any other commom VB Script control characters other than vbcrlf that I should be looking for?

Frosty555

Since the characters were "hidden", it could be a null character? Sometimes strings are terminated with a null character to denote when the string ends and it is possible this character was not removed from the data in the form. You could try chr(0) or chr(255) and see if that was it?

ASKER CERTIFIED SOLUTION

Frosty555

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Badotz

Or:

Dim i As Integer
Dim x As String

For i = Len(submissiondata) to 1 Step -1
x = ", " & Asc(Mid(submissiondata, i, 1))
Next
Debug.Print Mid(x, 2)

This will show everything from the end of the line to the beginning, separated by commas.

cgcmq

ASKER

My apologies for leaving this question open for so long.