Replace carriage return with < BR > in a MS SQL Database using ASP

I am running an ASP page to enter data into an MS SQL database into a text field (NVARCHAR) when the user enters a carriage return in the database it records it as two little squares. what I need to do is make a replace(FieldName, "TheSquare", "<Br>") so it will render the return as HTML however I can't seem to find what the "TheSquare" needs to be in order to replace it.  

I have tried REPLACE(FieldName, vbCrLf, "< br> ")
I have tried  REPLACE(REPLACE(FieldName, CHR(10), " < br > "), CHR(13), " < br > ")

*Note: I had to add spaces in the code example above becuse Experst Exchange Javescript failed when I tried to post the question

with no luck
LVL 1
Jason JonesGIS/APP DevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mgfranzCommented:
Try this;

(Replace(FieldName, vbCr, "<br>"))
0
sirbountyCommented:
Hmm - what about simply:
  Replace(FieldName, chr(13), "<br>")

Is that still leaving the square symbol?
0
i2mentalCommented:
Try REPLACE(Fieldname, char(13)+char(10), '<br>')
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jason JonesGIS/APP DevAuthor Commented:
Ok I tried all three and none work...  REPLACE(Fieldname, char(13)+char(10), '<br>') actually crashed the whole application.  the other two did nothing

Thanks
0
flipzCommented:
Try
replace(fieldname, vbcrlf,"</br>")
0
flipzCommented:
Sorry - didn't see you already tried it - works for varchar columns though.
0
sirbountyCommented:
What about we try to find out what's there?
You sure it's a carriage return?
Try this in your code:

response.write Instr(FieldName, chr(13))
response.write Instr(FieldName, chr(10))

Do either return any numbers?
0
Jason JonesGIS/APP DevAuthor Commented:
Both return 0
0
sirbountyCommented:
So there's no carriage return or line break in there...
Hmm...sure that the user is hitting /enter/ ?

What about

response.write Instr(FieldName, vbTab)
0
Jason JonesGIS/APP DevAuthor Commented:
It also says 0  

I know there is a return in there because I am the one entering it. The application is still under development.  If I look at the actual Data using SQL enterprise manager I can see the little squares (there are actually 2 of them side by side for any carriage returns I enter)   Here is an exmaple of soneone else with a similar problem with Excel but the same thing is happening to me with SQL
http://www.gossamer-threads.com/forum/General_C8/Databases_and_SQL_F36/Delete_Carriage_return_query_P259718/
0
sirbountyCommented:
Hmm - then what about we try to split the data by that character?

newData=Split(FieldName, chr(13))

Response.Write lbound(newData)
Response.Write ubound(newData)

Do either of those return anything?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flipzCommented:
Can you post example output directly from the database without any replacement? Is there any characters that get displayed?
0
Jason JonesGIS/APP DevAuthor Commented:
I actually just found the problem...  REPLACE(TheField, vbCrLf, "<br>")  DOES work.  Apparently I had declared two variables using that field and the first one was the one I kept changing and testing however what I was seeing on the website was actually second variable which did not have the replace function.  I'll split the points since you both have been helpful
0
sirbountyCommented:
Glad you got it sorted! :^)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.