Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-09-20
3
Medium Priority
?
285 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
[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
  • 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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