Solved

Word Format Paste into TextArea Field Not Displaying Properly in HTML View

Posted on 2008-10-17
21
520 Views
Last Modified: 2013-11-19
i'm sorry All, i haven't got any code to show you, well i have if you let me know what you need. Basically I'm building a program using good old ASP VBScript, I have a form with a textarea field on it, the form sends the data to a stored procedure on the database, basically everything works fine if the user manually tyes the data into the textarea field, however if they paste from word, the data gets stored on the database, but then when you try and view the data it shows errors like -

"PERSON SPECIFICATION

Job Title: Economist
Department: Policy Services
Location: Stoneleigh, Warwickshire
Grade: 6

Knowledge and Expertise

Essential

"aU"

Where the word document had -

"PERSON SPECIFICATION

Job Title: Economist
Department: Policy Services
Location: Stoneleigh, Warwickshire
Grade: 6

Knowledge and Expertise

Essential

"      To hold a Degree with a significant economic component (either at undergraduate or post graduate level or equivalent qualification.
"      To have excellent policy analysis skills.
"      To be a proficient user of the range of Microsoft Software.
"      To have knowledge of the economics and policy factors impacting the farming industry and the rural economy.

Desirable

"      To hold an economics degree or a relevant post graduate qualification.
"      To have an understanding of international affairs
"      To have an understanding of rural issues

Relevant experience

Essential

"      To have experience in preparing, analysing and communicating specialist and strategic information for the development of policies.
"      To have experience in advising others on policy matters
"      Experience of liaising with Civil servants, Commission Officials, Government and/or non-statutory bodies.
"      Previous track record of building good working relationships both internally and externally.
"      Ability to communicate appropriately with the media.

Desirable

"      Experience of public policy analysis.
"      Experience of giving public presentations on policy information.

Communication and Relationship Skills

Essential

"      Able to communicate effectively in written and spoken word, to a range of audiences.
"      Able to understand and be able to communicate complex issues."

The data stored on the database looks like -

"PERSON SPECIFICATION

Job Title: Economist
Department: Policy Services
Location: Stoneleigh, Warwickshire
Grade: 6

Knowledge and Expertise

Essential

