CODEWITHSUNDEEP
c#sqloledbquotesvisual-foxpro
Walter Fabio Simoni
Walter Fabio Simoni

Reputation: 5729

Double quotes with Sql Update

I'm trying to update a database with OleDb and .Net4.5.

My updates are working good , even if i use simple quote on a filed, but, when i input a double quote on a field, oledb raise an exception because of this double quote.

Here is a an example of a request :

string strRequest = "update " + strNomTable.Trim() + " set "
+ "evenotes = " + '"' + m_strNote.ToString().Trim() + '"' 
+ " where eveNum = " + '"' + strEvtNumeroString.Trim() + '"';

Have you an idea how i could avoid simple and double quotes ? Note : I tried to use SQL Parametrized updates, bu my DataBase don't appear to support this.

Thanks a lot,

Best regards,

Nixeus

Upvotes: 0

Views: 2505

Answers (1)

Martin Mulder
Martin Mulder

Reputation: 12954

A few options come to mind. Since I do not know what kind of database you are using, I am just guessing:

  1. Use parameters. I know you have tried it, but I would suggest to try again. If it failes, try the following:
  2. Remove the comma for the "where" (one line up!).
  3. Change all your double quotes inside your SQL-statement into single quotes. Literal text should be quotes like "'" + m_strNote.ToString().Trim() + "'" and not '"' + m_strNote.ToString().Trim() + '"'
  4. Replace all single single quotes (') in your values with double single quotes (''): "'" + m_strNote.ToString().Trim().Replace("'", "''") + "'"

If you combine option 2 till 4 you will get this:

string strRequest = "update " + strNomTable.Trim() + " set "
+ "evenotes = '" + m_strNote.ToString().Trim().Replace("'", "''") + "' "
+ "where eveNum = '" + strEvtNumeroString.Trim().Replace("'", "''") + "'";

Visual Fox Pro Database and OleDbParameters

You can use OleDbParameters. Start the name with an @. So: 

OleDbCommand command = new OleDbCommand(
    "update " + strNomTable.Trim() + " set "
    + "evenotes = @evenotes "
    + "where eveNum = @eveNum");
command.Parameters.AddWithValue("@evenotes", m_strNote.ToString().Trim());
command.Parameters.AddWithValue("@eveNum", strEvtNumeroString.Trim());

Upvotes: 1

Related Questions