troubleshooting Question

Problem with ' Character and TextDelimiters with OLE as a Datasource

Avatar of mitsic
mitsic asked on
.NET ProgrammingMicrosoft SQL Server 2005Editors IDEs
7 Comments1 Solution316 ViewsLast Modified:
I have an issue with a OLE Text file that I am reading into a table then building sql statements from...
the problem is that any field that contains a ' character is getting messed up...

Here is my connection string:
mODBCcn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + config.AppSettings.Settings["ImportLoc"].Value.ToString() + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\"");

now, I build a schema file that looks like ths:

[import.txt]
ColNameHeader=True
Format=TabDelimited
DecimalSymbol=,
TextDelimiter='

Then

ODBCda = new OleDbDataAdapter("Select * from " + msrcFile, mODBCcn);
try
{
   ODBCda.Fill(mODBCdt);
    mODBCdt.TableName = "import";
    mODBCdt.DefaultView.AllowNew = false;
}

any row that has a field with ' in it..gets wacked..in other words if the columns are:
mfgr model descr list cost
and ' is in the description, the rest of the row has null values.

Thanks for the help.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros