Solved

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

Posted on 2006-06-26
10
685 Views
Last Modified: 2008-07-02
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?

0
Comment
Question by:foreverdita
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 25

Expert Comment

by:dstanley9
ID: 16984734
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.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16984762
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.
0
 

Author Comment

by:foreverdita
ID: 16984901
How would I check for carriage returns by sleecting * from table where Ad1 has a carriage return?  In query Analyzer I mean?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:foreverdita
ID: 16984910
select * from address where ad1 has a carraige return (how do I check for carriage return here)

 
0
 
LVL 4

Expert Comment

by:kenpem
ID: 16984918
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.
0
 
LVL 4

Assisted Solution

by:kenpem
kenpem earned 100 total points
ID: 16984940
SELECT * FROM address WHERE ad1 LIKE ('%'+CHAR(13)+'%')

will show you which records contain a carriage return. Also check for CHAR(10) for line-feeds.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16985968
>>Possibly everything above CHAR(126) as well. <<
Ouch!  You have just knocked out Latin America and most of Europe.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16986000
>>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.
0
 

Author Comment

by:foreverdita
ID: 16986672
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?
0
 
LVL 25

Accepted Solution

by:
dstanley9 earned 400 total points
ID: 16986822
To fix existing records:

UPDATE address
SET ad1 = REPLACE(ad1,CHAR(13),'')

To fix on the form:

theaddress = ad1.Replace( "\n", "");  // CHR(10)
or
theaddress = ad1.Replace( "\r", "");   // CHR(13)
or
theaddress = ad1.Replace( "\r\n", "");  //CHR(13)+CHR(10)

depending on which character(s) is causing the problem.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2014 STD not using available memory 5 49
SQL Query Task 11 42
how to make geography query faster?  SQL 7 39
SQL Instance service gone? 5 31
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 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