none
Erstellen von SQL-Strings zur Laufzeit

    Frage

  • Moin,

    für DoCMD.runSQL wird der String mit VBA zur Laufzeit erstellt, z. B.

    "UPDATE Tabelle SET spalte = 'text';"

    Für den Fall, dass text selbst ' enthält, nutze ich

    "UPDATE Tabelle SET spalte = ""text"";"

    Blöderweise muss ich nun feststellen, dass text auch " enthalten kann.

    Gibt es noch eine oder mehrere Möglichkeiten, um beide Zeichen an SQL-Strings zu übergeben?

    Gruß Tom

    Donnerstag, 8. Oktober 2015 08:39

Antworten

  • Hallo Tom!

    Du brauchst auf keinen Fehler zu warten. Lass ihn einfach nicht auftreten. ;)

    Wenn du die Replace-Variante ersetzt, musst du nur auf das im SQL-Ausdruck verwendete Textbegrenzungszeichen achten. Sollte das Zeichen vorkommen, wird es verdoppelt. Kommt es nicht vor, ändert sich durch Replace nichts.

    Teste doch einfach ein paar Varianten. Du wirst sehen, dass der SQL-Ausdruck immer korrekt ist.

    Textwert = "ab'c"
    Debug.Print Textwert, "Feld = '" & replace(Textwert, "'", "''") & "'"
    
    Textwert = "ab""c"
    Debug.Print Textwert, "Feld = '" & replace(Textwert, "'", "''") & "'"
    
    Textwert = "ab''c"
    Debug.Print Textwert, "Feld = '" & replace(Textwert, "'", "''") & "'"

    mfg
    Josef


    Code-Bibliothek für Access-Entwickler
    AccUnit - Testen von Access-Anwendungen
    Virtueller Access-Stammtisch



    • Bearbeitet Josef Pötzl Montag, 12. Oktober 2015 07:35
    • Als Antwort markiert tom krist Montag, 12. Oktober 2015 08:31
    Montag, 12. Oktober 2015 07:34

