How to retain text formatting in a table

What is the best way to parse large amounts of formatted text datainto a table so that it can be retrieved with as much formattingretained as possible - particularly paragraphs? Will eachparagraph need to be inserted into its own row to be retrieved as aparagraph?

Wrap each paragraph in an HTML <p> paragraph goes here </p> section. They can sit together just like this

<p>1</p><p>2</p><p>3</p>

|||

Thanks, Bryan.

|||

To follow up, I am having trouble retrieving the text data in aparagraph format. For example, I input<p>1</p><p>2</p><p>3</p>into a text field in the database and dropped the table onto a page inVisual Web Developer. The data format looked exactly like it wasinput into the db (i.e.,<p>1</p><p>2</p><p>3</p>) with theparagraph tags showing but no actual paragraphs. Is there a trickto getting the paragraph formatting to show up properly in the controlon my web page?

|||

It depends on what kind of control you are trying to show the value in. Some controls render output to the page as HTML. You have to understand what really happens when an ASPX page is rendered. Browsers dont know what asp.net is. They dont care, they never see it. This is why you need an asp.net server and IIS. The engine on the server downrenders your .net code to HTML in a way the browser can understand. If you view the source of one of your pages, you'll notice all you have is a bunch of HTML and javascript.

The example below points this out by using a label (which just throws values into the HTML stream). And a textbox (which renders literal output)

<asp:LabelID="label1"runat="server"></asp:Label>

<asp:TextBoxID="textbox1"runat="server"/>

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

label1.Text ="<p>1</p><p>2</p><p>3</p>"

textbox1.Text ="<p>1</p><p>2</p><p>3</p>"

EndSub

|||

Bryan - thanks for the input. But I am a little baffled sinceall three controls on my page are ASP (and not simply html) and all arerunat="server" (which I understand to mean that the server converts theasp to html for the browser). But only the ASP label has theparagraph formatting. Since I don't expect to be using the labelcontrol as a primary means of displaying the extensive text data fromthe db, is it simply a matter of setting control properties on othercontrols (like the gridview dataformatstring property) to get them tohandle the <p> tags? Or am I simply limited in my choice ofcontrols that can do this job?

|||

Not all controls will render ...for lack of a better term...renderable HTML from their data / text property. Inside gridviews for example, you'll probably want to use Bound labels, or literal controls. I think you may be missing something conceptually here. Explain to me what type of gridview you are rendering, maybe I can shed some light on the subject - and give you an example of how you'd do what you're trying to do.

|||

The project I am considering involves providing searchcapability on numerous, various documents (mostly magazine articles andresearch papers) each of which is several pages long with faily complexformatting (if I retain the original look). The product needs to beavailable online and on CDs. The actual search results willprobably contain document titles or abstracts that can be selected sothat the full article will be displayed - probablly a gridview orsimilar control for the brief results list with some type of detailsview counterpart for the selected text content display. My choiceof controls may need to include Windows forms controls as well as webpage controls.

At this early stage I am wondering if Ishould put the formatted documents into the database as varbinary datathat can be searched and retrieved maintaining the formatting. Or, just put the text data into the db with simple paragraph formattingthat will give a uniform look to the search output. I have done abit ofdatabase work involving simple data searching, etc., but never withextensive, complex text fields such as this where the formatting in theoutput is important.

Thanks for the insights so far and for any additional help you can provide.

|||

Ok, I think you've done a pretty good job thinking through this so far, but I'll offer up a little advice to you. First of all, probably the best / most accepted web format for complex documents is PDF. There are numerous .NET utilities to encode input into a PDF, including converters that could take word documents, excel spreadsheets, star office...just about anything you can think of - and turn it into a PDF for your purposes. Now, my advice would be to NOT put all this information in the database. To truly allow a full text search - you are going to cause yourself a huge amount of work, and headache.

Store a path to the PDF in your database, so you know where it's at, and render a title, maybe document iinformation like creation time, last modified, owner, short description...etc.

Now the magic comes in. Tie into the MS Indexing service. It can allow a really slick full text search, and there is a connector that allows the indexing service to search inside pdfs. So all the text inside the PDF itself will be indexed, and included in the search criteria. What's returned from a call to the indexing service is a location to the document (your aspx form). My advice is to break it out like this

Site > Document List > Document Information Page with a link to the document (derived from the path in your database)

When the MS indexing service returns, it will return the location of your Document Information Page (as a link) or a list of them depending on how many results were returned. Clicking it will take you directly to the pertitant information.

Here is a great article on accomplishing this.

