asked on

quoted string not properly terminated

Have an excel file I am importing into an Oracle database using a small C# application only problem is that if someone sticks an apostrophe in there it kills the entire process. I am doing an insert to a temp table and then using a package to do the final insert. Suggestions?

below is where I am parsing through the excel. The issue happens with the comments column.

while ((line = sr.ReadLine()) != null)
                {
                    cnt2 += 1;
                    row = line.Split(',');
                    ExternalAcct = row[2];
                    FirstCompDate = row[3];
                    LastCompDate = row[4];
                    CompCode = TranCompCode(row[5]);
                    Comments = row[20].Replace("'","");

                    if (ExternalAcct != "" && is_date(FirstCompDate) && is_date(LastCompDate) && CompCode != "")
                    {
                        // set up Oracle reader ---------------------
                        cnt += 1;
                        OraCmd.CommandText = "Insert into CIRC.TH_STC_Temp Values ("
                                           + cnt.ToString()
                                           + ", 0"
                                           + ", '" + CompCode
                                           + "', to_date('" + FirstCompDate + "','YYYYMMDD')"
                                           + ", to_date('" + LastCompDate + "','YYYYMMDD')"
                                           + ",'" + ExternalAcct
                                           + "', '" + row[5] 
                                           + "', '" + row[20] + "')";
                        OraCmd.ExecuteScalar();
                    }

Open in new window

ASKER CERTIFIED SOLUTION

Ron Malmstead

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ron Malmstead

example...

Comments = row[20].Replace("'","''");