CODEWITHSUNDEEP
c#ms-accessparameterized-query
user2147152
user2147152

Reputation: 31

How to deal with single or double quote in string variable in Access SQL expression

I wrote this line in c# to operate microsoft access database 

OleDbCommand sComm = new OleDbCommand
  ("Update TableToBeImport set TextDefault = '" + resxValue + "' 
    WHERE ControlName = '" + resxKey + "'" , c);

resxValue and resxKey are all string variable.

when the two string variables are simple such as "abc","xyz", things are OK, but when one string variable become complex, in my case, like this:

TIPS:

  1. To fix rate plan & rpt error use 'reprocess'

  2. To fix carrier,trunk group,trader error, use 'delete and reinsert'

the program throws an error, and when i doing debug, the sComm.CommandText is this form:

Update TableToBeImport set TextDefault = 'TIPS: \r\n1. To fix rate plan & rpt error use 'reprocess'\r\n2. To fix carrier,trunk group,trader error, use 'delete and reinsert'' WHERE ControlName = 'frmCDRQuery.label7.Text'

I know in c#, I can use @ before a string variable, so any escape character in it will be ignored. But in accesss, how can I get the same result?

Upvotes: 1

Views: 945

Answers (1)

Kane
Kane

Reputation: 16802

Does this work for you?

var resxValue = ""; // obviously this will be whatever the type and value that is required
var resxKey = ""; // obviously this will be whatever the type and value that is required

// I am assuming here that you are already programming with a `using` statement
using (OleDbConnection c = new OleDbConnection(""))
{
    using (OleDbCommand sComm = new OleDbCommand("UPDATE TableToBeImport SET TextDefault = @p0 WHERE ControlName = @p1", c))
    {
        sComm.Parameters.Add(new OleDbParameter("@p0", resxValue));
        sComm.Parameters.Add(new OleDbParameter("@p1", resxKey));

        // rest of your code here E.G., sComm.ExecuteNonQuery();
    }
}

Upvotes: 1

Related Questions