Carriage return problem when inserting a string into sql server

Posted on 2006-05-01
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
    LVL 142

    Accepted Solution

    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

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

    Author Comment

    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?
    LVL 25

    Expert Comment

    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

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    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…
    This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now