http://aspnet.4guysfromrolla.com/articles/033005-1.aspx

|||

Thanks again Bryan. I have been looking at that article and will try that approach.

Followupquestion on the previous discussion on controls. Are you aware ofany web controls that render html besides the label? I wouldthink the textbox would have a property that would support htmlrendering but can't find any.

|||

A textbox will not. You'll need an HTML based textbox that can support it. Much like the one you use to post comments on this site. There are several out there. the FCK Editor is the one I usually use, but there's also FreeTextBox, which is the other big free one.

An ASP:Literal control gives you options to render HTML formated output, transform it, or passthrough. That may be a great display control for you, but .NET has no controls that are directly editable, that support HTML nativly.

|||

Bryan. Thanks for all your help in sorting this out. Good to have some options as I move forward with this project.

How to Retain Text and Paragraph Formatting

Is it possible to retain text or paragraph formatting in a SqlServer 2005 Express edition table? If so, how?

I am particularly interested in keeping a linebreak between paragraphs and the only way I can think to do that is to put each paragraph in its own row. But I want some input before I undertake that substantial task.

Thanks for any help provided.
You can use char(10) which is the hard break in SQL.

eg.

Code Snippet

Print 'Microsoft' + char(10) + 'Website'

The Output is like below:
Microsoft
Website

You can as many as char(10) to provide line break in your query.

Even the below syntax will also work.

Code Snippet

select 'Microsoft' + char(10) + 'Website'

|||Hi Vidhya. Thanks for your advice.

I am trying to understand how to insert the data into a table and retrieve to a control on a VB form. In Visual Basic Express I tried including the char(10) with the data by pasting it into my table like this:

Paragraph 1 text here. + char(10) + Paragraph 2 text here. + char(10) + Paragraph 3 text here.

But all I get for output in my datagrid control is the literal string as one block of text.

I use a similar technique with html tags (

) if I am outputing to a webcontrol and it seems to work okay if the control renders html (ie.,

Paragraph 1 text here

Paragraph 2 text here

Paragraph 3 text here

). But this doesn't work for Windows forms unless I am using a webbrowser control and html controls (which I don't want to do).

So I opened up SQL Server Management Studio Express and created a database with a table (text) and column (paragraph) and ran these scripts:

INSERT INTO Text (Paragraph)
VALUES ('Paragraph 1 text here.' + char(10) + 'Paragraph 2 text here.' + char(10) + 'Paragraph 3 text here.')

SELECT *
FROM Text

select 'Microsoft' + char(10) + 'Website'
From Text

But my output is still a single line of text without paragraph formatting.

When I run the code you provided (Print 'Microsoft' + char(10) + 'Website'), that works perfectly. But I don't know how to insert and retrieve the formatted text data in my db.

I am relatively new to SQL, so I realize that I am missing something simple in my implementation of your advice. Any suggestions on how to apply this concept is greatly appreciated.|||To Insert into table jus insert as usual

insert into <tablename> values('col1','col2')


To select from the table with char(10) you can use

select col1,char(10),col2 from <tablename>

Run the above query in text format(ctrl+t)

|||Thanks for the follow-up. I have had some success (I think). Here is what I have tried:

CREATE TABLE Paragraph
(ID int Primary Key IDENTITY(1,1) NOT NULL,
FormattedText nvarchar(500) NOT NULL)

INSERT INTO Paragraph (FormattedText)
VALUES ('Paragraph 1 text here.' + char(10) + 'Paragraph 2 text here.' + char(10) + 'Paragraph 3 text here.')

select FormattedText from Paragraph

If I have the "Results to Text" button clicked, the output looks great, like this:

Paragraph 1 text here.
Paragraph 2 text here.
Paragraph 3 text here.

If I output with "Results to Grid" clicked, it loses the format and looks like this:

Paragraph 1 text here. Paragraph 2 text here. Paragraph 3 text here.

So I think I am making progress. I am getting the data into my table ok. Getting it out in the proper paragraph format is the challenge, which is easy in Management Studio.

But when I use the same table in Visual Basic and output the row of data to a datagrid, it comes out like this, without the paragraph formatting:

Paragraph 1 text here. Paragraph 2 text here. Paragraph 3 text here.

So my problem now is how to get it to display properly on my Windows Form. Any thoughts on this, or do I need to switch to the VB forum?|||Im not sure abt VB. But i think there is a command "Break" in VB. Pls check
|||I will look into it further. There is a break property for creating menus, but I am not sure if it can be applied to datagrids or other controls for displaying formatted data.

Thanks again for your helpl.