Solved

S/B EZ:  Losing Carriage Returns and Line Feeds in SQL Server

Posted on 2007-04-07
6
722 Views
Last Modified: 2007-12-19
Hopefully this isn't too difficult, but after searching I haven't really found a solution.

My VB.NET Windows App stores information that is input into a rich text box (a text box would be fine too) into a field of an SQL Server database.  The important thing is KEEPING carriage returns and line feeds.  When I pull the data back out, Carriage Returns and Line Feeds are lost.  E.g.

Hello

How Are you

Have you been alright.

Comes back as:

HelloHow Are youHave you been alright.

So I need to preserve the CR's and LF's.  I've read articles where they suggest replacing the CR's and LF's with the <br /> tag, but this isn't a web app, and doesn't appear to work for an Windows application.

I tried configuring the SQL field as varchar(), nvarchar(), and ntext, but there's no difference in the result.

Any help?
0
Comment
Question by:kmoloney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 167 total points
ID: 18871077
I doubt you are saving CRLF in the first place.  Add some diagnostic code to your VB app to confirm the value of the field before you save it.  I'm speculating you'll see something besides CRFL at the line breaks.

0
 
LVL 18

Assisted Solution

by:Priest04
Priest04 earned 167 total points
ID: 18871993
You need to save RTF property of the RichTextBox, and then later load data from database to RTF property again, and all will work well.

Goran
0
 
LVL 24

Assisted Solution

by:Jeff Certain
Jeff Certain earned 166 total points
ID: 18872738
In keeping with what dgmg said... put a breakpoint where you're retrieving the text. If you have CR/LF there, they will show up as boxes (non-printable characters) in the text string.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 2

Author Comment

by:kmoloney
ID: 19020646
SORRY I've been so long in getting back to this question.  Muy busy these days.

Okay, well, when retreiving the rtf property, I'm getting something like the following:

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\f0\fs17 Now is the time for all men to come to aid of their country.\par
\par
However, I have some reservations.\par
\par
I am a coward.\par
\par
Thank you---\par
\par
Kevin\par
}

Which I imagine could be parsed...however...

NOW -- for a bit more intriguing problem...turns out the application DOES have to be a web app, which means...no rich text box control.  So if I'm just using the textbox control (multiline), all CR's and LF's are being removed when I retrieve the info using the .text property of the text box.

Grrrrrrr....
0
 
LVL 2

Author Comment

by:kmoloney
ID: 19021381
OK, I'm half way there --

Converting the the text to have CRLF's isn't that bad; I just do a replace before I save the text in the textbox:

e.g., strVar = TextBox1.Text.Replace(Environment.Newline,Microsoft.VisualBasic.VbCrLf)

That seems to store it okay in SQL.  Now only problem remaining is getting that out to display in web-based controls, like a repeater.  Replacing with "<BR />" doesn't seem to work.
0
 
LVL 2

Author Comment

by:kmoloney
ID: 19230990
It turned out that my question wasn't properly phrased, so I awarded points equally.  The issue is going between VB or VS Studio 2005 controls bound to SQL Server.  Multilines DO store crlf's as characters (Microsoft.VisualBasic.VBCRLF), and that the basic approach to solve the problem was in my last comment dated 5/3/2007.  However, flipping back and forth between display and input states of a one of the VS2005 databound controls (e.g., a formview) should (IMHO) take into account the transition ahead of time.

Manually replacing <BR />'s with Microsoft.VisualBasic.vbCrLf's, and vice versa, in any situation where a view might change from edit to view, is silly.  The control should recognize the context in which it is being viewed in, and developer shouldn't have to piddle with details like this.

This question is in no way solved.  I'll have to post a new one specific to the VS controls I'm interested in.

Thanks.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

696 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