SQL Server använder par enkla citat för att identifiera start och slutet på en sträng. Att infoga "Mrs Brown's Boys" i en databastabell misslyckas eftersom de tre enkla citaten innebär två strängar, varav en är ofullständig. En flykt karaktär krävs för apostrophe efter Brown. Den här artikeln utforskar användningen av en anpassad VBA-funktion för att lösa denna anomali.
Den här artikeln förutsätter att läsaren har utvecklarbandet och är bekant med VBA Editor. Om inte, vänligen Google "Excel-fliken för utvecklare" eller "Excel-kodfönstret".
Termen "databas" här gäller "industristyrka" databaser som SQL Server och Oracle.
Ett exempel på arbetsboken som används i denna övning kan hittas här..
SQL-strängen
Inkludering av apostrophes (eller enstaka citat) i ett SQL-uttalande ger följande fel som returneras från databashanteraren (för namnet O'Dowd i det här fallet):
En flyktkaraktär behövs, eftersom det är en dubbel apostrophe istället för en enda. Således är O ”Dowd acceptabel för databasen. O'Dowd är det inte.
Funktionen
Där fångfält kan tänkas innehålla en apostrophe, en anpassad funktion kan byggas för att avfyras före uppdatering, och ersätter det enda offertet med ett dubbelt.
- Öppna en ny arbetsbok;
- Namnge det första arket "Uppdatera" och slutföra på följande sätt med ditt eget databasnamn etc. Dessa fält kommer att användas för att skapa en anslutningssträng till SQL Server.
- Öppna kodfönstret och sätt i en modul. Använd menyalternativen> Verktyg> Referenser för att referera till ADO librarut.
Kopiera koden nedan till modulen. Detta ansluter till databasen.
Offentlig anslutning Som ny ADODB.Anslutning Offentlig rs Som ny ADODB.Recordset Offentlig strSQL som sträng Offentlig strCriteria som sträng Sub ConnectDatabase () Om connDB.State = 1 Då connDB.Close 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 (" Uppdatering "). Område (" B5 ") Om strPWD>" "Då strConnectionstring =" DRIVER = {SQL Server}; Server = "& strServer &"; Databas = "& strDBase &"; Uid = "& strUser &"; Pwd = "& strPWD &"; Timeout för anslutning = 30; "Annars strConnectionstring =" DRIVER = {SQL Server}; SERVER = "& strServer &"; Trusted_Connection = ja; DATABASE = "& strDBase 'Windows-autentisering Slut om connDB.ConnectionTimeout = 30 connDB. Öppna strConnectionstring Avsluta Sub ErrConnect: MsgBox Err. Beskrivning End Sub Sub
- Lägg till funktionen i modulen:
Funktion fRemoveApostrophe (strWord As String) Dim n Som Integer Dim x As Integer x = 0 For n = 0 To 100 x = InStr (x + 2, strWord, "'")' Hitta position för apostrophes If x = 0 Gå sedan ut för 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-funktion
- Ignorera funktionen.
Sub IgnoreFunction () Ring ConnectDatabase strCriteria = Sheets ("Update"). Range ("B10") strSQL = "Insert in tblCrewMember (LastName) values ('" & strCriteria & "')" MsgBox strSQL & ". Denna SQL-post kommer att misslyckas; notera de tre apostrophes. "Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Använd funktionen
Sub UseFunction () Ring ConnectDatabase strCriteria = Sheets ("Update"). Range ("B15") strCriteria = fRemoveApostrophe (strCriteria) strSQL = "Infoga i tblCrewMember (LastName) värden ('" & strCriteria & "')" MsgBox strSQL & ". Denna SQL-post kommer att lyckas och visas i datatabellen som O'Dowd." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Slutför Uppdatering kalkylblad enligt följande, starting vid cellen A8:
- Tilldela knapparna till makron Ignorera funktion och UseFunction repektivt
Resultatet
En meddelanderuta visar resultaten; ingen databas uppdateras fysiskt i den här övningen, men om du vill göra det, se till att fältnamnen är kompatibla med din databas och lägg till använd VBA-uttalandet conndb.execute (strSQL)
Återställa från Excel kraschar
Excel är benägen att krascha när din dator tar slut på resurser. Under skrivningen av denna övning frös Excel-kalkylbladet ännu. Kodfönstret var delvis lyhört, vilket möjliggjorde stängning av arbetsboken som helhet. Som det visade sig öppnades arbetsboken normalt med innehållet och koden komplett. Hade tempotrary- och källfiler skadades (alltför ofta) skulle arbetet behöva göras om i avsaknad av ett verktyg för att lösa xlsx skada. Det var av liten betydelse i det här fallet, men det kan vara en potentiell katastrof för större arbetsböcker.
Författarintroduktion:
Felix Hooker är en dataåterställningsexpert i DataNumen, Inc., som är världsledande inom teknik för återställning av data, inklusive återhämta rar och mjukvaruprodukter för SQL-återställning. För mer information besök www.datanumen.com