Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-29
12
Medium Priority
?
64 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
ID: 40577993
did you try
chr(10) -- for LF
chr(13) -- for CR
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40578006
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 70

Expert Comment

by:Scott Pletcher
ID: 40578033
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:arstark
ID: 40580408
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
ID: 40580606
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
ID: 40595582
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40597056
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
ID: 40597155
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 70

Expert Comment

by:Scott Pletcher
ID: 40598419
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 1500 total points
ID: 40599314
...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
ID: 40625994
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
ID: 40625999
Pointed us in the right direction, but we never did find the specific answer we were looking for.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

972 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