Link to home
Start Free TrialLog in
Avatar of foreverdita
foreverdita

asked on

MS SQL data - strange character but no show in the actual table when submitting a form

I have a form which submits data to an MSSQL table.  This table then submits to our backend system.

There is a problem only at certain times with the data submitting to the backend.  In MSSQL, it shows nothing.  When I copy the data over to textpad or wordpad to view the records, I see something very strange.

First of all, we do a check and take out all weird characters before we submit to MSSQL - including !@#$%^&*()_+=-,./<>?;':"[]\{}| etc.

Then we enter the form into mssql table.

In the table, all looks well.

When I extract the files to a text document, this is what I see for the normal submissions:

BILL          1 address       city   state    zip

**********
for the ones that come across and cause errors in our backend system, this is what I see in text editor:

BILL         "1 Address
"               city           state            zip

Does anyone know what causes this and what I can do to eliminate the problem?

Avatar of dstanley9
dstanley9

Check for carriage returns (CHR(13)) and line feeds (CHR(10)) in your text.  Some text editors will show a single CR or LF, some will only show if they are together (CRLF) - this is Windows style.
In Query Analyzer select the column from the table and check the accii code for that character
ascii('a)' and try to replace this ascii with empty string

But obviously it is coming from one of the client machine so you have to identify that client machine and then  check for regional setting for that client machine.
Avatar of foreverdita

ASKER

How would I check for carriage returns by sleecting * from table where Ad1 has a carriage return?  In query Analyzer I mean?
select * from address where ad1 has a carraige return (how do I check for carriage return here)

 
In your weird-character check, you should also remove anything below CHAR(32) as these are all non-printable control characters. Possibly everything above CHAR(126) as well. This should render everything neatly viewable.
SOLUTION
Avatar of kenpem
kenpem
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Anthony Perkins
>>Possibly everything above CHAR(126) as well. <<
Ouch!  You have just knocked out Latin America and most of Europe.
>>Does anyone know what causes this and what I can do to eliminate the problem?<<
It means you have a CrLf or Cr at the end of the Address column.  This is easy to confirm:
1. Open the table in Enterprise Manager
2. Select the row that is causing the problem.
3. Go into Edit mode (F2) and the value should disappear (it is actually there, you are just on the second line).  On other cells when pressing F2 you will see the value highlighted and you will remain on the same line.
OK, then upon a form submittal, how would I get rid of the CHAR(13) before submitting the contents of the form?

theaddress = replace(ad1, ""+Char(13)+"", "")

Or something like that?

Also, is there a way to replace existing records in the table that have char13 without erasing the entire ad1?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial