QV9 DynamicUpdate delete problem with apostrophes

[#1]

QV9 works fine for inserting data containing apostrophes into qv tables, but I am having a problem removing data containing apostrophes.

The following code works when apostrophes are not involved, but doesnothing if the field contains apostrophes:

query = "DELETE FROM Name WHERE NameSelected='" & mySelections.Item(i).text & "'"
ActiveDocument.DynamicUpdateCommand(query)

Replacing the single quote with two single quotes SHOULD work, but does not:

tempstring = Replace(mySelections.Item(i).text, "'", "''")
query = "DELETE FROM Name WHERE NameSelected='" & tempstring & "'"
ActiveDocument.DynamicUpdateCommand(query)

Ideas?

[Anonymous]

Try to replace apostrophes with chr(39)

[#3]

Michael,

Thanks for the tip, but I've tried chr(39) every which way but up in the delete statements, to no avail.

Dynamic inserts work properly with embedded single quotes and no special handling (and, yes, I've tried replacing single quotes on the inserts and deletes with chr(39), double single quotes, chr(39)&chr(39), etcetera), but deletes simply do not work.

I've tried sticking chr(34)s (double quote) on the left and right (but not in the middle), and that does not help.

QlikView: do we have a bug? It appears so.

[#4]

Hi Kencho/Michael,

Not sure if the above question is resolved or was it a bug in earlier version and resolved now.. But this is how I got through it in QV11 version

My requiremnet is to delete records with a specific list filenames.

sub Deletefile()
 
Dim objADO
Dim objRs
Dim strSQL

'vCurrentSelections is variable with filenames to be deleted - separated by commas)
str= ActiveDocument.Variables("vCurrentSelections").GetContent().String

fstr=InStr(str,"Filename")
ct=len(str)

'inserting double quotes to match the syntax as similar to SQL
fnm=replace(fnml,",", chr(34)&","&chr(34))

AppPath = "S:\nostro management\IDLM\IDLM Tactical Tool\Phase I\After Run1\IDLM Database v1.1.accdb"

Set objADO = CreateObject("ADODB.Connection")

objADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & AppPath & ";"

'msgbox("delete * from Basedata where Filename in "&chr(34)& fnm &chr(34))
objADO.Execute("delete * from Basedata where Filename in ("&chr(34)& fnm &chr(34)&")")

objADO.Close

Msgbox("Files deleted" )

end sub

regards,

kamal