0

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

Walter Fabio Simoni
  • 5,671
  • 15
  • 55
  • 80

1 Answers1

1

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());
Martin Mulder
  • 12,642
  • 3
  • 25
  • 54
  • I tried to use Parametrized but i have a bad "syntax error" exception :\ – Walter Fabio Simoni May 05 '13 at 18:37
  • That is why I offered you two other options. But perhapse you could create another question, post your code with parameters and ask if somebody sees the error. A lot of people abondon the correct way to easy and stray from the right path. – Martin Mulder May 05 '13 at 18:40
  • I already ask for the parametrized whitout succes ! In addition, i tried your second solution ( the .replace()) with an sql Error too :( – Walter Fabio Simoni May 05 '13 at 19:02
  • 1) Where did you ask the question (link?) and 2) what is the error? – Martin Mulder May 05 '13 at 19:03
  • The link is here : http://stackoverflow.com/questions/16384635/syntax-error-on-a-sql-parametrized-update-command-c-sharp – Walter Fabio Simoni May 05 '13 at 19:07
  • And for the error message : " Command contains unreconized phrase/keywords" – Walter Fabio Simoni May 05 '13 at 19:07
  • I modified my answer. I think you should use single quotes to mark your literals. – Martin Mulder May 05 '13 at 19:20
  • To mark my litteral ? Directly on my SQL command ? eg : string strRequest = 'update ' + strNomTable.Trim() + ' set ' + 'evenotes = ' + '"' + m_strNote.ToString().Trim() + '"' + ' , ' + ' where eveNum = ' + '"' + strEvtNumeroString.Trim() + '"'; – Walter Fabio Simoni May 05 '13 at 19:22
  • `Literals` are the values you use inside your state. So the result of `strNomTable.Trim()` which you put in your SQL-string is a literal. If modified line 2 with a beter example. – Martin Mulder May 05 '13 at 19:27
  • I noticed a great bug in your statement: Remove the comma before the "where" (one line up). – Martin Mulder May 05 '13 at 19:30
  • With simple quote, i have a "Syntax Error." Message. I would like to use SQL Parametrized, but i have worked all day long in order to correct this but i didn't found the right solution. – Walter Fabio Simoni May 05 '13 at 19:34
  • For a Comma, it's a typo when i paste the code on the website. – Walter Fabio Simoni May 05 '13 at 19:34
  • Well... in that case, remove it from your question :) – Martin Mulder May 05 '13 at 19:34
  • I'd suggest you: 1) follow up the answers of your prev. question and this question (so single quotes, etc.) then 2) make an exact copy of your code which contain the error WIHTOUT typo's. If you do not you will repeatingly get the same answes from different people (about the comma, about double quotes, etc.) which do not help you. In short: give us a good starting point. – Martin Mulder May 05 '13 at 19:54