Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Carriage return problem when inserting a string into sql server

Posted on 2006-05-01
Medium Priority
Last Modified: 2010-07-27
Whenever I try to insert a string into an text field in a sql server table, everything except the first line is lost.  The text is from a multi line text field and contains carriage returns and linefeeds.  I use a simple insert statement and have tried this with varchar and text as the field type in sql server 8.0.  What could be the problem?  
Question by:jasoncpp
  • 3
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16576889
can you show use the code, and how you tell that "everything except the first line is lost".
I guess that you are not "looking" correctly, ie with the Enterprise Manager of SQL Server the "Show Table" will give you a grid where you have to select the cell, enter into edit mode and move with the arrows of the keyboard to the next line(s).

you might also try to increate the row height...
LVL 25

Expert Comment

ID: 16579050
Also check how you are inserting the string.  If you are creating a SQL statement using string concatenation it could cause problems.

Author Comment

ID: 16588050
Not happy about the first answer because I pull out the data using standard sql and it still returns one line.  Perhaps this is a sql server problem, I am using simple sql and sqlcommand object, nothing fancy and no string concat is involved.  It is same as:

string sql = "insert into [table] values ('cat\r\dog\r\n','dkfj','fdsf')";
SqlCommand cmd = new SqlCommand(sql, connection);

It inserts 'cat' and I can only pull cat out using a simple select statement.  I will certainly do some more investigation but this is definitely a problem.

One point to note is that I am inserting into a table with an identity to column so I always omit the first column value, so the assume the sql above specifies the column names.  Apart from that , u have the scenario, any ideas?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 25

Expert Comment

ID: 16588220
Well, SQL can certainly handle control characters in text fields:

create table #myTemp ( c1 varchar(20), c2 char(20))

insert into #myTemp values ('line1' + char(10) + 'line2', 'line3' + char(13) + char(10) + 'line4')

select * from #myTemp

drop table #myTemp

Results (note the CR after line1 and CRLF after line3):

(1 row(s) affected)

c1                   c2                  
-------------------- --------------------
line2          line3

(1 row(s) affected)

Are you using SqlParameters to create the SQL statement?  Can you view the SQL statement in the debugger and execute it in SQL Query Analyzer?

Also, I'm not quite following your last statement.  Do you mean that your actual SQL statement specifies the columns, and you specify all but the first column (the identity column)?

Author Comment

ID: 16588361
SqlParameters are used, I forgot to mention, sorry.  I can try it in sql analyzer to verify the sql is correct when I am able definitely.

You are correct with your last statement.  I specify all the columns except the identity column and insert into them using sql parameters.  It has been a few days since I looked at the code, so will check it over and report the result on here.  Any ideas in the meantime that will help me investigate are appreciated.  Cheers.
LVL 25

Expert Comment

ID: 16589939
How are you visualizing the results of the SELECT to show that you only get the first line back?  Query Analyzer?  Dataset?  Debugger?  GridView?  Since SQL can handle CRLF then it's probably either 1) How the data is sent to SQL (which should be OK if you're using parameters and populating them properly) or 2) how the output is inspected.  

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview
Suggested Courses
Course of the Month10 days, 15 hours left to enroll

571 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