Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

What's a good way to edit varchar field containing CRLFs in SQL 2000?

Posted on 2010-09-20
3
Medium Priority
?
287 Views
Last Modified: 2012-05-10
I have an email template table in SQL 2000 which stores the body of an email. I initialized the data using SQL scripts that contained char(13) and char(10).  My client needs to make changes to this data but doesn't have access / knowledge of SMS or T-SQL.

In trying to use Excel (using Alt-Enter for line breaks) and Access (via a linked table), the data gets pasted but replaces each line break with a bunch of spaces.

Are there any end-user friendly ways to edit the text and keep the CRLFs? Or should I generate the email in html format and just teach my client how to incldue "<br />" tags?

Thanks in advance.
0
Comment
Question by:ZekeLA
  • 2
3 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 33720680
You can still use Excel / Access, but you need to think differently.  Here's what I would do with those tools for editing the data.

First, write a procedure to split the text body into multiple rows (there are a lot of SPLIT type functions out there that can handle this already).

Create a table for staging the data that will include the ID of the email template and one line of email template data per row.  So, if your email template had ten line breaks, the resulting table would have eleven rows.

Export the data to that table as multiple rows.

Have Excel read in the rows, one per cell.

Have the user edit the text, one line per cell (don't use the ALT-ENTER line breaks).

When done, the user clicks something that calls a section of code that will update the staging table with all of the new data and trigger another procedure.

The final procedure merges the rows into a CR/LF delimited piece of text and updates the original table.

Voila.

It may sound kludgy, but it actually can work quite well, and gets rid of other kludges (such as the Alt-Enter thing).
0
 
LVL 1

Author Comment

by:ZekeLA
ID: 33720748
I appreciate the idea but since I already have a table with a single template body field, I think it would be easier to just convert it to html and let the user edit it with a simple html editor. A problem with each line as a record is that I would really need a parent child relationship since the email subject is not per line. There's also the issue of what to do for future maintenance if lines get moved around. But your solution would resolve the CR/LF conversion issue.
0
 
LVL 1

Accepted Solution

by:
ZekeLA earned 0 total points
ID: 33799832
Since I already had admin pages that used textarea fields to edit text data, I built a new admin page to edit this data. The textarea preserves the CRLFs.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

877 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