none
Escaping double quotes in C#

    Question

  • Hi,

    I'm attempting to build a string in C#; it is a SQL command to be passed to an OleDbCmd with a view to inserting a row in a table.

    The statements:

    string strSQL = "INSERT INTO Bookings (Start, End, ResourceId) " +
                             "VALUES (";

    strSQL = strSQL + "CDate(\"" + txtStartDate.Text + "\"),";

    The result:
    "INSERT INTO Bookings (Start, End, ResourceId) VALUES (CDate(\"7/1/2009\"),"

    How should I be escaping double quotes in a string literal?

    (By way of alternatives, which may be suggested, I've already got Error Code -2147217900 trying to use parameters and also building "INSERT INTO Bookings (Start, End, ResourceId) VALUES(#7/1/2009#,#8/1/2009#,1);" which Access was perfectly happy with, supplied directly. )

    Any help would be greatly appreciated.

    Cheers

    Chas
    Thursday, January 01, 2009 11:24 AM

Answers

  • Quotes are not the problem. Your field name End is a reserved word in MS Access. To ensure that it is recognized as an identifier rather than a keyword, put it in square brackets.

    "INSERT INTO Bookings (Start, [End], ResourceId) VALUES(#7/1/2009#,#8/1/2009#,1);";

    http://support.microsoft.com/kb/286335

    • Edited by mexil Saturday, January 03, 2009 5:13 AM Added link to reserved words list
    • Proposed as answer by mexil Saturday, January 03, 2009 5:14 AM
    • Marked as answer by Chas Long Saturday, January 03, 2009 8:19 AM
    Saturday, January 03, 2009 5:12 AM

All replies

  • You can use single quotes instead of double quotes for SQL server.

    Note that writing queries this way is susceptible to SQL injection and is highly discouraged. If possible, package your db logic into stored procedures.

    --
    Madhur

    http://blogs.msdn.com/mahuja | Please mark the replies as answers if they help
    Thursday, January 01, 2009 12:12 PM
  • You're correctly escaping the double quotes (when you inspect a string in the debugger, the debugger will put '\' before any double quotes, but the string doesn't actually contain any backslashes).

    Anyway, that doesn't solve your problem. You should use single quotes, but that's not safe, so you better use parameters (that also solves the problem of handling differente date formats).
    Thursday, January 01, 2009 1:38 PM
  • You would have to use single quotes for SQL Server, unless you set QUOTED_IDENTIFIER OFF. Access will tolerate single quotes and I tried it but to no avail. I could go back to parameters as the SQL string building approach was only brought on because their use generated a similar error. I'm not that bothered by SQL injection in a simple Windows form application where sound validation should be an adequate protection.

    The connection should be OK as data can be read using it. Still stuck
    Friday, January 02, 2009 7:40 AM
  • Quotes are not the problem. Your field name End is a reserved word in MS Access. To ensure that it is recognized as an identifier rather than a keyword, put it in square brackets.

    "INSERT INTO Bookings (Start, [End], ResourceId) VALUES(#7/1/2009#,#8/1/2009#,1);";

    http://support.microsoft.com/kb/286335

    • Edited by mexil Saturday, January 03, 2009 5:13 AM Added link to reserved words list
    • Proposed as answer by mexil Saturday, January 03, 2009 5:14 AM
    • Marked as answer by Chas Long Saturday, January 03, 2009 8:19 AM
    Saturday, January 03, 2009 5:12 AM
  • The reason it works without square brackets in an MS Access Query, is that Access implicitly inserts the brackets. If you save your query in Access and re-open it, you will find that Access has inserted the square brackets for you.

    Apparently it does not do the same for queries executed using an OleDbCommand.
    Saturday, January 03, 2009 5:19 AM
  • Thanks Maxilius; problem solved.
    Saturday, January 03, 2009 8:19 AM