Solved

How do I insert a LF in a default value in an ntext field in SQL table?

Posted on 2015-01-29
12
47 Views
Last Modified: 2015-02-23
I have a SQL table with an Access front end.  In the table, I define a default value for an ntext field.  I would like the default value to include multiple paragraphs, but I have been unable to find any way to insert a LF into the value.  Any help will be appreciated!
0
Comment
Question by:arstark
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 12

Expert Comment

by:FarWest
Comment Utility
did you try
chr(10) -- for LF
chr(13) -- for CR
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
You may not be able to do his easily...
AFAIK, SQL Server does not support fields with CR/LF characters, and have them be interpreted as true Carriage Returns/Line Feeds.

You can do this in Access 2007 and higher if you use a "Rich Text" field.
But you need to create the Default value with "HTML" line feeds (Rich Text is really HTML)
So your default value might look something like this:
"Your First Paragraph <br> Your Second Paragraph <br> Your Third Paragraph"

Perhaps there is a workaround for this in SQL, ...so lets see if another Experts can contribute more...

JeffCoachman
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Concatenate CHAR(10) wherever you need a line feed:
...
DEFAULT 'Paragraph 1 blahblahblah' + CHAR(10) + 'Paragraph 2 moreblah' + CHAR(10) + 'Paragraph 3 endofblah'
...

For example:
declare @text nvarchar(4000)
set @text = 'Paragraph 1 blahblahblah' + CHAR(10) + 'Paragraph 2 moreblah' + CHAR(10) + 'Paragraph 3 endofblah'
print @text
0
 

Author Comment

by:arstark
Comment Utility
This looks like vb code.  I am talking about going to the field in the table from SQL Server Management Studio and entering the data.  But thanks for the response!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
You will have to try both suggestions.

Note that while you can "insert" these Carriage returns/Line feeds/Breaks, "characters" ...in the field, ...they may not display as actual line breaks when you type into them...
You may need to display these fields in a specially formatted control...
Keep us posted
0
 

Author Comment

by:arstark
Comment Utility
We've tried both suggestions with no luck and are still looking for a solution.  In the past we were able to insert line feeds and carriage returns when we used an Access project (.ADP) to make changes to the default values in the table. Now it seems that Access projects with SQL 2008 R2 will not allow design updates and SSMS is not allowing us to do it either, so we're looking for an alternate 3rd party tool/terchnique that will allow for these type of updates to the table default values.

I've read on other forums that others have had this same issue, however there wasn't a clear solution.  Thinking that copy/pasting the default values from word, notepad maybe causing the issue, we tried "programmer's notepad" with Unix line endings and that didn't work either.  

Is there a good 3rd party tool instead of SSMS that we could use that might work better with LF/CR in the default text?  Is it so unusual to have paragraphs as part of the default value?  

We use the defaults to populate Access forms and generate Access Reports, some times the values are updated, but some times  they are just left as default. Our staff likes to have the defaults in there so they can they don't have to start from scratch each time they set up a new record.

Thanks in advance for any advice or help!
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I'm am not sure...

Your situation is unique in that you are not storing your data in the standard way.
Typically a "break" (Carriage return or line feed) is interpreted as a new "record" (not just multiple parts of one record.)
So your situation is similar to you having one customer record, and you then using a memo field, you store their multiple "Orders" (each order separated by a cr/lf)

In a standard normalized database you would have one Customer table and one Order Table.
So One Customer record could have many Order "records".
You could then "concatenate" the many records together (with your CR/LFs) for display purposes only .

This more standard approach would also be helpful if you wanted to filter or sort for the individual Orders.
(This is impossible with your current design.)

I will admit that I don't know your data or its purpose, ...but the whole reason why this is difficult to do is that more advanced databases like SQL Server, are a lot more structured in the way they need the data to be stored.
    "In the past we were able to insert line feeds and carriage returns when we used an Access project"
Yes Access allows this, and sometimes this spoils you into thinking that it is acceptable.
;-)
(Perhaps, now that you are moving to SQL, this might be the time to normalize this structure a bit more.)

In the end, ...then only other way I can see doing this is to add the cr/lf as HTML breaks (</br>), then use a HTML viewer control (Like the webbrowser control in Access) to display the breaks.

JeffCoachman
0
 
LVL 12

Expert Comment

by:FarWest
Comment Utility
what about making a special character pattern to be replace later for vbCrLf ,
for example when you adding the records you separate them with "#CR#" then before display or print you replace this text with vbCrLf
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If you need a full cr and line feed, you should be able to add char(13) and char(10) rather than just char(10):

DEFAULT 'Paragraph 1 blahblahblah' + CHAR(13) + CHAR(10) + 'Paragraph 2 moreblah' + CHAR(13) + CHAR(10) + 'Paragraph 3 endofblah'

Your original request just asked for a line feed, which is char(10).
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
...But the OP needs a certain amount of breaks set as a "default" Value...
That is why I was gearing them toward normalizing this data into separate records...
0
 

Author Comment

by:arstark
Comment Utility
We decided that will break this data field into several fields, one for each paragraph of text being stored.  Just to clear up a couple of points, the database is normalized and it's always been a SQL database, it's just that we "view" the data via Access (mdb and/or accdb with linked tables) (we use it as a "front end" for users).  In the past, we were able to use an Access project (.adp file) to insert and make changes to these default values, which included having multiple paragraphs in this one memo field.  

Based on the fact that we were having a hard time finding a way to do this and because of expert comments, it seems like the best practice solution was to break up the field into multiple fields.

Thanks for the advice!
0
 

Author Closing Comment

by:arstark
Comment Utility
Pointed us in the right direction, but we never did find the specific answer we were looking for.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

13 Experts available now in Live!

Get 1:1 Help Now