Kategorie: .Net

With DBRecordset
     .ActiveConnection = DBConnection
     .CursorLocation = ADODB.CursorLocationEnum.adUseClient
     .LockType = ADODB.LockTypeEnum.adLockOptimistic
     .Open("SELECT @@IDENTITY")
     
     oder
     
     .Open("SELECT MAX(ID_Field) FROM Table")
     
     If .RecordCount = 1 Then
         Dim iZahl As Interger = .Fields(0).Value
     End If
End With

Dim sConnectionString
Dim sSQL As String

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydatabase.mdb"
sSQL = "SELECT ID, Name FROM Person"

Dim DB_Conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim DB_DataAdapter As New System.Data.OleDb.OleDbDataAdapter(sSQL, DB_Conn)
Dim DataSet As New DataSet()

DB_Conn.Open()
DB_DataAdapter.Fill(DataSet, "Person")

dgvConsumptionData.DataSource = DataSet.Tables("Person")

DB_Conn.Close()

Dim DB_OleDBCommandDelete As New System.Data.OleDb.OleDbCommand
Dim sSQL As String

Try
    ' steht die Datenbankankbindung?
    If DB_OleDBConnection.State = System.Data.ConnectionState.Open Then
        DB_OleDBCommandDelete.Connection = DB_OleDBConnection
        sSQL = "DELETE FROM Users WHERE ID=1"

        DB_OleDBCommandDelete.CommandText = sSQL
        DB_OleDBCommandDelete.ExecuteNonQuery()
    End If

    DB_OleDBCommandDelete.Dispose()
    DB_OleDBCommandDelete = Nothing
Catch ex As Exception
    MessageBox.Show("Fehler: " & ControlChars.CrLf & ex.ToString())
End Try

Dim DB_OleDBCommand As New System.Data.OleDb.OleDbCommand
Dim sSQL As String
Dim sDecimalSeparator As String = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator

Try
    DB_OleDBCommand.Connection = frmMain.DB_OleDBConnection

    sSQL = "UPDATE Users SET Name='" & tbName.Text & "', " & _
    "Weight=" & tbPower.Text.Replace(sDecimalSeparator, ".") & ", " & _
    "WHERE ID=" & Me.Tag.ToString

    DB_OleDBCommand.CommandText = sSQL
    DB_OleDBCommand.ExecuteNonQuery()
    DB_OleDBCommand.Dispose()
    DB_OleDBCommand = Nothing
Catch ex As Exception
    MessageBox.Show("Fehler: " & ControlChars.CrLf & ex.ToString())
End Try

Dim DB_OleDBCommandInsert As New System.Data.OleDb.OleDbCommand
Dim DB_OleDBCommandSelect As New System.Data.OleDb.OleDbCommand

With DB_OleDBCommandInsert
    .CommandText = "INSERT INTO Users (Name) VALUES ('Horst')"
    .Connection = frmMain.DB_OleDBConnection ' Connection wurde in frmMain erzeugt
    .ExecuteNonQuery()
    .Dispose()
End With

Dim iID As Integer = -1 ' Platzhalter für die Insert-ID

'letzte Insert ID zurückholen, ab Provider=Microsoft.Jet.OLEDB.4.0 möglich
With DB_OleDBCommandSelect
    .CommandText = "SELECT @@IDENTITY"
    .Connection = frmMain.DB_OleDBConnection ' Connection wurde in frmMain erzeugt
    iID = .ExecuteScalar()
    .Dispose()
End With

DB_OleDBCommandInsert = Nothing
DB_OleDBCommandSelect = Nothing

Dim DB_OleDBConnection As System.Data.OleDb.OleDbConnection
Dim DB_OleDBCommandSelect As New System.Data.OleDb.OleDbCommand
Dim DB_OleDBReader As System.Data.OleDb.OleDbDataReader

DB_OleDBConnection = New System.Data.OleDb.OleDbConnection

With DB_OleDBConnection
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDatabase.mdb"
    .Open()
End With

With DB_OleDBCommandSelect
    .CommandText = "SELECT ID, Name FROM Users"
    .Connection = DB_OleDBConnection
    DB_OleDBReader = .ExecuteReader()
    .Dispose()
End With

With DB_OleDBReader
    If .HasRows Then
        Do While .Read()
            System.Console.WriteLine(.Item("Name"))
        Loop
    End If
    .Close()
End With

DB_OleDBCommandSelect = Nothing

DB_OleDBConnection.Close()
DB_OleDBConnection.Dispose()
DB_OleDBConnection = Nothing

oder

Dim sConnectionString
Dim sSQL As String

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydatabase.mdb"
sSQL = "SELECT ID, Name FROM Users"

Dim DB_Conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim DB_Cmd As New System.Data.OleDb.OleDbCommand(sSQL, DB_Conn)
Dim DB_DataReader As System.Data.OleDb.OleDbDataReader

DB_Conn.Open()

DB_DataReader = DB_Cmd.ExecuteReader()

If DB_DataReader.HasRows Then
    Do While DB_DataReader.Read()
        System.Console.WriteLine(DB_DataReader.Item("Name"))
    Loop
End If

DB_DataReader.Close()
DB_Cmd.Dispose()
DB_Cmd = Nothing
DB_Conn.Close()
DB_Conn.Dispose()
DB_Conn = Nothing

' Connection String für MS Access
Dim sConnectionString = sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDatabase.mdb"

Dim DB_Conn As New System.Data.OleDb.OleDbConnection(sConnectionString)

DB_Conn.Open()

...

DB_Conn.Close()
DB_Conn.Dispose()
DB_Conn = Nothing

• DBConnection muss vorher geöffnet worden sein (siehe ADODB Datenanbindung zu MS Access)
• DBRecordset öffnen

Dim DBRecordset As New ADODB.Recordset

DBRecordset.Open("Gebaeude", DBConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

oder

With DBRecordset
     .ActiveConnection = DBConnection
     .CursorLocation = ADODB.CursorLocationEnum.adUseClient
     .LockType = ADODB.LockTypeEnum.adLockOptimistic
     .Open("SELECT ID, Bezeichnung FROM Gebaeude")
End With

• allg. Zugriff auf Feldelemente

Dim h As Integer
h = DBRecordset.Fields!ID.Value 'unsauber
h = DBRecordset.Fields.Item(0).Value ' per Index
h = DBRecordset.Fields("ID").Value 'Spaltenname

• Daten per Schleife aus dem DBRecordset auslesen

While (Not DBRecordset.EOF)
   sXMLResult = DBRecordset.Fields("ID").Value
   DBRecordset.MoveNext()
End While

oder
   
For i = 0 To DBRecordset.RecordCount - 1
   sXMLResult = DBRecordset.Fields("ID").Value
   DBRecordset.MoveNext()
Next i

• und das Schließen des DBRecordset nicht vergessen

DBRecordset.Close()

• ADODB Klassen zum Projekt hinzufügen durch Klick im Projektbaum->Verweise->Verweis hinzufügen …->.Net->adodb wählen
• Connection herstellen:

Dim DBConnection As New ADODB.Connection

DBConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & OpenFileDialog.FileName)
...
DBConnection.Close()

oder

With DBConnection
    .Mode = ADODB.ConnectModeEnum.adModeShareDenyNone
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & sDBFileName
    .Open()
End With