Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert multi-line text to SQL table

Posted on 2009-04-09
13
Medium Priority
?
2,215 Views
Last Modified: 2012-05-06
I have multiple textarea fields that upon submit need to keep the carriage returns, tabs, spaces, etc. when it is saved to the SQL table.  The data type I have for the fields is 'ntext'.

What do I need to do so that the carriage returns and tabs, etc. are sent to the table and displayed properly when shown on the page.
Here are examples of the form field and the insert code:
 
<cftextarea name="strDetail"
    label="Description:"
    cols="75"
    rows="15"
    wrap="virtual"
    required="true"
    validate="noblanks"
    message="You must enter a detailed description of the request." />
 
<cfquery datasource="mydns" name="insertexample">
     insert into MY_table
          (intID, 
          strDetail, 
          dtiModified, 
          strModifiedBy)
     values
          (#variables.varintID#, 
          '#form.strDetail#', 
          '#DateFormat(Now(), 'mm/dd/yy')# #TimeFormat(Now(), 'hh:mm:ss tt')#', 
          '#Variables.User#')	
</cfquery>

Open in new window

0
Comment
Question by:Lee R Liddick Jr
[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
  • 7
  • 6
13 Comments
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 24107680
You shouldn't have to do anything special. I save text with CR/LF all the time and it is preserved in the database. Or are you talking about the soft wrapping that results in a multi-line text box? That soft wrapping doesn't cause CR/LF characters to be inserted into the text. In that case you're going to have to process the text and do the line-breaking in code before storing the data in the database.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24107873
When it is displayed on the page it lumps everything right after each other.  In the database field it also displays it as one long text string.

In the table field it displays like this:
Set up new stuff.  When the user activates one of these it will show one of the following: Something 1 Something 2 Something 3 Something 4.  Please do something.

When it should look like this in the table and/or when displayed from a query:

Set up new stuff.  When the user activates one of these it will show one of the following:
     Something 1
     Something 2
     Something 3
     Something 4
--------------------------------------------------------------------

You wrote about processing the text and doing the line-breaking in code before storing the data in the database...if that is the case, how exactly is that done as I have never had experience in doing that before.  

I even upped the point value since this is more than a simple answer.
0
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 24108213
You say, "In the table field it displays like this." Are you talking about in query results from SQL Server Management Studio? Are you using Grid or Text view for the results? Grid view will never show the line breaks, it always puts everything in a single row. If there are CR/LF characters in the string, Text view of the results would show you the actual formatting.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Lee R Liddick Jr
ID: 24108843
No query...just opening up the table in SSMS and looking in the column.  I have queries that are displayed on the web...when it displays on the web, all slammed together like I displayed above.
0
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 2000 total points
ID: 24109680
When you open the table in SSMS you are executing a query (click on the little SQL button and you'll be able to edit the query) and displaying it in a grid. You'll never see the CR/LF rendered in that format, it will always appear as a single line.

When you display the queries "on the web" are you displaying them in a textarea or other control, or just writing them to the page? If the latter, you won't see the CR/LF there either, because HTML doesn't honor CR/LF characters. That's just the way HTML works. You'd have to put the text into a multi-line control (textarea) in order to see the effect.

The first thing you have to figure out is whether or not the CR/LF data is actually in the database. So far nothing you've done would seem to conclusively prove it isn't there.

Go into SSMS. Open a new query window. Write your query (select * from whatever). Before executing the query, go to Query > Results To > Results To Text. That will show you definitely whether the data in the database contains the CR/LF or not.

If the data were entered the way you showed above, as multiple lines in the textarea, you should see the results breaking across multiple lines. If you don't, then the CR/LF characters are not making it from the UI into the database, so the next step I'd do is use SQL Profiler to view the traffic between the application and database and see if the data is actually being passed through with the CR/LF characters intact.
0
 
LVL 21

Assisted Solution

by:Craig Wagner
Craig Wagner earned 2000 total points
ID: 24110088
Here's an example of what I'm trying to illustrate.

I created a table with two columns, an integer and a varchar(50). I then ran the following SQL:

insert into junk
values(3, 'this is some text
that contains
line breaks')

As you can see from the attached images, when I view the results of a query against the row (i.e. select * from junk where primarykey = 3) I see a single row in grid view but I see the line breaks in text view.


2009-04-09-11.30.29.jpg
2009-04-09-11.30.45.jpg
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24112606
I think I found what part of my problem is.  I did what you had above and then looked further into my code.  The form actually goes to a validation page for the end user to validate what they entered...if it's wrong, they return to the form, correct what they had and resubmit it.  If it's okay, then they just submit it.  But I have the form data saved as variables on this page and then submit to the database.  For example:


<cfparam name="variables.strDetail" type="string" default="#form.strDetail#" />
<!--- this is the code that displays the data from the form --->
<tr>
    <td style="text-align:right; font-weight:bold; width:25%;">Description:</td>
    <td colspan="3">#variables.strDetail#</td>
</tr>
<!--- then if end user hits submit, this is what gets sent to the database for insert --->
<cfinput type="hidden" name="strDetail" value="#strDetail#" />

Open in new window

0
 
LVL 21

Assisted Solution

by:Craig Wagner
Craig Wagner earned 2000 total points
ID: 24112670
That HTML is definitely not going to show line breaks, because you're basically just inserting the content as text into the HTML document, and as I said before, HTML doesn't honor CR/LF, if you want a line break in HTML you need to use <br>. If you want the validation/confirmation page to show the line breaks, you're going to have to do a substitution on the string to replace any CR and/or LF with <br> to get it to display properly. Either that or put the data into a disabled textarea.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24112691
I did try to put just that field into a disabled textarea box but I couldn't get the data to come back right if the end user chose to go back to the form instead of submitting.  You know of any other type of validation I can use.  I found this one off the internet but it seems to not be doing what I need.  Any suggestions on that?
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24149671
Is this happening because it is a flash form?  I had to go out to the internet to figure out how to write the code to even get the CR's in.  I got it partly working but there is still something that is not right.  
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24171926
I've tried CR, chr(10, and char(10) to convert the CR's to <br />'s and none of that is working...what is it supposed to be?  This is very frustrating.
0
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 24250656
I put quite a bit of effort into putting together examples showing that the carriage returns are being stored and retrieved from the database (which was the first part of the question).

The thread then morphed into how to convert the carriage returns into HTML line break tags, which I could not help with because I do not know ColdFusion (this wasn't tagged as a ColdFusion question to begin with).

I think at least part of the points should be awarded for putting the OP on the right track.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24250849
I have no problem with that Craig...I would have just awarded the points without posting the delete but after my last three posts with no response, I figured I would get your response with the delete.  I appreciate all the assistance with the beginning part of this as it was initially thought it was a SQL issue as to why it wasn't putting the CR's in.  Thanks again and I will be posting the points here shortly.  Pool guys are here now...thanks again.
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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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