Solved

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

Posted on 2008-10-17
21
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 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