String met '-teken bij SQL Insert

[tuning4you]

Mijn SQL is als volgt:
sSQL_INSERT = "INSERT INTO [klantgegevens] (Familienaam, Voornaam, txtAdresPat,txtPostcodePatiënt,txtgemeentenaam,id2,Geslacht,Huisdokter,geboortedatum) Values ('" & sVoornaam & "','" & sVoornaam & "','" & sStraat & "','" & sPostcode & "','" & sGemeente & "','" & sPatientNummer & "','" & sGeslacht & "','" & sHuisartsID_db & "'," & sGeboorteDatum & ")"

Wanneer je nu als Familienaam bijvoorbeeld D'Hoore hebt, dan krijg je een foutmelding. Je krijgt dan eigenlijk 'D'Hoore'.

Iemand een idee hoe ik dit kan oplossen?

 

[MerlinTheGreat]

Als je een afkappingsteken (of quote) in een tekst hebt en die in een sql wil gebruiken moet je de quote verdubbelen.

Voorbeeld:
in je sql statement komt

[FONT="Courier New"]D''Hoore[/FONT]

LET OP het zijn twee afkappingstekens, geen aanhalingsteken...
Je gebruikt hiervoor de replace functie:

[FONT="Courier New"]replace(Familienaam, "'", "''")[/FONT]

Best doe je dit voor elke gegeven waarin een afkappingsteken kan voorkomen.

 

[tuning4you]

Hoi Merlin. Dat werkt uitstekend, hartelijk bedankt.

Weet je ook toevallig hoe ik in mijn string een nullwaarde kan sturen.
Er wordt namelijk niet altijd een gemeente opgegeven en dan krijg ik een waarde '' die ik naar mijn database stuur, dit bezorgt een error.

 

[MerlinTheGreat]

Je kan proberen met vbNull. Dat is een voorgedefiniëerde constante die de waarde null heeft.

 

[tuning4you]

Hoe bedoel je precies?

Voorlopig heb ik het via een omweg opgelost. In mijn SQL staat bijvoorbeeld Values (" & sVoornaam & ").

Via een function wordt er gekeken indien de waarde sVoornaam leeg is, indien dit het geval is wordt dit vervangen door Null. Indien dit niet het geval is worden er twee ' links en rechts geplaatst van de waarde sVoornaam.
Beetje omslachtig vind ik maar weet het anders niet.

Dus vertaald naar een stringwaarde:
Geen voornaam --> Null
Wel voornaam --> 'Andy'

 

[MerlinTheGreat]

Je kan de Intrinsic If functie gebruiken.
Deze functie kan twee verschillende waarden teruggeven afhankelijk van de gestelde voorwaarde.
Syntax:

[FONT="Courier New"]Iif(Voorwaarde, waarde als voorwaarde voldoet, waarde als voorwaarde [COLOR="Red"]niet[/COLOR] voldoet)[/FONT]

Een voorbeeld:
Je wil de voornaam doorgeven als die ingevuld is, anders wil je de nullwaarde doorgeven. (jouw voorbeeld)

[FONT="Courier New"]" ... values (" & Iif(Len(sVoornaam), sVoornaam, vbNull) & ...[/FONT]

De functie Len(sVoornaam) krijgt de waarde True als de lengte groter is dan nul (niet leeg) en False als sVoornaam leeg is. Zodoende kunnen we de voorwaarde testen of er iets in sVoornaam zit en afhankelijk van het resultaat de string sVoornaam zelf doorgeven of de nullwaarde (vbNull)
Je kan natuurlijk ook van tevoren even de strings overlopen om te zien of er iets in zit.
Je kan zelfs een eigen functie aanmaken die je aanroept en waaraan je een defaultwaarde kan meegeven:

[FONT="Courier New"]Public Function fnNE(Invoer As String, Optional DefaultWaarde As Variant = vbNull) As Variant
  ' --------------------------------------------------
  ' Functie om te controleren of een string
  ' leeg is en zo ja een standaard waarde te geven
  ' --------------------------------------------------
  fnNE = Iif(Len(Invoer), Invoer, DefaultWaarde)
