SQL Server Er worden paren van enkele aanhalingstekens gebruikt om het begin en einde van een tekenreeks aan te geven. Het invoegen van 'Mrs Brown's Boys' in een databasetabel zal mislukken, omdat de drie enkele aanhalingstekens twee tekenreeksen impliceren, waarvan er één onvolledig is. Voor de apostrof na Brown is een escape-teken vereist. Dit artikel onderzoekt het gebruik van een aangepaste VBA-functie om deze anomalie op te lossen.
In dit artikel wordt ervan uitgegaan dat de lezer het ontwikkelaarslint heeft weergegeven en bekend is met de VBA-editor. Als dit niet het geval is, gebruik dan Google "Excel Developer Tab" of "Excel Code Window".
De term "database" is hier van toepassing op databases met "industriële sterkte", zoals SQL Server en Oracle.
Een voorbeeld van het werkboek dat in deze oefening wordt gebruikt, is te vinden hier.
De SQL-string
Het gebruik van apostrofen (of enkele aanhalingstekens) in een SQL-instructie resulteert in de volgende foutmelding van de databasebeheerder (in dit geval voor de naam O'Dowd):
Er is een escape-teken nodig, namelijk een dubbele apostrof in plaats van een enkele. Daarom is O”Dowd acceptabel voor de database. O'Dowd niet.
De functie
Als er in vastgelegde velden mogelijk een apostrof voorkomt, kan een aangepaste functie worden gemaakt die vóór de update wordt uitgevoerd en de enkele aanhalingsteken vervangt door een dubbele.
- Open een nieuwe werkmap;
- Noem het eerste blad "Update" en vul het als volgt in, gebruik uw eigen databasenaam, enz. Deze velden worden gebruikt om een verbindingsreeks op te bouwen naar SQL Server.
- Open het codevenster en voeg een module in. Gebruik de menu-items >Tools >References om naar ADO-bibliotheken te verwijzen.
Kopieer de onderstaande code naar de module. Dit maakt verbinding met de database.
Public connDB As New ADODB.Connection Public rs As New ADODB.Recordset Public strSQL As String Public strCriteria As String Sub ConnectDatabase () If connDB.State = 1 Then connDB.Connection On Error GoTo ErrConnect Dim strServer, strDBase, strUser, strPWD As String strServer = Sheets ("Update"). Range ("B2") strDBase = Sheets ("Update"). Range ("B3") strUser = Sheets ("Update"). Range ("B4") strPWD = Sheets (" Update "). Range (" B5 ") If strPWD>" "Then strConnectionstring =" DRIVER = {SQL Server}; Server = "& strServer &"; Database = "& strDBase &"; Uid = "& strUser &"; Pwd = "& strPWD &"; Time-out verbinding = 30; "Else strConnectionstring =" DRIVER = {SQL Server}; SERVER = "& strServer &"; Trusted_Connection = yes; DATABASE = "& strDBase 'Windows authenticatie Einde If connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Sub afsluiten ErrConnect: MsgBox Err.beschrijving Einde Sub
- Voeg de functie toe aan de module:
Functie fRemoveApostrophe(strWord As String) Dim n As Integer Dim x As Integer x = 0 For n = 0 To 100 x = InStr(x + 2, strWord, "'") 'Vind de positie van apostrofs If x = 0 Then Exit For If x > 0 Then strWord = Left(strWord, x - 1) & Chr(39) & Chr(39) & Right(strWord, Len(strWord) - (x)) End If Next n fRemoveApostrophe = strWord End Function
- Negeer de functie.
Sub IgnoreFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B10") strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')" MsgBox strSQL & ". This SQL entry will fail; note the three apostrofs." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Gebruik de functie
Sub UseFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B15") strCriteria = fRemoveApostrophe(strCriteria) strSQL = "Voeg in tabel tblCrewMember (Achternaam) waarden in ('" & strCriteria & "')" MsgBox strSQL & ". Deze SQL-invoer zal slagen en in de datatabel verschijnen als O'Dowd." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Vul het bijwerken werkblad als volgt, beginnend bij cel A8:
- Wijs de knoppen toe aan macro's IgnoreFunctie en Gebruik Functie respectievelijk
Het Resultaten
Een berichtvenster toont de resultaten; er wordt geen database fysiek bijgewerkt in deze oefening, maar als je dat wilt, zorg er dan voor dat de veldnamen compatibel zijn met je database, en voeg het VBA-statement conndb.execute (strSQL) toe
Herstellen van Excel crasht
Excel loopt vaak vast wanneer uw computer onvoldoende resources heeft. Tijdens het schrijven van deze oefening liep het Excel-spreadsheet, dat nog niet was opgeslagen, vast. Het codevenster reageerde gedeeltelijk, waardoor het mogelijk was het werkboek in zijn geheel te sluiten. Het bleek dat het werkboek normaal heropende met de inhoud en code compleet. Als de tijdelijke en bronbestanden (wat helaas maar al te vaak voorkomt) beschadigd waren geweest, had het werk opnieuw moeten worden gedaan, tenzij er een tool beschikbaar was om dit op te lossen. xlsx schade. In dit geval was het van weinig belang, maar het zou een potentiële ramp kunnen zijn voor grotere werkboeken.
Auteur Introductie:
Felix Hooker is een expert op het gebied van gegevensherstel DataNumen, Inc., de wereldleider in technologieën voor gegevensherstel, waaronder herstel rar en sql-herstelsoftwareproducten. Voor meer informatie bezoek www.datanumen.com