Unescaped single quote in data

Question

Unescaped single quote in data

mfeuti 24899

SSC Enthusiast

Points: 174
More actions
April 3, 2019 at 11:50 am

#2677834

Hello, I need some help with some code that I borrowed and am trying to make work. The issue is my data contains many names with apostrophes and I know I need to deal with it with by escaping the single quote (turn it into two single quotes)but I'm having difficulty doing that in the right location. Anyway I try produces the same error. I have copied the section of the code below, C#. Any help would be really appreciated.
Thank you.
//Write Data to Excel Sheet from DataTable dynamically
foreach (DataTable table in ds.Tables)
{
String sqlCommandInsert = "";
String sqlCommandValue = "";
foreach (DataColumn dataColumn in table.Columns)
{
sqlCommandValue += dataColumn + "],[";
}
sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue + ") VALUES(";
int columnCount = table.Columns.Count;
foreach (DataRow row in table.Rows)
{
string columnvalues = "";
for (int i = 0; i < columnCount; i++)
{
int index = table.Rows.IndexOf(row);
columnvalues += "'" + table.Rows[index].ItemArray + "',";
}
columnvalues = columnvalues.TrimEnd(',');
var command = sqlCommandInsert + columnvalues + ")";
Excel_OLE_Cmd.CommandText = command;
Excel_OLE_Cmd.ExecuteNonQuery();
}

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

handkot SSCertifiable Points: 5273 More actions · Answer 1

string value = table.Rows[index].ItemArray

columnvalues += “‘” +  value.replace("'", "''")+ “‘,”;

I Have Nine Lives You Have One Only
THINK!

mfeuti 24899 SSC Enthusiast Points: 174 More actions · Answer 2

If you are saying replace this section:

int index = table.Rows.IndexOf(row);

columnvalues += "'" + table.Rows[index].ItemArray + "',";

with the code provided above it produced an error. Is that what you meant?

handkot SSCertifiable Points: 5273 More actions · Answer 3

yes.

int columnCount = table.Columns.Count;

char[] charsToTrim = {','};

foreach (DataRow row in table.Rows)

{

string columnvalues = "";

for (int i = 0; i < columnCount; i++)

{

string value = row.ToString().Replace("'", "''");

columnvalues += "'" + value + "'";

}

columnvalues = columnvalues.TrimEnd(charsToTrim);

}

I think, there are a lot of errors

I would recommend ClosedXML

what error occurs ?

This reply was modified 5 years, 8 months ago by handkot.

I Have Nine Lives You Have One Only
THINK!

mfeuti 24899 SSC Enthusiast Points: 174 More actions · Answer 4

Actually there are no more specific errors that makes the package stop running or error BUT it is only populating the column headers in the Excel file and not the data from the rows.