End Function[/FONT]

Ik gebruik hier het type variant om ook de vbNull te kunnen gebruiken. Als er geen defaultwaarde wordt meegegeven wordt automatisch vbNull teruggegeven voor een lege string. Dat kan je natuurlijk wijzigen...

Je gebruikt de functie zo:

[FONT="Courier New"]" ... values (" & fnNE(sVoornaam) & "," & fnNE(sFamilienaam) & "," & ...[/FONT]

Doordat defunctienaam kort is vraagt het niet veel extra tikwerk en als de string leeg is wordt automatisch de nullwaarde gebruikt.
Wil je bijvoorbeeld "n/a" als waarde bij een lege string, dan kan je de code wijzigen in:

[FONT="Courier New"]" ... values (" & fnNE(sVoornaam, "n/a") & "," & fnNE(sFamilienaam, "n/a") & "," & ...[/FONT]

:thumb:

 

[tuning4you]

Dat lukt toch niet zoals verwacht.
Ik heb een test uitgevoerd in mijn programma voor de gemeente.

Volgende code werd toegevoegd:
stest = IIf(Len(sGemeente), sGemeente, vbNull)

Mijn SQL is dan als het volgt:
sSQL_INSERT = "INSERT INTO [klantgegevens] (Familienaam, Voornaam, txtAdresPat,txtPostcodePatiënt,txtgemeentenaam,id2,Geslacht,Huisdokter,SortVoornaam,[Sortering familienaam],geboortedatum) Values (" & sFamilienaam & "," & sVoornaam & "," & sStraat & "," & sPostcode & "," & stest & ",'" & sPatientNummer & "','" & sGeslacht & "','" & sHuisartsID_db & "'," & sVoornaamSearch & "," & sFamilienaamSearch & "," & sGeboorteDatum & ")"



Wanneer sGemeente geen gegevens bevat krijg ik de waarde 1 in mijn database. Wanneer sGemeente wel gegevens bevat krijg ik een foutmelding, dit is waarschijnlijk omdat de string dan niet tussen ' ' staat.

 

[MerlinTheGreat]

Ik heb het nog even opgezocht en blijkt dat je aan een string geen null waarde kan toekennen...
Je kan wel nog een spatie invoegen, dan geeft de SQL ook geen foutmelding terug.
Dat van die ' rond de string klopt. SQL heeft die dingen nodig om een string af te bakenen. Ik heb daar niet bij stil gestaan. Mijn fout...
Maar als sGemeente reeds de ' voor en achter bevat kan dat normaal geen fout geven. Die ' blijven normaal behouden... Alleen als er helemaal niets tussen de beide ' staat kan je een fout krijgen omdat SQL dan een afgekapt commando voorgeschoteld krijgt. Dat is hetgeen je in je tweede post hierboven had.

Er wordt namelijk niet altijd een gemeente opgegeven en dan krijg ik een waarde '' die ik naar mijn database stuur, dit bezorgt een error.

SQL is op dat gebied een beetje raar.
Je kan ook je SQL string opbouwen zonder de ' in de gegevens zelf te stoppen, maar ze bij de komma's te plaatsen.

[FONT="Courier New"]" ... values ('" & fnNE(sVoornaam) & "','" & fnNE(sFamilienaam) & "','" & ... & "')"[/FONT]

Dan moet fnNE in geval van een lege string een spatie teruggeven:

[FONT="Courier New"]Public Function fnNE(Invoer As String, Optional DefaultWaarde As String = " ") As String
  ' --------------------------------------------------
  ' Functie om te controleren of een string
  ' leeg is en zo ja een standaard waarde te geven
  ' --------------------------------------------------
  fnNE = Iif(Len(Invoer), Invoer, DefaultWaarde)
End Function[/FONT]

Of met de Iif() functie rechtstreeks zoals in jouw voorbeeld:

[FONT="Courier New"]stest = IIf(Len(sGemeente), sGemeente, " ")[/FONT]

