SQL Update Statement inserting a New Line

[mana2]

Hi,

I'm trying to add a new line to a field in the database so that each item is displayed on a separate line.

So, I'm trying to do:
Math
English

strSQL = "Update tblContent SET Product = 'Math + CHAR(13) + CHAR(10) + English ' WHERE productid = 1"

The result I get is:
Math + CHAR(13) + CHAR(10) + English

I haven't been able to figure out how to do it.

Thanks

 

[markros]

strSQL = "Update tblContent SET Product = 'Math' + CHAR(13) + CHAR(10) + 'English ' WHERE productid = 1"

PluralSight Learning Library

 

[dgillz]

UPDATE will update an existing record in the database. INSERT INTO will create a new record in the database.

What are you trying to do? Add a new record? Add 2 records, one for math and one for english?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[mana2]

Thanks for your replies. I tried:

strSQL = "Update tblContent SET Product = 'Math' + CHAR(13) + CHAR(10) + 'English ' WHERE productid = 1"

But just got the text with no new line:
Math English

Instead of:
Math
English

So, I am trying to update an existing record in the database by adding a new line and more text. I tried to do it manually by opening the database and editing the record but it doesn't accept the new line.

Thanks

 

[markros]

In SSMS you would not see the char(13) + char(10) character. However, it did get inserted. If you output results into Excel, for example, you'll see the items in two lines.

There is a way to do this manually as well, though a bit tricky.

PluralSight Learning Library

 

[mana2]

Markros,

Could you tell me how to insert the new line manually?
I'm using Microsoft SQL Server Management Studio and I select the table and Edit Top 200 records and I can go in and edit the data manually. However, it doesn't seem to allow you to enter a new line. I tried Ctrl Enter and other characters but it didn't work.

If I try through the SQL Update when I display the results, it doesn't work correctly. It might be the way I'm displaying it.

Thanks

 

[markros]

I saw a suggestion in another forum, but I forgot which one and the suggestion I'll try to search in one, but not sure, I'll find it.

PluralSight Learning Library

 

[markros]

May be I was wrong and there is no way. Here is a thread I found

http://stackoverflow.com/questions/...ine-character-in-sql-server-management-studio

and I also tried few combinations and nothing worked.

PluralSight Learning Library

 

[mana2]

Thanks so much for your help, I had seen that one too. It seems you can't do it manually in SQL Server 2008.

 

[SgtJarrow]

I would handle that sort of item on the presentation-side of my program...

In the database, I would store the data comma-separated (or semi-colon, or pipe, or even use XML) instead of storing the data in the formatted fashion. So my data in the table would look something like:

Math;English

Then, in the presentation-layer of my program I would leverage a Replace function or some such to replace the separator character with the char(13) + char(10) - or Environment.Newline in Visual Studio - or whatever the appropriate change is in the language/tool you are using to display the data.

In essence, I would not try to store formatted data in the database - unless that was my requirement. You don't mention having to have rich-text formatting, which would be one of the only times I would consider saving the data as you suggest.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[markros]

mana2 - Yes, seems like you can only do it by coping the text from another place.

However, I still have an impression we did discuss this problem in some forum. May be ASP.NET forums - I'll take another quick search.

PluralSight Learning Library

 

[gbaughma]

In the past, I have used hand-built "macros" for such things, as mstrmage1768 suggests.

For example, I may use \N for a new line..... so my record would look like this:

"Math\NEnglish"

... then on the presentation side (say, in VB), I would do a:

sString = Replace(rs.Fields(Whatever),"\N",VbCrLF)

... or, if I was going to put it in a web page, it would end up as:

sString = Replace(rs.Fields(Whatever),"\N","<br>")

... this method works well where you have other pseud-fields as well... for example, I once used something like this for verbiage on a report:

rs.Fields(TheText) = "[YouthName] will perform 20 hours of community service."

Response.Write Replace(rs.Fields(TheText),"[YouthName]",strYouthName)

... you get the general idea.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mana2]

That makes sense adding Math\NEnglish in the database and then doing the replace.

I tried adding <br> to the data in the database and it might not be the best way to do it but it seems to work:

strSQL = "Update tblProducts SET product = 'Math<br>English<br>Science' WHERE productid = 1"

 

[SQLSister]

I would add a related table and store the values in separate records. ANytime you are adding multiple lines or values to one filed, you have a basic datbase design flaw. Fix as soon as poosible.

"NOTHING is more important in a database than integrity." ESquared

 

[gbaughma]

SQLSister:

I agree 100% about multiple values; but multiple lines would be a common thing...

I can certainly see multiple fields for
Name Address1 Address2 City State Zip

... but I see no problem with multiple lines in a field like "Biography" or something to that effect.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com