•      To hold a Degree with a significant economic component (either at undergraduate or post graduate level or equivalent qualification.
•      To have excellent policy analysis skills.
•      To be a proficient user of the range of Microsoft Software.
•      To have knowledge of the economics and policy factors impacting the farming industry and the rural economy.

Desirable

•      To hold an economics degree or a relevant post graduate qualification.
•      To have an understanding of international affairs
•      To have an understanding of rural issues

Relevant experience

Essential

•      To have experience in preparing, analysing and communicating specialist and strategic information for the development of policies.
•      To have experience in advising others on policy matters
•      Experience of liaising with Civil servants, Commission Officials, Government and/or non-statutory bodies.
•      Previous track record of building good working relationships both internally and externally.
•      Ability to communicate appropriately with the media.

Desirable

•      Experience of public policy analysis.
•      Experience of giving public presentations on policy information.

Communication and Relationship Skills

Essential

•      Able to communicate effectively in written and spoken word, to a range of audiences.
•      Able to understand and be able to communicate complex issues."

I dont really want to use a rich text editor, and people will need to paste from Word, is there anyway around this, either client side, or on the database itself, i would prefer the latter if possible?

Thank you
0
Comment
Question by:garethtnash
  • 13
  • 8
21 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22743198
Are you using parameterized stored procedure calls for your text area data to be inserted into the database?
0
 

Author Comment

by:garethtnash
ID: 22743255
There's a question, my stored procedure looks like this (on the database) -

"CREATE PROCEDURE NormalAdvert
@EmployeeId int,
@SiteID int,
@ClientId INT,
@JobTitle nvarchar(50),
@Description ntext,
@Payrate nvarchar(50),
@location nvarchar(50),
@category nvarchar(50),
@employmenttype nvarchar(50),
@reference nvarchar(50),
@startdate nvarchar(50)
AS
Declare @NewID INT
INSERT INTO dbo.JBAdvert (JBASiteID, JBAEmployeeID, JBAClientID, JBATitle, JBADescription, JBAPayRate, JBALocation, JBACategory, JBAEmplymentType, JBAReference, JBAStartDate)
VALUES (@SiteID, @EmployeeId, @ClientId, @JobTitle, @Description, @Payrate, @location, @category, @employmenttype, @reference, @startdate)
GO"

and the asp page looks like this -

"<%

Dim Command1__EmployeeId
Command1__EmployeeId = "0"
if(Request.Form("employeeid") <> "") then Command1__EmployeeId = Request.Form("employeeid")

Dim Command1__SiteID
Command1__SiteID = "0"
if(Request.Form("siteid") <> "") then Command1__SiteID = Request.Form("siteid")

Dim Command1__ClientId
Command1__ClientId = "0"
if(Request.Form("clientid") <> "") then Command1__ClientId = Request.Form("clientid")

Dim Command1__JobTitle
Command1__JobTitle = "0"
if(Request.Form("postjobtitle") <> "") then Command1__JobTitle = Request.Form("postjobtitle")

Dim Command1__Description
Command1__Description = "0"
if(Request.Form("taMessage") <> "") then Command1__Description = Server.HTMLEncode(Request.Form("taMessage"))

Dim Command1__Payrate
Command1__Payrate = "0"
if(Request.Form("postpayrate") <> "") then Command1__Payrate = Server.HTMLEncode(Request.Form("postpayrate"))

Dim Command1__location
Command1__location = "0"
if(Request.Form("jsloc4") <> "") then Command1__location = Request.Form("jsloc4")

Dim Command1__category
Command1__category = "0"
if(Request.Form("jscat4") <> "") then Command1__category = Request.Form("jscat4")

Dim Command1__employmenttype
Command1__employmenttype = "0"
if(Request.Form("postemploymenttype4") <> "") then Command1__employmenttype = Request.Form("postemploymenttype4")

Dim Command1__reference
Command1__reference = "0"
if(Request.Form("postreference") <> "") then Command1__reference = Request.Form("postreference")

Dim Command1__startdate
Command1__startdate = "0"
if(Request.Form("poststartdate") <> "") then Command1__startdate = Request.Form("poststartdate")

%>
<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_recruta2_STRING
Command1.CommandText = "dbo.NormalAdvert"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@EmployeeId", 3, 1,8,Command1__EmployeeId)
Command1.Parameters.Append Command1.CreateParameter("@SiteID", 3, 1,8,Command1__SiteID)
Command1.Parameters.Append Command1.CreateParameter("@ClientId", 3, 1,8,Command1__ClientId)
Command1.Parameters.Append Command1.CreateParameter("@JobTitle", 200, 1,50,Command1__JobTitle)
Command1.Parameters.Append Command1.CreateParameter("@Description", 200, 1,5000,Command1__Description)
Command1.Parameters.Append Command1.CreateParameter("@Payrate", 200, 1,50,Command1__Payrate)
Command1.Parameters.Append Command1.CreateParameter("@location", 200, 1,50,Command1__location)
Command1.Parameters.Append Command1.CreateParameter("@category", 200, 1,50,Command1__category)
Command1.Parameters.Append Command1.CreateParameter("@employmenttype", 200, 1,50,Command1__employmenttype)
Command1.Parameters.Append Command1.CreateParameter("@reference", 200, 1,50,Command1__reference)
Command1.Parameters.Append Command1.CreateParameter("@startdate", 200, 1,50,Command1__startdate)
Command1.Execute()

%>
<% Response.Redirect("aftervacancyposted.asp") %>"

Does that help at all?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22743379
And what field are we dealing with here?  Description?
0
 

Author Comment

by:garethtnash
ID: 22743407
yes sorry we are, description is the field, (Request.Form("taMessage")) is the input
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22743467
And you're on SQL 2000 huh... not 2005?  

Anyway, that would be just a matter of datatype choice.

I think it's a matter of the special characters what word uses.  I'd bet it's just an issue with word too (and other open/ms office like products) and not just a matter of pasting vs. typing.  To test this, copy from word, then paste and re-copy through notepad, before pasting into your form.  This will at least verify what I am thinking.
0
 

Author Comment

by:garethtnash
ID: 22743611
still get the same problem this way, on this particular inser the problem is caused by the -

"      To have

or in notePad -

"      To

Something I think to do with the way the unordered list, but it also picks up thinks like tabs etc??

the data now reads -

PERSON SPECIFICATION

Job Title: Economist
Department: Policy Services
Location: Stoneleigh, Warwickshire
Grade: 6

Knowledge and Expertise

Essential

"aU
thanks
0
 

Author Comment

by:garethtnash
ID: 22743722
you're right i am on sql 2000
0
 

Author Comment

by:garethtnash
ID: 22743752
I was wondering whether there waas any way to write a trigger on the database that looks for word formatting, tabs etc and removes/replaces it?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22744108
Yes.  But it's trickier to do since you are working with SQL 2000 and a ntext column and not SQL 2005 and nvarchar(max).  I have to admit I don't have a lot of experience with (n)text columns.
0
 

Author Comment

by:garethtnash
ID: 22744215
How would you do it in MS SQL 2005?  thanks
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22744739
Well you could have an nvarchar(max) column and data type.  This would allow you to load the entire contents into a variable and do a replace based on the ascii character value of the characters (tab) that you want to replace.  Otherwise, you have to read and update a text column which is more cumbersome.
0
 

Author Comment

by:garethtnash
ID: 22747343
How about if I change the field from ntext to nvarchar(8000)? Any ideas?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22749499
nvarchar only goes to 4000 or to max which is SQL 2005 and above only.  If you select the data from the table (which SQLEM won't really work because it will only show the first X characters) how much does it display.  It may just be that you don't have the right encoding set on your ASP.
0
 

Author Comment

by:garethtnash
ID: 22751221
SQLEM? i tried changing the ntext to nvarchar (8000) and was given the error a maximum of 5000 characters, I then tried changing the stored procedure from ntext to nvarchar(5000) and was given the error a maximum of 4000 characters, so I'm going to settle on 4000 characters, so now there are really two questions, 1 how to search for the tab space and replace it, and 2 as everything is html encoded, what should the maximum input be on the form?

Thank you
0
 

Author Comment

by:garethtnash
ID: 22752649
Anyone have any ideas on this, I can't believe that I'm the only person having difficulty with pasting into textareas?

Please help Experts...
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22752703
SQLEM?
SQLEM is SQL Enterprise Manager.  But I guess you're in Query analyzer.

i tried changing the ntext to nvarchar (8000) and was given the error a maximum of 5000 characters, I then tried changing the stored procedure from ntext to nvarchar(5000) and was given the error a maximum of 4000 characters, so I'm going to settle on 4000 characters
It is 4000.  No doubt about that.
update Yourtable

set YourField = replace(yourField, char(9), ' ') -- you need to decide what you want to replace the tab with.  I used a single space here.

Open in new window

0
 

Author Comment

by:garethtnash
ID: 22752738
Sorry Brandon, not the quickest over here, and pulling my hair out, where do I do that?, can you give me an example please?
Also would it be an idea to remove the ordered/unordered lists all together? is that possible.

Thank you
0
 

Author Comment

by:garethtnash
ID: 22752741
P.S. have been searching the internet since Friday AM trying to find this, doesn't appear to come up much? is it a common problem?
0
 

Author Comment

by:garethtnash
ID: 22752775
here's my ddl
CREATE TABLE [dbo].[JBAdvert] (

  [JBAID] int IDENTITY(1, 1) NOT NULL,

  [JBAEmployeeID] int DEFAULT 0 NULL,

  [JBAClientID] int DEFAULT 0 NULL,

  [JBASiteID] int DEFAULT 0 NULL,

  [JBATitle] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBADescription] nvarchar(4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBAPayRate] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBALocation] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBACategory] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBAEmplymentType] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBAReference] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBAStartDate] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBADatePosted] datetime DEFAULT convert(char(19),getdate(),120) NULL,

  [JBAPostFor] int DEFAULT 10 NULL,

  [JBAInvoiced] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT 'n' NULL,

  [JBANotified] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT 'n' NULL,

  [JBAAccountTYPE] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBAPPClient] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBAPPPaid] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT 'y' NULL,

  [JBAConkersEmail] nvarchar(225) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [JBAInvoiceBatch] int NULL,

  [JBAPayPalRate] decimal(8, 2) NULL,

  CONSTRAINT [aaaaaJBAdvert_PK] PRIMARY KEY NONCLUSTERED ([JBAID])

)

