Zoeken…
Opmerkingen
De voorbeelden in dit onderwerp gebruiken voor de duidelijkheid vroege binding en vereisen een verwijzing naar de Microsoft ActiveX Data Object xx-bibliotheek. Ze kunnen worden geconverteerd naar late binding door de sterk getypte verwijzingen te vervangen door Object en waar mogelijk het maken van objecten te vervangen met behulp van New door CreateObject .
Verbinding maken met een gegevensbron
De eerste stap bij het openen van een gegevensbron via ADO is het maken van een ADO- Connection . Dit wordt meestal gedaan met behulp van een verbindingsreeks om de gegevensbronparameters op te geven, hoewel het ook mogelijk is om een DSN-verbinding te openen door de DSN, het gebruikers-ID en het wachtwoord door te geven aan de .Open methode.
Merk op dat een DSN niet vereist is om verbinding te maken met een gegevensbron via ADO - elke gegevensbron met een ODBC-provider kan worden verbonden met de juiste verbindingsreeks. Hoewel specifieke verbindingsreeksen voor verschillende providers buiten het bestek van dit onderwerp vallen, is ConnectionStrings.com een uitstekende referentie voor het vinden van de juiste string voor uw provider.
Const SomeDSN As String = "DSN=SomeDSN;Uid=UserName;Pwd=MyPassword;"
Public Sub Example()
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
'... Do work.
database.Close 'Make sure to close all database connections.
End If
End Sub
Public Function OpenDatabaseConnection(ConnString As String) As ADODB.Connection
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = New ADODB.Connection
With database
.ConnectionString = ConnString
.ConnectionTimeout = 10 'Value is given in seconds.
.Open
End With
OpenDatabaseConnection = database
Exit Function
Handler:
Debug.Print "Database connection failed. Check your connection string."
End Function
Merk op dat het databasewachtwoord in de bovenstaande voorbeeldreeks alleen voor de duidelijkheid is opgenomen in de verbindingsreeks. Best practices zouden voorschrijven dat databasewachtwoorden niet in code worden opgeslagen. Dit kan worden bereikt door het wachtwoord via gebruikersinvoer te nemen of Windows-verificatie te gebruiken.
Records ophalen met een zoekopdracht
Query's kunnen op twee manieren worden uitgevoerd, die beide een ADO Recordset object retourneren dat een verzameling geretourneerde rijen is. Merk op dat in beide onderstaande voorbeelden de OpenDatabaseConnection functie OpenDatabaseConnection van het voorbeeld Verbinding maken met een gegevensbron wordt gebruikt om het kort te houden. Vergeet niet dat de syntaxis van de SQL die aan de gegevensbron wordt doorgegeven, providerspecifiek is.
De eerste methode is om de SQL-instructie rechtstreeks aan het Connection-object door te geven en is de eenvoudigste methode voor het uitvoeren van eenvoudige query's:
Public Sub DisplayDistinctItems()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim records As ADODB.Recordset
Set records = database.Execute("SELECT DISTINCT Item FROM Table")
'Loop through the returned Recordset.
Do While Not records.EOF 'EOF is false when there are more records.
'Individual fields are indexed either by name or 0 based ordinal.
'Note that this is using the default .Fields member of the Recordset.
Debug.Print records("Item")
'Move to the next record.
records.MoveNext
Loop
End If
CleanExit:
If Not records Is Nothing Then records.Close
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
De tweede methode is om een ADO- Command maken voor de query die u wilt uitvoeren. Dit vereist iets meer code, maar is nodig om geparametriseerde zoekopdrachten te gebruiken:
Public Sub DisplayDistinctItems()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim query As ADODB.Command
Set query = New ADODB.Command
'Build the command to pass to the data source.
With query
.ActiveConnection = database
.CommandText = "SELECT DISTINCT Item FROM Table"
.CommandType = adCmdText
End With
Dim records As ADODB.Recordset
'Execute the command to retrieve the recordset.
Set records = query.Execute()
Do While Not records.EOF
Debug.Print records("Item")
records.MoveNext
Loop
End If
CleanExit:
If Not records Is Nothing Then records.Close
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
Houd er rekening mee dat opdrachten die naar de gegevensbron worden verzonden, gevoelig zijn voor SQL-injectie , opzettelijk of onbedoeld. Over het algemeen mogen query's niet worden gemaakt door gebruikersinvoer van welke aard dan ook samen te voegen. In plaats daarvan moeten ze worden geparametriseerd (zie Parameteropdrachten maken ).
Niet-scalaire functies uitvoeren
ADO-verbindingen kunnen worden gebruikt om vrijwel elke databasefunctie uit te voeren die de provider via SQL ondersteunt. In dit geval is het niet altijd nodig om de Recordset te gebruiken die wordt geretourneerd door de functie Execute , hoewel het handig kan zijn om sleuteltoewijzingen te verkrijgen na INSERT-instructies met @@ Identity of vergelijkbare SQL-opdrachten. Merk op dat in het onderstaande voorbeeld de OpenDatabaseConnection functie OpenDatabaseConnection uit het voorbeeld Verbinding maken met een gegevensbron wordt gebruikt met het oog op beknoptheid.
Public Sub UpdateTheFoos()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim update As ADODB.Command
Set update = New ADODB.Command
'Build the command to pass to the data source.
With update
.ActiveConnection = database
.CommandText = "UPDATE Table SET Foo = 42 WHERE Bar IS NULL"
.CommandType = adCmdText
.Execute 'We don't need the return from the DB, so ignore it.
End With
End If
CleanExit:
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
Houd er rekening mee dat opdrachten die naar de gegevensbron worden verzonden, gevoelig zijn voor SQL-injectie , opzettelijk of onbedoeld. Over het algemeen mogen SQL-instructies niet worden gemaakt door gebruikersinvoer van welke aard dan ook samen te voegen. In plaats daarvan moeten ze worden geparametriseerd (zie Parameteropdrachten maken ).
Geparametriseerde opdrachten maken
Telkens wanneer SQL die via een ADO-verbinding wordt uitgevoerd, gebruikersinvoer moet bevatten, wordt het als beste praktijk beschouwd om dit te parametreren om de kans op SQL-injectie te minimaliseren. Deze methode is ook leesbaarder dan lange aaneenschakelingen en vergemakkelijkt robuustere en onderhoudbare code (dat wil zeggen door een functie te gebruiken die een reeks Parameter retourneert).
In standaard ODBC-syntaxis worden parameters gegeven ? "tijdelijke aanduidingen" in de querytekst en vervolgens worden parameters aan de Command in dezelfde volgorde als in de query.
Merk op dat het onderstaande voorbeeld de OpenDatabaseConnection functie van de Verbinding maken met een gegevensbron voor beknoptheid gebruikt.
Public Sub UpdateTheFoos()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim update As ADODB.Command
Set update = New ADODB.Command
'Build the command to pass to the data source.
With update
.ActiveConnection = database
.CommandText = "UPDATE Table SET Foo = ? WHERE Bar = ?"
.CommandType = adCmdText
'Create the parameters.
Dim fooValue As ADODB.Parameter
Set fooValue = .CreateParameter("FooValue", adNumeric, adParamInput)
fooValue.Value = 42
Dim condition As ADODB.Parameter
Set condition = .CreateParameter("Condition", adBSTR, adParamInput)
condition.Value = "Bar"
'Add the parameters to the Command
.Parameters.Append fooValue
.Parameters.Append condition
.Execute
End With
End If
CleanExit:
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
Opmerking: In het bovenstaande voorbeeld wordt een geparametriseerde instructie UPDATE getoond, maar aan elke SQL-instructie kunnen parameters worden gegeven.
