Datagrid, formatting, carriage returns

15 Dec 2005 3:01 PM

Zamdrist

It appears as though, in the transition from a query to the datagrid I
am losing the carriage returns in an address field which I would like
to maintain for the purposes of a Word merge.

Here's the stored procedure. I have to Cast the data from text to
varchar in order to replace the @@ characters at the beginning of the
address field. But even if I don't do that and not cast it, I still
lose the carriage returns. Ideas? Thanks.

CREATE Procedure sp_UTL_MGAttorneyGroupMerge

@sAttorney Varchar(50),
@sGroup Varchar(50)

As

Select ContactName=Case FullName When '' Then CompName Else FullName
End,
Replace(Replace(Cast(AddrLines As Varchar(500)),'@@',''),'
',Char(10)+Char(13)) As AddressLine

>From Contacts

Inner Join ContactCategories ContactAtty On ContactAtty.Contacts =
Contacts.Contacts
Inner Join ContactCategories ContactGroup On ContactGroup.Contacts =
Contacts.Contacts
Where ContactAtty.Category = @sAttorney And ContactGroup.Category =
@sGroup
Order By Contacts.LastName

16 Dec 2005 7:06 PM

addup

I'm not sure i understand your problem, but

Use View|Source to look at the HTML rendered, are your carriage returns
there?
The UA (web browser) generally ignores all whitespace

have you tried HTML encoding your address ?

16 Dec 2005 7:20 PM

Zamdrist

The datasource (SQL Server) holds an address field of a data type of
text. Therein this field are carriage returns at the appropriate places
for an address field.

When bringing in this data to a datagrid in ASP.Net, those carriage
returns are gone and the data in that field is now just one long
string.

I was going to then allow the user to export the datagrid to Excel for
the purposes of a mergein MS Word...I know it's a round about way, but
the users are more familar with Excel than database queries. But the
lose of the carriage returns in a wrench in the plan, it wouldn't
format correctly.

In any event, I'm not rendering the HTML in any particular way. It
doesn't appear in the susequent HTML source the carriage returns are
'gone'...and I wouldn't know how to HTML encode the data.

It's the transition from the stored procedure to the datagrid is where
I lose the carriage returns.

Thanks

19 Dec 2005 4:34 PM

addup

That's what i suspected.

HTML normally does not care about carriage returns (or line feeds,
tabs, spaces etc. collectively known as 'whitespace' )

The Databind faithfully renderers your carriage returns into HTML, and
it's the browser that ignores them

The simplest way (with the least impact on existing code) is probably
to do
1. Add the following style code to the top of your page (In the
<HEAD></HEAD> section

<STYLE>.WhiteSpaced {white-space:pre;}</STYLE>

2. Add the following to your datagrid definition
ItemStyle-CssClass="WhiteSpaced"

--

Another way to get the browser to display line breaks is to use the
HTML <BR/> tag

I'm not sure of your SP code, but try

Replace(Replace(Cast(AddrLines As Varchar(500)),'@@',''),'
', '<BR/>' ) As AddressLine

to get SQL server to use '<BR/>' instead of the x0A0D cr/lf

--
These are just tow of the MANY ways you could do this :)
Hope this helps.

PS: There's nothing wrong with using excel's HTML import features!

27 Dec 2005 5:24 PM

Zamdrist

Well I got results, of sorts, with replacing Char(13)+Char(10) with
'<BR/>' and it looks right on the resulting web page & datagrid. When
exporting to Excel however, it unfortunately puts each line of the
address in a row by itself...when has the unwanted side-effect of when
you merge into Word...each address line is seen as a record of it's
own...not good

I'm actually using both Whitespace style as you suggested, and added
the replacement in the stored procedure when when the WhiteSpace didn't
seen to do anything at all.

I certainly appreciate your help! Thanks.

28 Dec 2005 4:42 PM

addup

my bad - The white-space:pre; needs to be applied to the individual
table cells (TDs) and not the entire table

change the
<STYLE>.WhiteSpaced {white-space:pre;}</STYLE>
to
<STYLE>.WhiteSpaced, TABLE.WhiteSpaced TD {white-space:pre;}</STYLE>

and go back to the Char(13)+Char(10)
This should show the html OK ... I'm not a 100% about the excel import
(it'll also depend on your EXCEL version)

--- having said that ---

You seem to have two separate requirements (presentation and excel -->
word mailmerge)

Since your excel needs seem to be largely unformatted, I suggest using
a simple CSV (comma delimited) format.

1. Leave the "pretty" HTML/datagrid display intact
2. Add an "Export to excel" button on your page and generate a csv file
with "address line 1", "Address Line 2" columns in it (serve this with
an excel mime type)

Just my .02
Hope this helps

28 Dec 2005 7:49 PM

Zamdrist

Thanks addup...my first try with this syntax didn't yield the results
I'm looking for. I event set the <asp:Boundcolumn
ItemStyle-CssClass...> to WhiteSpaced, and that had no apprent effect.

Ultimately all that is important is that the merge contain the carriage
returns so that the form letters format properly or labels for
instance.

I appreciate your help and if you have any more ideas that would be
great, but don't feel overly obligated! :) Thanks