SQL Server gebruikt paren van enkele aanhalingstekens om de s te identificerentart en het einde van een string. Het invoegen van 'Mrs Brown's Boys' in een databasetabel zal mislukken, aangezien de drie enkele aanhalingstekens twee tekenreeksen impliceren, waarvan er één onvolledig is. Een escape-teken is vereist voor de aposttouw achter Brown. In dit artikel wordt het gebruik van een aangepaste VBA-functie beschreven 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
Opname van apostrophes (of enkele aanhalingstekens) in een SQL-instructie levert de volgende fout op die wordt geretourneerd door de databasemanager (in dit geval voor de naam O'Dowd):
Er is een ontsnappingskarakter nodig, namelijk een dubbele aposttouw in plaats van een enkele. O ”Dowd is dus acceptabel voor de database. O'Dowd is het niet.
De functie
Waar capture-velden mogelijk een ap bevattenostrophe kan een aangepaste functie worden gebouwd om te worden geactiveerd vóór de update, waarbij het enkele aanhalingsteken wordt vervangen 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> Extra> Verwijzingen om naar ADO lib te verwijzenrarout.
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 Tot 100 x = InStr (x + 2, strWord, "'")' Vind de positie van apostrophes 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-functie
- Negeer de functie.
Sub IgnoreFunction () Roep ConnectDatabase strCriteria = Sheets ("Update"). Range ("B10") strSQL = "Invoegen in tblCrewMember (LastName) waarden ('" & strCriteria & "')" MsgBox strSQL & ". Dit SQL-item zal mislukken; let op de drie apostrophes. "Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Gebruik de functie
Sub UseFunction () Call ConnectDatabase strCriteria = Sheets ("Update"). Range ("B15") strCriteria = fRemoveApostrophe (strCriteria) strSQL = "Invoegen in tblCrewMember (LastName) waarden ('" & 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, starting op 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 is vatbaar voor crashen als uw computer bijna geen bronnen meer heeft. Tijdens het schrijven van deze oefening bevroor de spreadsheet van Excel, die nog niet was opgeslagen. Het codevenster reageerde gedeeltelijk, waardoor de werkmap als geheel kon worden gesloten. Het bleek dat de werkmap normaal opnieuw werd geopend met de inhoud en code compleet. Had het temporary en bronbestanden (maar al te vaak) beschadigd waren, zou het werk opnieuw moeten worden gedaan zonder een tool om 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 herstellen rar en sql-herstelsoftwareproducten. Voor meer informatie bezoek www.datanumen.com