Solved

Infopath - Gathering Data using Particular Character Set

Posted on 2010-11-12
6
716 Views
Last Modified: 2012-06-21
Hi,

I am currently involved in a project which uses an Infopath form to gather data and then submits that data to an Oracle database. (Infopath Form is hosted on Sharepoint)

If a user populates the form fields by typing in the data, there's no problem All the data is entered in to the DB correctly.

However, if a user copied and pasted text in to the form from Word for example, then there can be problems. For example, single quotes, tabs and/or other special characters often appear as squares or upside down question marks (¿).

This seems to be because the character that is being passed by Infopath is not a valid character in the Oracle DB character set.

Does anybody have any advice on how to rectify this?

Thank you in advance

Ben
0
Comment
Question by:Beircheart
[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
  • 3
  • 3
6 Comments
 
LVL 28

Accepted Solution

by:
clayfox earned 500 total points
ID: 34121256
You would probably need to usesome conditional rules which would check for special characters and then remove them.
You can use a concat function to combine a substing-before the special character and substring-after the special character.

You would need one of these per special character type or you could recursively check until they are all gone.
0
 

Author Comment

by:Beircheart
ID: 34121385
Thank you for your response clayfox but unfortunately it is a very large form with over 100 fields so this solution would not be ideal for my circumstance.

I may need to fix this (Using a similar solution to what you suggested) as it is being entered in to the DB. i.e. Insert \ Update statements are triggered on Submit, these statements could strip charcters as you described. Better again, there may be a way to convert the strings to another character set within the statement?? Reaching?

The only problem with this is that I don't have access to these statements at the moment. An external contractor developed that aspect and their keeping it in a black box at the moment. If I touch it and anything goes wrong, they'll just say I broke it and wash their hands of it...
0
 
LVL 28

Assisted Solution

by:clayfox
clayfox earned 500 total points
ID: 34121468
I assume you are using web services to move the data into Oracle. Ther emay be a way to have it cleaned on the server side.

I am guessing the InfoPath is using rich text controls. If you used text boxes it would reformat for you I am pretty sure. The trade off is that you would lose any special formatting but I am not sure Oracle can perserve that anyway.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Beircheart
ID: 34121874
That's correct, web services are being used to populate the DB.

In Infopath however, I am using standard Text Box controls rather than Rich Text Boxes so unfortunately that doesn't do the trick.

Do you think modifying the web service is the way to go? Or have you any other thoughts on this?

Thanks again for your help clayfox
0
 
LVL 28

Expert Comment

by:clayfox
ID: 34129620
Not sure if you could fix it moving data from one field to another.  

Can you not edit the infopath form?

I would screen for the characters if it was me.
0
 

Author Closing Comment

by:Beircheart
ID: 34195901
clayfox, even though you answered everything I asked in a timely manner, I'm only awarding a B because I can't imagine this solution being very useful to someone else due to the fact that there is no code or explanation of how to implement any of the suggested fixes. It just so happens that I think I know how to do it whereas someone else may not.

Thank you for your help

Ben
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

623 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