SQL Server Utilizza coppie di virgolette singole per identificare l'inizio e la fine di una stringa. L'inserimento di 'Mrs Brown's Boys' in una tabella di database non andrà a buon fine poiché le tre virgolette singole implicano due stringhe, una delle quali è incompleta. Per l'apostrofo dopo Brown è necessario un carattere di escape. Questo articolo esplora l'utilizzo 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
L'inserimento di apostrofi (o virgolette singole) all'interno di un'istruzione SQL genera il seguente errore restituito dal gestore del database (in questo caso per il nome O'Dowd):
È necessario un carattere di escape, ovvero un doppio apostrofo anziché uno singolo. Pertanto, O”Dowd è accettabile per il database. O'Dowd non lo è.
La funzione
Laddove i campi di acquisizione potrebbero potenzialmente contenere un apostrofo, è possibile creare una funzione personalizzata che si attivi prima dell'aggiornamento, sostituendo l'apostrofo singolo con un apostrofo doppio.
- 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. Utilizza le voci di menu >Strumenti >Riferimenti per aggiungere riferimenti alle librerie ADO.
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 Per n = 0 To 100 x = InStr(x + 2, strWord, "'") 'Trova la posizione degli apostrofi Se x = 0 Allora Esci Per Se x > 0 Allora strWord = Left(strWord, x - 1) & Chr(39) & Chr(39) & Right(strWord, Len(strWord) - (x)) Fine Se Successivo n fRemoveApostrophe = strWord Fine Funzione
- Ignora la funzione.
Sub IgnoreFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B10") strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')" MsgBox strSQL & ". Questa voce SQL non andrà a buon fine; notare i tre apostrofi." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Usa la funzione
Sub UseFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B15") strCriteria = fRemoveApostrophe(strCriteria) strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')" MsgBox strSQL & ". Questa voce SQL avrà successo e apparirà nella tabella dati come O'Dowd." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Completa il Aggiornanento foglio di lavoro come segue, a partire dalla cella A8:
- Assegna i pulsanti alle macro IgnoraFunzione and 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 tende a bloccarsi quando il computer esaurisce 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 dell'intera cartella di lavoro. Come si è poi scoperto, la cartella di lavoro si è riaperta normalmente con il contenuto e il codice completi. Se i file temporanei e sorgente fossero stati (cosa fin troppo frequente) danneggiati, il lavoro avrebbe dovuto essere rifatto in assenza di uno strumento per risolvere il problema. 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 recupera rar e prodotti software di recupero SQL. Per maggiori informazioni visita www.datanumen.com