OLEDB QUERY If user input has ' query fails.

[Ken-Barrie]

When an Access database is being updated and an apostrophy ' is embedded in a user input string, (txtArray[0].Text here), the query fails. I am using OLEDB. I am hoping I do not have to parse each user input searching for single quotes and probably other naughties like ". I tried searching past posts but it's hard to phrase a good search string looking for a little '

string SQL = "UPDATE Inventory SET" +
" Mask = " + Convert.ToInt32(mask) + "," +
" Field1= '" + txtArray[0].Text + "',"+ ...etc

[Ken-Barrie]

Well, seems I'll have to write to myself. After a bit of research and trial and error I came up with this code:

string SQL = "UPDATE Inventory SET" +
" Mask = " + Convert.ToInt32(mask) + "," +
" Field1= \"" + txtArray[0].Text + "\","+……

Works OK with ' ie if users enters text such as Ken's problem it now updates OK. However use of full quotes-eg inputing My "solution". in input string, still crashes it. So this is a partial fix!

[Ken-Barrie]

No one has picked this up, so I worked out a solution. There is a very useful “.Replace” method for strings which replaces all instances of a character eg \ or “. The code is correspondingly simple and short. You have to decide what characters to substitute for your naughty ones. I picked the “Thorn” character and the “Pilcrow” character.
See http://www.alanwood.net/demos/ansi.html

[code]
//Search for and replace characters in user input string that create errors in
//an SQL Update query. Replace Quote mark with Thorn character.
//Replace Backslash with Pilcrow character
private string ParseInput(string UserInput)
{
string sub1 = "\u00DE"; string sub2 = "\u00B6";
string search1 = "\""; string search2 = "\\";
string NewString = UserInput.Replace(search1,sub1);//Replace ALL quote marks
NewString = NewString.Replace(search2,sub2);//Replace ALL backslash characters
return NewString;
}
[\code]

There is a corresponding “inverseParse” method when reading from the database.