SQL Server utilizza coppie di virgolette singole per identificare la start e fine di una stringa. L'inserimento di "Mrs Brown's Boys" in una tabella di database fallirà poiché le tre virgolette singole implicano due stringhe, una delle quali è incompleta. Per l'ap è richiesto un carattere di escapeostrophe dopo Brown. Questo articolo esplora l'uso di una funzione VBA personalizzata per risolvere questa anomalia.
Questo articolo presuppone che il lettore abbia visualizzato la barra multifunzione dello sviluppatore e abbia familiarità con l'editor VBA. In caso contrario, Google "Excel Developer Tab" o "Excel Code Window".
Il termine "database" qui si applica a database "industriali" come SQL Server e Oracle.
È possibile trovare un esempio della cartella di lavoro utilizzata in questo esercizio Qui..
La stringa SQL
Inclusione di apostrophes (o virgolette singole) all'interno di un'istruzione SQL fornisce il seguente errore restituito dal gestore database (per il nome O'Dowd in questo caso):
È necessario un carattere di fuga, essendo un doppio apostrophe invece di uno solo. Pertanto, O”Dowd è accettabile per il database. O'Dowd non lo è.
La funzione
Dove i campi di acquisizione potrebbero plausibilmente contenere un apostrophe, è possibile creare una funzione personalizzata da attivare prima dell'aggiornamento, sostituendo la virgoletta singola con una doppia.
- Apri una nuova cartella di lavoro;
- Denominare il primo foglio "Aggiorna" e completare come segue, utilizzando il nome del proprio database, ecc. Questi campi verranno utilizzati per creare una stringa di connessione a SQL Server.
- Apri la finestra del codice e inserisci un modulo. Utilizzare le voci di menu >Strumenti >Riferimenti per fare riferimento a ADO librarfuori.
Copia il codice qui sotto nel modulo. Questo si connette al 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.Close On Error GoTo ErrConnect Dim strServer, strDBase, strUser, strPWD As String strServer = Fogli("Aggiorna").Range("B2") strDBase = Fogli("Aggiorna").Range("B3") strUser = Fogli("Aggiorna").Range("B4") strPWD = Fogli(" Aggiorna").Range("B5") If strPWD > "" Then strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & ";Timeout connessione=30;" Else strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" & strDBase 'Autenticazione Windows End If connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Exit Sub ErrConnect: MsgBox Err.Description End Sub
- Aggiungi la funzione nel modulo:
Funzione fRemoveApostrophe(strWord As String) Dim n As Integer Dim x As Integer x = 0 For n = 0 To 100 x = InStr(x + 2, strWord, "'") 'Trova posizione di 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 fRimuoviApostrophe = strWord End Function
- Ignora la funzione.
Sub IgnoreFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B10") strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')" MsgBox strSQL & ". Questa voce SQL fallire; notare i tre apostrophes." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Usa la funzione
Sub UseFunction() Chiama ConnectDatabase strCriteria = Sheets("Aggiorna").Range("B15") strCriteria = fRemoveApostrophe(strCriteria) strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')" MsgBox strSQL & ". Questa voce SQL avrà esito positivo e apparirà nel datatable come O'Dowd." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Completa il Aggiornanento foglio di lavoro come segue, starting alla cella A8:
- Assegna i pulsanti alle macro IgnoraFunzione e UsaFunzione rispettivamente
I risultati
Una finestra di messaggio mostrerà i risultati; nessun database viene aggiornato fisicamente in questo esercizio ma, se lo desideri, assicurati che i nomi dei campi siano compatibili con il tuo database e aggiungi usa l'istruzione VBA conndb.execute(strSQL)
Ripristino dagli arresti anomali di Excel
Excel è soggetto a arresti anomali quando il computer sta esaurendo le risorse. Durante la stesura di questo esercizio, il foglio di calcolo di Excel, non ancora salvato, si è bloccato. La finestra del codice era parzialmente reattiva, consentendo la chiusura della cartella di lavoro nel suo insieme. Come si è scoperto, la cartella di lavoro si è riaperta normalmente con il contenuto e il codice completi. Aveva il temporary e i file sorgente sono stati (troppo frequentemente) danneggiati, il lavoro avrebbe dovuto essere rifatto in assenza di uno strumento per risolvere danno xlsx. In questo caso era di scarsa importanza, ma potrebbe essere un potenziale disastro per cartelle di lavoro più grandi.
Introduzione dell'autore:
Felix Hooker è un esperto di recupero dati in DataNumen, Inc., che è il leader mondiale nelle tecnologie di recupero dati, tra cui recuperare rar e prodotti software di recupero SQL. Per maggiori informazioni visita www.datanumen.com