Apostrophe / String^

[sambos]

Hi,

I'm having some issues around dealing with an apostrophe in a String^.

I have a dataGridView that has a series of names in it. These names are exported to an excel file using OLEDB. I can do this easily but there's one name that has an apostrophe and it's getting hung up on dealing with it.

Assmue the name is: O'Riley

This is what I have:

for (int i = 0; i<dataGridView1->Rows->Count; i++)
{
String^ Name = Convert::ToString(dataGridView1->Rows[i]->Cells[2]->Value);

cmd->CommandText = "INSERT INTO [table](Name) VALUES('" + Name + "');"; 
			
cmd->ExecuteNonQuery();
}

The error message I get is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error (missing operator) in query expression ''O'Riely'.

I can't seem to take the string in the dataGridView as verbatim. Suggestions?

[Thomas1965]

Try escaping the ' with an \ so O'Riely becomes O\'Riely

[sambos]

Hi Thomas1965,

Thanks for your reply.

I've tried this way too but still get another problem:

for (int i = 0; i<dataGridView1->Rows->Count; i++)
{
String^ Name = Convert::ToString(dataGridView1->Rows[i]->Cells[2]->Value);

cmd->CommandText = "INSERT INTO [table1] (Name) VALUES (@Name)";

cmd->Parameters->AddWithValue("@Name", Name);
cmd->ExecuteNonQuery();

}

This problem only copies the first name in my list of 105 names to the spreadsheet. For example, Smith is placed in every cell of the column (x105 rows)

The O'Riely name is on the 91st row of my dataGridView. If I change the following line in the above code:

String^ Name = Convert::ToString(dataGridView1->Rows[i]->Cells[2]->Value);

to

String^ Name = Convert::ToString(dataGridView1->Rows[90]->Cells[2]->Value

the name O'Riely appears but is copied for every cell like the Smith example stated above.

I'm very confused...Not sure why it isn't looping through all the different rows of the dataGridView and therefore changing the String^ Name....