Using C#, is it possible to make System.Data.OleDb handle special characters?

I have an existing C# project that reads a text file and loads it into an Oracle DB using System.Data.OleDb as provider. Problem is that if the file has upper ascii characters (e.g. ÀÑÓ, non-breaking space) it always generates an error when it tries to load it into Oracle:

Error is: OLEDBConnection Command Parameter data value could not be converted for reasons other than sign mismatch or data overflow.

Our Oracle can accept upper ascii characters (insert via SQL*PLUS works fine), it is System.Data.OleDb that is having issue.

Anyone know if there is a setting to change this? Can't believe that that only accepts A-Z-0-9. Look开发者_Go百科ed thru a ll documentation but couldn't find anything.

If it can't, how do you let OLEDB know to escape the character. Tired putting \ in the file before special characters, but it still errors with same message.

---

Usually this is possible without any problem... since you don't provide much detail only some general pointers:

• Database Charset should be set to AL32UTF8
  you can check this by executing SELECT parameter, value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
• Client Charset should be set to AL32UTF8
  see for this registry settings for NLS_LANG
• Columns should be VARCHAR2
• Connection string should include OLEDB.NET=True
  for further reference see http://download.oracle.com/docs/cd/B28359_01/win.111/b28431/using.htm#i1017221

Other points to check could be OS and Client and OLEDB versions... some have bugs or strange behaviour...

---

You may have to use HEX conversions. For example %c4 is Ä.

Try that and let me know if that works. Here are the conversion functions you can use.

internal String convertAsciiTextToHex(String asciiText)
{
StringBuilder sBuffer = new StringBuilder();
for (int i = 0; i < asciiText.Length; i++)
{
    sBuffer.Append(Convert.ToInt32(asciiText[i]).ToString("x"));
}
return sBuffer.ToString().ToUpper();
}

internal String convertHexToAsciiText(String hexString)
{
    StringBuilder sb = new StringBuilder();

 for (int i = 0; i < hexString.Length; i += 2)
 {
   string hs = hexString.Substring(i, 2);
   sb.Append(Convert.ToChar(Convert.ToUInt32(hs, 16)));
 }
    String ascii = sb.ToString();
    return ascii;
}

---

Is your datatype and parameter type nvarchar2?