Inserting data with apostrophe into iOSSQLite table

Hi. I have the code below which inserts a record into an iOSSQLite table. This works fine during testing most of the time until the one time it threw an error. I found that one of the fields where the error occurred contained an apostrophe. I did a little reading that the approach for this is to use prepared statements. However, I don’t see SQLitePreparedStatement or iOSSQLitePreparedStatement as an option for iOS.

dim sqlAddIncorr as Text sqlAddIncorr = "INSERT INTO incorrect (ProgID, RefID, Test, AnswerCode, Correct, DomainNumber, " +_ "UsersChoice, Redeem, Question, Answer1, Answer2, Answer3, Answer4, Solution)" +_ "VALUES ('" + ProgID + "', '" + valID.ToText + "', '" + IncorrTestSet + "', " +_ "'" + CorrectAnswerCode.ToText + "', '" + IncorrCorrect + "', " +_ "'" + IncorrDomNum + "', '" + IncorrUserChoice + "', '" + IncorrRedeem + "', " +_ "'" + txtQuestion.Text + "', '" + txtAnswer1.Text + "', '" + txtAnswer2.Text + "', " +_ "'" + txtAnswer3.Text + "', '" + txtAnswer4.Text + "', '" + theSolution + "');" app.dbUser.SQLExecute(sqlAddIncorr)

Any suggestions on how to insert a field containing an apostrophe for iOS? We cannot use DatabaseRecord in iOS, right?

Escape the apostrophe ?

Hi Emile. I’m not fully sure what you mean by that. Explain?

dont do it that way at all
use the prepared statement forms as shown on http://developer.xojo.com/iossqlitedatabase

Dim sql As Text
sql = "INSERT INTO Team (Name, Coach, City) VALUES (?1, ?2, ?3)"
  
' Pass in values after sql instead of doing string replacement
Try
  Dim name As Text = "Flying Squirrels"
  Dim coach As Text = "Tim Roberts"
  Dim city As Text = "Springfield"
  ' ID is created automatically because it is a primary key
  DB.SQLExecute(sql, name, coach, city)
Catch e As iOSSQLiteException
  Dim err As Text = e.Reason
End Try

and you NEVER have to worry about special characters in the sql

That did the trick Norman, thanks! I knew there was a workaround to this and am sure I use this in some other app but couldn’t find it. Appreciate it!