Solved How to handle ' in sql string (1 Viewer)

John Sh

Active member
Local time
Tomorrow, 07:36
Joined
Feb 8, 2021
Messages
632
I fave an sql string that takes it's input from a couple of tempvars.
The input for one of the tempvars is Sp. 'Yarawah' from a table field, so the tempvars.value presented to the sql string is "Sp. 'Yarawah'"
The single inverted commas are part of the scientific name.
db.execute crashes with this input.
How do I present this data to the sql string
Code:
sQry = "INSERT INTO images ( Genus, SpeciesEpithet, [Image], Collection, Author, sub, infrafamily)  " & _
           "SELECT A.Genus, A.SpeciesEpithet, A.[image], 'Collier' AS exp1, 'Barry Collier' as exp2, A.subspecies, a.infrafamily " & _
           "FROM Collier_Collection as A " & _
           "WHERE A.Genus='" & [TempVars]![Genus] & "' " & _
           "AND A.SpeciesEpithet='" & [TempVars]![Species] & "';"
    oDB.Execute sQry, dbFailOnError
 
Code:
sQry = "INSERT INTO images ( Genus, SpeciesEpithet, [Image], Collection, Author, sub, infrafamily)  " & _
           "SELECT A.Genus, A.SpeciesEpithet, A.[image], 'Collier' AS exp1, 'Barry Collier' as exp2, A.subspecies, a.infrafamily " & _
           "FROM Collier_Collection as A " & _
           "WHERE A.Genus='" & Replace([TempVars]![Genus],"'","''") & "' " & _
           "AND A.SpeciesEpithet='" & Replace([TempVars]![Species],"'", "''") & "';"
    oDB.Execute sQry, dbFailOnError
 
Thank you.
I had tried a few variations of that but not that particular one.
John
 

Users who are viewing this thread

Back
Top Bottom