Un-escaping characters present in recordset that is bound to control in Access 2003

Question

I have some data stored in SQL Server that contains apostrophes. Within the SQL Server table to apostrophe is escaped. For example the phrase "Roberts's thesis" is stored as "Robert''s Thesis". I use Access 2003 as a font end for the application and I use ADO to access the data stored in SQL Server using Stored Procedures.

The issue I am having is how to "un-escape" the double apostrophe when the data is retrieved using a recordset and then bound to a control. Here is some sample code.

  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset


  Set cnn = New ADODB.Connection
  cnn.ConnectionString = myConnectionString
  cnn.Open

  Set rs = New ADODB.Recordset
  Set rs.ActiveConnection = cnn
  rs.Source = "EXEC uspMyStoredProcedureName"

  rs.LockType = adLockOptimistic
  rs.CursorType = adOpenStatic
  rs.CursorLocation = adUseClient
  rs.Open

  Set ListControl.Recordset = rs

  Set rs = Nothing
  Set cnn = Nothing

Do I have to "loop" through the recordset manually and un-escape the apostrophe? Thank you.

Answer 1

You don't have to unescape anything. The doubled-apostrophe form is only used for string literals inside SQL statements. The actual value inserted in the database by INSERT... 'Robert''s Thesis'; is Robert's Thesis, and that is the value you'll get out when you read it from a recordset grabbed from a SELECT.

If whatever's inside uspMyStoredProcedureName is doing something weird to cause doubled apostrophes to get returned then it's broken and needs fixing; if you have Robert''s Thesis as an actual value in the database, then you've inserted broken data and you should be looking at fixing the code that's done that, and cleaning up the data.

Answer 2

If you have no possibility to change uspMyStoredProcedureName, then You will have to loop through the resultset. Otherwise, changing the select statement in the stored procedure from col to Replace(col, '''''', '''') would do the trick. Yes, there are six single quotes in the second argument and four in the third: Two enclose a string, and within that, each quote is doubled to escape it.

BTW: Why are the data stored with doubled quotes at all?