met de ' bij de komma's geplaatst zoals hierboven aangegeven.

 

[tuning4you]

Als ik bij een nullwaarde een spatie doorgeef dan komt er een spatie in mijn database dacht ik. En bij een datum in mijn database geeft me dit ook foutmeldingen.

Ik had het eigenlijk als volgt opgelost:

sGemeente = QuoteReplacer(sGemeente)
sGemeente = QuoteAdd(sGemeente)


Function QuoteReplacer(sString) As String
sReplace = sString
sReplace = Replace(sString, "'", "''")
QuoteReplacer = sReplace
End Function

Function QuoteAdd(sString) As String
If sString = "" Then
QuoteAdd = "Null"
Else
QuoteAdd = "'" & sString & "'"
End If
End Function


In mijn SQL gebruik ik dan geen ' meer !!
Ik vind het zeer omslachtig deze manier maar het kan precies niet anders.

 

[MerlinTheGreat]

Dat is ook een mogelijkheid. NULL is het SQL equivalent voor een nullwaarde.

Let wel op met je functie QuoteReplacer. Als er reeds een dubbele quote in de string zit worden dat er vier...
Misschien is het beter om eerst alle dubbele quotes ('') te vervangen door enkele quotes (') en daarna alle enkele quotes (') door dubbele ('') om het uiteindelijke resultaat te verkrijgen...
Het klinkt omslachtig, maar als je vier quotes na mekaar in je SQL krijgt komen er uiteindelijk twee in je database. Ik vermoed dat dat nou net niet de bedoeling is. Toch?

Je functie zou dan zo worden:

[FONT="Courier New"]Function QuoteReplacer(sString As String) As String
  Dim sReplace As String
  ' Eerst alle mogelijk reeds dubbel aanwezige
  ' quotes omzetten naar enkelvoudige
  sReplace = Replace(sString, "''", "'")
  ' Dan alle enkelvoudige quotes
  ' omzetten naar dubbele
  sReplace = Replace(sReplace, "'", "''")
  QuoteReplacer = sReplace
End Function[/FONT]

De lijn sReplace = sString is overbodig in je code aangezien je in de volgende lijn toch weer een nieuwe toewijzing doet van sString naar sReplace.
Zoals je ziet kan je in de Replace() functie de doelvariabele gelijk nemen aan de oorsprongvariabele. Dat spaart een hoop gejongleer met inhoud van een variabele naar een andere uit. Uiteraard moeten we in de eerste Replace van de input parameter van de functie vertrekken, daarna kunnen we telkens op de nieuwe variabele werken.
Best kan je ook alle variabelen en parameters van een type voorzien. Zonder typeaanduiding maakt VB er Variant van en die gebruiken meer geheugen, werken trager en moeten telkens door het programma omgezet worden naar het juiste type bij elke bewerking die er mee gebeurt.
Als je bovendien in VB aangeeft dat alle variabelen moeten gedefiniëerd, en dus ook getypeerd, moeten worden kan je nooit een programma compileren met variabelen die een tikfout in de naam bevatten of niet gedefiniëerd zijn. Beter voorkomen dan genezen.
Als je deze optie aan zet plaatst VB automatisch Option Explicit bovenaan in elke nieuwe module, form, ...

 

[tuning4you]

Merlin, terug thx voor de info.
De regel sReplace = sString was inderdaad overbodig. Ik had dit toegevoegd omdat ik in eerste instantie dacht een error te krijgen wanneer je toch geen ' in de string had. Maar heb dit getest zonder deze regel en dit werkt inderdaad ook.

Wat je zegt ivm de typeaanduiding is correct. Ik doe dit in mijn programma niet te veel en ik weet dat dit verkeerd is. Maar ik ben .Net wat aan het leren ondertussen en ik hoop dat ik mijn programma dan daarin kan herschrijven. Daar krijg ik dan ook info wanneer ik een bepaalde variabele niet definieer.

In elk geval is deze topic behandeld en zal ik dit als opgelost aanduiden.
Nogmaals bedankt !!!!