Alle Antworten

  • Hallo Tom,

    eigentlich musst Du nur ein im Wert vorkommendes Hochkomma durch zwei Hochkommas ersetzen. Die Verdopplung der Anführungszeichen ist VB geschuldet, da die Zeichenbegrenzung der Variablen an sich auch durch Anführungszeichen gemacht wird. Das musst Du aber auch nur machen, wenn Du das hart so im Code stehen hast. Ein Anführungszeichen in einer Variablen macht nichts aus.

    "UPDATE Tabelle SET spalte = 'Das '' ist ein ""Text"" mit Anführungszeichen "" und Hochkomma ''.';"

    Schließ den Wert also in Hochkommata ein '...' und verdoppele für den Wert selbst alle Hochkommata.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    Donnerstag, 8. Oktober 2015 09:20
    Moderator
  • Hallo,

    ich empfehle dringend mit SQL-Parametern zu arbeiten, dann sollte auch das Escapen von Hochkommas u.ä. automatisch erfolgen.

    Hier wird das auch erklärt: ADO Tutorial inkl. SQL-Parametern

    Besten Gruß

    Donnerstag, 8. Oktober 2015 09:57
  • Hallo Stefan,

    um ehrlich zu sein, versteh ich Deinen Tip nicht, deshalb konkret für das Beispiel mit der Variablen strWert, diese soll  ' (was mit der Variante ja auch schon gelingt) oder auch " enthalten dürfen, wie müsste ich das dann formulieren:

    strSQL = strSpalte & " = """ & strWert & """;"
    DoCmd.RunSQL  "update " & strTabelle & " set " & strSQL

    Hallo Sensenmann,

    ich arbeite hier mit VBA, kann zwar ADO nutzen, aber nicht ADO.NET oder?

    Gruß Tom

    Donnerstag, 8. Oktober 2015 10:42
  • Hallo,
     
    tom krist wrote:
     
    > um ehrlich zu sein, versteh ich Deinen Tip nicht, deshalb konkret für das Beispiel mit der Variablen strWert, diese
    > soll  ' (was mit der Variante ja auch schon gelingt) oder auch " enthalten dürfen, wie müsste ich das dann
    > formulieren:
    >
    > strSQL = strSpalte & " = """ & strWert & """;"
    > DoCmd.RunSQL  "update " & strTabelle & " set " & strSQL
     
    Du kannst in Jet(ACE)-SQL sowohl Single (') als auch Double Quote (") als
    Begrenzungszeichen verwenden. Fuer beides gilt: Wenn das verwendete Zeichen
    im Text ebenfalls vorkommt, muss es verdoppelt werden. Am besten eignet
    sich dafuer die Replace-Funktion:
     
    strSQL = strSpalte & " = '" & Replace(strWert, "'", "''") & "';"
    

     
    Und dann verwende statt des Makrointerpreters (DoCmd.RunSQL) besser die
    .Execute-Methode des DB-Objekts, also
     
    Dim Db As DAO.Database
    Set Db = CurrentDb
    strSQL = ...
    Db.Execute strSQL, dbFailOnError
    


    Gruss - Peter
     
    --
     

    Donnerstag, 8. Oktober 2015 13:45
    Moderator
  • Hallo!

    [OT]

    Kurz noch der Vorschlag von SensenMannLE mit Access/VBA-Mitteln:

    Dim UpdateParamSql As String
    UpdateParamSql = "Parameters NeuerWert varchar(255), FilterID int;" & _
                     " UPDATE Tabelle SET spalte = [NeuerWert] where ID = [FilterID]"
    
    With CurrentDb.CreateQueryDef("", UpdateParamSql)
       .Parameters("FilterID") = 13
       .Parameters("NeuerWert") = "Text"
       .Execute dbFailOnError
       Debug.Print .RecordsAffected; "DS wurde geändert"
       .Close
    End With

    Bei dieser Variante muss man sich in VBA nicht um die Konvertierung der Werte nach Text kümmern. Alternativ zur dynamischen Parameter-Abfrage über CreateQueryDef könnte man mit CurrentDb.QueryDefs("AbfrageName") eine gespeicherte Abfrage verwenden.

    Wenn man den oben gezeigten Code in eine Hilfsprozedur steckt, ist der relativ übersichtlich zu verwenden.

    Beispiel: Insert oder Update als Parameter-Abfrage

    mfg
    Josef


    Code-Bibliothek für Access-Entwickler
    AccUnit - Testen von Access-Anwendungen
    Virtueller Access-Stammtisch


    Samstag, 10. Oktober 2015 07:38
  • Hallo tom!

    Das Problem mit dem Verdoppeln der Anführungszeichen wirst Du immer haben, wenn Du SQL-Statements per Code zusammensetzt. Ich habe mir aus diesem Grund eine kleine Funktion geschrieben, die die notwendige Ersetzung für mich übernimmt:

    Public Function DoubleQuotes(strSQL As String) As String
        
        DoubleQuotes = Replace(strSQL, "'", "''")
        
    End Function

    Diese Funktion packst Du in ein Modul. Die Funktion ist Public deklariert und kann somit überall in Deinem Code eingesetzt werden.

    Jetzt musst Du Dir nur noch angewöhnen, dass Du alle SQL-Statements, die Du per VBA ausführen lässt, vorher durch diese Funktion jagst. Das gilt insbesondere dann, wenn Du Eingaben des Benutzers im SQL-Statement hast. Schnell ist es passiert, dass im Feld für die Eingabe des Namen z.B. der Wert "D'Agonstini" auftaucht. Durch den konsequenten Einsatz dieser Funktion vermeidest Du solche Probleme künftig.

    HTH


    Thomas@Team-Moeller.de
    Blog: Blog.Team-Moeller.de
    Homepage: www.Team-Moeller.de



    Samstag, 10. Oktober 2015 11:41
  • Moin in die Runde,

    war paar Tage offline, bin aber positiv überrascht über Eure Vorschläge.

    Da ich nicht vorhersehen kann, ob in den mit Textdateien übermittelten Aktualisierungen (K2-Format) für die DB eines oder auch beide Zeichen vorkommen und ich weiteren Problemen vorbeugen möchte, habe ich mich entschieden, diese zu eliminieren.

    Angesichts der zu verarbeitenden Datenmenge, bei denen Aktualisierungen einzelner (Memo-)Felder durchaus 10 Textseiten umfassen können, habe ich mich entschieden, dies über reguläre Ausdrücke zu bewerkstelligen wie folgt:

    Sub test() Dim strWert As String strWert = "Ein beliebiger Text optional mit 'single quotes' und/

    oder mit ""double quotes""." Call convertValues(strWert) MsgBox strWert End Sub Sub convertValues(ByRef strWert As String) Set re = New RegExp re.Pattern = "[" & Chr(34) & Chr(39) & "]" re.IgnoreCase = False re.Global = True strWert = re.Replace(strWert, "") End Sub

    Ich hoffe, dies ist nicht die schlechteste Lösung.

    Besten Dank für Eure Anregungen

    Gruß Tom

    Sonntag, 11. Oktober 2015 16:48
  • Hallo!

    Wenn du mit VBA eine SQL-Anweisung erzeugst, musst du nur darauf achten, ob das Textbegrenzungszeichen in den Texten enthalten ist. 
    Wenn du ' als Textbegrenzung verwendest, ist es vollkommen egal, ob ein " im Text enthalten ist.  Du musst nur die ' verdoppeln. (Im Prinzip ist das wie in VBA, wenn du über den VBA-Editor ein " in einer Textvariablen speichern willst.)

    Zu deinem Lösungsansatz: wenn im Text z. B. O'Neill steht - willst du daraus ONeill erzeugen. Ist das gleichwertig in der Weiterverwendung? Falls das nur ein Kompromiss ist, würde ich eher den Ansatz von Thomas wählen. Das ist nicht aufwendiger und ändert den Inhalt nicht.

    Vielleicht hilft auch SQL-Text für Filterbedingung  

    mfg
    Josef


    Code-Bibliothek für Access-Entwickler
    AccUnit - Testen von Access-Anwendungen
    Virtueller Access-Stammtisch

    Sonntag, 11. Oktober 2015 17:32
  • Moin Josef,

    ("Wenn du mit VBA eine SQL-Anweisung erzeugst, musst du nur darauf achten, ob das Textbegrenzungszeichen in den Texten enthalten ist." ) .. und genau das kann ich nicht.

    = "update spalte = '" & Textdatei.liesZeile & "';"

    Ich kann nicht vorhersehen, ob bzw. welches Zeichen in der Zeile enthalten ist, denkbar ist sogar, dass beide Zeichen gleichzeitig enthalten sind. Wären diese alle über sogenannte Protypes (\00)  maskiert, wie eigentlich vorgesehen, wäre das auch kein Problem, nur hin und wieder meint einer der Editoren, die Zeichen selbst verwenden zu müssen.

    Der Informationsverlust durch das Entfernen ist gleich Null bzw. vernachlässigbar für Fälle, wie von Dir beschrieben. Was bleibt, ist allerdings der enorme Rechenaufwand, um in mehreren Dutzend MBs vielleicht ein Hochkomma oder zwei double quotes zu finden und zu löschen. Vielleicht wäre es sinnvoller, es auf einen Fehler ankommen zu lassen und diesen dann zu behandeln.

    Gruß Tom


    • Bearbeitet tom krist Montag, 12. Oktober 2015 07:01 Änderung
    Montag, 12. Oktober 2015 07:00
  • Hallo Tom!

    Du brauchst auf keinen Fehler zu warten. Lass ihn einfach nicht auftreten. ;)

    Wenn du die Replace-Variante ersetzt, musst du nur auf das im SQL-Ausdruck verwendete Textbegrenzungszeichen achten. Sollte das Zeichen vorkommen, wird es verdoppelt. Kommt es nicht vor, ändert sich durch Replace nichts.

    Teste doch einfach ein paar Varianten. Du wirst sehen, dass der SQL-Ausdruck immer korrekt ist.

    Textwert = "ab'c"
    Debug.Print Textwert, "Feld = '" & replace(Textwert, "'", "''") & "'"
    
    Textwert = "ab""c"
    Debug.Print Textwert, "Feld = '" & replace(Textwert, "'", "''") & "'"
    
    Textwert = "ab''c"
    Debug.Print Textwert, "Feld = '" & replace(Textwert, "'", "''") & "'"

    mfg
    Josef


    Code-Bibliothek für Access-Entwickler
    AccUnit - Testen von Access-Anwendungen
    Virtueller Access-Stammtisch



    • Bearbeitet Josef Pötzl Montag, 12. Oktober 2015 07:35
    • Als Antwort markiert tom krist Montag, 12. Oktober 2015 08:31
    Montag, 12. Oktober 2015 07:34
  • Hallo Josef,

    nicht schlecht!

    meine Testdatei:

    asdhjasdg'gkjhk' ghj
    ghfhg "tzut" hkjh
    kljhkj O'Neill hjgj "vghjgj" gkuhkjuh

    ergibt mit

    Debug.Print "Feldname = '" & Replace(dat.readline, "'", "''") & "'"
    Debug.Print "Feldname = '" & Replace(dat.readline, "'", "''") & "'"
    Debug.Print "Feldname = '" & Replace(dat.readline, "'", "''") & "'"
    

    Feldname = 'asdhjasdg''gkjhk'' ghj'
    Feldname = 'ghfhg "tzut" hkjh'
    Feldname = 'kljhkj O''Neill hjgj "vghjgj" gkuhkjuh'

    Kleiner Schönheitsfehler, damit hast Du Deinen O'Neill verhunzt, aber das merk ich mir trotzdem, danke.

    An die anderen: falls Ihr das so ähnlich habt versucht, mir nahezubringen, muss ich mich entschuldigen, bin ich nicht hintergestiegen.

    Gruß Tom

    Montag, 12. Oktober 2015 08:31
  • Hallo!

    > Kleiner Schönheitsfehler, damit hast Du Deinen O'Neill verhunzt
    Warum? Der Text ist nur in der SQL-Anweisung als O''Neill dargestellt, damit der SQL-Interpreter erkennt, dass das ' nach dem O nicht das Ende vom Text ist. 

    > An die anderen: falls Ihr das so ähnlich habt versucht, mir nahezubringen, ...

    Vergleich einmal die Zeile von Peter mit dem letzten Vorschlag:

    strSQL = strSpalte & " = '" & Replace(strWert, "'", "''") & "';"

    Oder der Vorschlag von Thomas:

    DoubleQuotes = Replace(strSQL, "'", "''")

    ... es kommt immer der Vorschlag das ' mittels Replace zu verdoppeln. ;-)

    Bei Thomas seiner Prozedur könnte man eventuell den Parameternamen strSQL gegen strWert ändern, weil er damit sicher keine fertige SQL-Anweisung mit diese Prozedur ändern wird. ;)


    BTW: Meine bevorzugte Schreibweise ist 

    Textwert = "ab'c"
    Debug.Print Textwert, "Feld = " & SqlTools.TextToSqlText(Textwert)
    Debug.Print Textwert, "Feld = " & SqlTools.ConvertToSqlText(Textwert, SQL_Text)

    Beachte das fehlende ' nach dem = im VBA-Code. Ich lasse mir lieber den fertigen Text-Ausdruck inkl. Begrenzungszeichen von einer Hilfsfunktion erstellen, dann kann ich das im Code nicht übersehen.

    mfg
    Josef


    Code-Bibliothek für Access-Entwickler
    AccUnit - Testen von Access-Anwendungen
    Virtueller Access-Stammtisch





    Montag, 12. Oktober 2015 09:00
  • Moin Josef,

    i denk, i habs jetzt verstanden.

    Auch wenn die anderen dasselbe meinten, Deine Didaktik passte halt besser zu meinem IQ.

    Trotzdem Gruß an alle, Tom

    Dienstag, 13. Oktober 2015 07:37