ON [PRIMARY]

GO
 

CREATE NONCLUSTERED INDEX [JBAClientID] ON [dbo].[JBAdvert]

  ([JBAClientID])

ON [PRIMARY]

GO
 

CREATE NONCLUSTERED INDEX [JBAEmployeeID] ON [dbo].[JBAdvert]

  ([JBAEmployeeID])

ON [PRIMARY]

GO
 

CREATE NONCLUSTERED INDEX [JBAID] ON [dbo].[JBAdvert]

  ([JBAID])

ON [PRIMARY]

GO
 

CREATE NONCLUSTERED INDEX [JBAPPPaid] ON [dbo].[JBAdvert]

  ([JBAPPPaid])

ON [PRIMARY]

GO
 

CREATE NONCLUSTERED INDEX [JBASiteID] ON [dbo].[JBAdvert]

  ([JBASiteID])

ON [PRIMARY]

GO
 

CREATE TRIGGER [dbo].[JBAdvert_tr] ON [dbo].[JBAdvert]

FOR INSERT, UPDATE

AS

if @@ROWCOUNT = 0

    return
 

update dbo.JBAdvert

set

    JBAPayRate = replace (JBAPayRate, 'Â', '')

where exists

(

    select

        *

    from

        inserted

    where

        inserted.JBAID = JBAdvert.JBAID

)

GO

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22752871
Also would it be an idea to remove the ordered/unordered lists all together?: Sure, why not.  You would have to parse it into sections in your ASP using some known formatting rules.

Where do you do that? You would do it in your update trigger.


CREATE TRIGGER [dbo].[JBAdvert_tr] ON [dbo].[JBAdvert]

FOR INSERT, UPDATE

AS

if @@ROWCOUNT = 0

    return

 

update dbo.JBAdvert

set

    JBAPayRate = replace (JBAPayRate, 'Â', '')

   ,YourField = replace(yourField, char(9), ' ')

where exists

(

    select

        *

    from

        inserted

    where

        inserted.JBAID = JBAdvert.JBAID

)

GO

Open in new window

0
 

Author Comment

by:garethtnash
ID: 22753101
Thanks Brandon, i'll check and come back to you.

Many thanks
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

705 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

18 Experts available now in Live!

Get 1:1 Help Now