[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

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
0
Jason Jones
Asked:
Jason Jones
  • 5
  • 4
  • 3
  • +2
2 Solutions
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now