line breaks missing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

line breaks missing

Author

Topic

ScreamForMe
Starting Member

2 Posts

Posted - 2006-05-03 : 10:32:06

I am fairly new to SQL Server. I work for an Internet sofware company building web sites for our customers.

I am trying to insert JavaScript ad tags into a text field in my db. It seems that when the calling web page is rendered the line breaks are removed from the js code and it does not function properly. I've doubled up all of the single quotes and the line breaks appear in the insert statement but do not make it to the front side of my web site.

Kristen
Test

22859 Posts

Posted - 2006-05-03 : 14:48:22

What does the data look like if you select it directly from the database? If that looks OK then your application logic to retrieve the value from the database, and put it in the web page, is faulty - and not the storage in the database!

Kristen

ScreamForMe
Starting Member

2 Posts

Posted - 2006-05-03 : 16:13:33

If I grab the data directly from the field in the tabular results after a query the data appears to be all on one line which leads me to believe it's a problem with the way the db insert is being handled rather than an application issue.

sshelper
Posting Yak Master

216 Posts

Posted - 2006-05-03 : 16:29:31

This is a very common question when storing in a database table the value entered in a multi-line text box wherein there are carriage returns. Unfortunately, you have to manually replace the carriage return/line feed with the <br> tags before inserting to the table to retain the formatting. Here's an example code in VB.NET:

lbltest.Text = var_NwsText.Replace(vbCrLf, "<br>")

SQL Server Helper
http://www.sql-server-helper.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-03 : 16:52:39

quote:
Originally posted by ScreamForMe

If I grab the data directly from the field in the tabular results after a query the data appears to be all on one line which leads me to believe it's a problem with the way the db insert is being handled rather than an application issue.

If you change your query options to "results in text" instead of "results in grid" you will see the wrapped lines.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-05-04 : 03:19:42

You could check that the line break is actually there by doing

SELECT TOP 10 *
FROM MyTable
WHERE MyTextColumn LIKE '%' + CHAR(13) + '%'

you might need to use

WHERE MyTextColumn LIKE '%' + CHAR(10) + '%'

depending on how the linebreak is stored.

That will rule out any display-back issues!

Kristen

coryjones
Starting Member

2 Posts

Posted - 2006-05-22 : 19:56:44

I am building a forum.
This is the entire code to the page i am working on, which is the page to display results from an SQL Express database, and I am working in Visual Web Developer Express. All I am trying to do is output the database THREADS field THREADCONTENTS, and have it display the proper linebreaks. I have done the legwork, and tried everything, and cannot get it to work. Where it says <%#Request.Querystring("threadcontents")%> is where I want this to happen. The reason it is request.querystring is because i am even trying to retrieve the dbfield THREADCONTENTS in a previous page and then passing it as a querystring. That doesn't work either. I AM DESPARATE FOLKS! The main page is where the threadposter's names and thread names appear, and i pass those as querystring params to this current page and retrieve them and it works fine. but when i try the same for threadcontents, it's one long string.

In place of <%#Request.Querystring("threadcontents") I have tried:

<%#Replace(Request.Querystring("threadcontents"), vbcrlf, "<br/>") %>
(and have tried ALL variables in place of vbcrlf, like vbcr;vblf;\n;\n\r;chr(13);etc.

<%#Replace(Eval("threadcontents"), vbcrlf, "<br/>") %>

<%# Databinder.Eval(Container.DataItem, "threadcontents").ToString().Replace("vbcrlf", "<br>") %>

I've even tried

<% dim tc as string
tc = request.querystring("threadcontents")
tc = tc.replace(vbcrlf, "<br/>")
response.write(tc)%>

or

<% dim tc as string
tc = request.querystring("threadcontents")
tc = replace(tc, vbcrlf, "<br/>")
response.write(tc)%>

i've also tried putting <pre></pre> tags around
not only the input(to database)multi-line textbox on page one,
but also around an asp:label to display them. No worky-worky.

i've included everything so that you can even see my sql query.
most of the things i've tried return no value, but a couple have just
returned the entire thing in one long string.
I feel like i've tried to cobble together my own solution for long enough, and need a hand with some app specific code. HELP!

here it is:

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<table>
<tr>
<td style="width: 40%" align="left" >
<asp:Image ID="Image1" runat="server" ImageUrl="http://x.xx.xxx.xxx/forumweb/images/header.jpg" /></td>
<td style="width: 60%" align="center">
<asp:LoginView ID="LoginView1" runat="server">
<LoggedInTemplate>
Hello,
<asp:LoginName ID="LoginName1" runat="server" />
! You are already logged in, so post away!
</LoggedInTemplate>
<AnonymousTemplate>
You are not currently logged in.<br />
In order to start a Thread or Post a reply, please
<asp:LoginStatus ID="LoginStatus1" runat="server" />
.
</AnonymousTemplate>
</asp:LoginView>
</td>
</tr>
</table>
<br />

<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
<HeaderTemplate>
<table>
<tr>
<th colspan="2">Current Thread</th>
</tr>
<tr align="left" valign="top">
<td rowspan="2" style="width: 20%"><%#Request.QueryString("threadpostername")%></td>
<td><%#Request.QueryString("threadname") %></td>
</tr>
</headertemplate>
<itemtemplate>
<tr>
<td><%#Request.QueryString("threadcontents")%>
</td>
</tr>
</itemTemplate>
<FooterTemplate></table></FooterTemplate>
</asp:Repeater>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:forumwebsqlConnectionString %>"
SelectCommand="SELECT [threadpostername], [threadname], [threadcontents] FROM [threads] WHERE (([threadname] = @threadname) AND ([threadpostername] = @threadpostername))">
<SelectParameters>
<asp:QueryStringParameter Name="threadname" QueryStringField="threadname" Type="String" />
<asp:QueryStringParameter Name="threadpostername" QueryStringField="threadpostername"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>

i've never been COMPLETELY UNABLE to figure this web stuff out until now, and it seems like such a simple, common problem, but all the answers out there seem to assume i know 90% of what they mean already, so they don't have to say it explicitly. but that is just not working this time.

cory

coryjones
Starting Member

2 Posts

Posted - 2006-05-23 : 15:32:46

well, i did discover something here.

i went ahead and made a new page that searches my db connection to THREADCONTENTS. I made a textbox, and configured my datasource to select from THREADCONTENTS where THREADCONTENTS is LIKE textbox.text.

to my surprise, it will search every word, and return results, but it will not find anything with vbcrlf, vbcr, vblf, chr(10), chr(13), \n, \n\r, etc.

but when I look at my database, my THREADCONTENTS field actually does have little squares signifying where i put linebreaks in when I inserted to the db with a multiline textbox.

so, how exactly does sql express (without advanced services) store linebreaks? shouldn't i be able to search for them? it's no wonder that i can't replace them with "<br>" at output time if i can't even search them. is it BECAUSE i don't have sqlexpress with advanced services to get the full-text indexing? i have wondered if full-text search was the problem, but am really afraid to upgrade because i already installed sqlexpress on top of msde and don't want it to mess everything up.

any ideas anyone?

i really appreciate any attention to this....(begging) please?

cory

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources