VB.Net – Access Insert Update Delete mit OleDb.OleDbConnection und OleDb.OleDbCommand

VB.Net – Access Insert Update Delete mit OleDb.OleDbConnection und OleDb.OleDbCommand

Mit der folgenden Funktionsreihe kann man ACCESS Ereignissabfragen mit einer OleDbConnection an eine ACCESS Datenbank senden.

So wird es gemacht:

 
            Beispielaufrufe:
 
            '------------------------------------
            'Edit wenn BEO Land vorhanden
            Dim intCount As Integer = _
                ACCESS_ROW_Update( _
                "Tabelle", _
                "Land;", TextBox2.Text & ";", "S;", _
                "WHERE Ursprungsland = '" & TextBox1.Text & "'", _
                gStrDB)
            '------------------------------------
 
            '------------------------------------
            'Addnew wenn BEO Land nicht vorhanden
            If Not intCount <> 0 Then
                Dim intCount2 As Integer = _
                ACCESS_ROW_Insert( _
                "Tabelle", _
                "Ursprungsland;Land;", TextBox1.Text & ";" & TextBox2.Text & ";", "S;S;", _
                gStrDB)
            End If
            '------------------------------------
    '============================================
    ' VB.NET Beispiel in Visual Basic .Net
    ' © wilfried.bitz@bridgesolution.de
    '============================================
    Public Function ACCESS_ROW_Update( _
        ByVal strTable As String, _
        ByVal strFields As String, _
        ByVal strFieldValues As String, _
        ByVal strFieldFormats As String, _
        ByVal strKritMitWhere As String, _
        ByVal strMdb As String) As Integer
 
        '----------------------------------------
        Dim strProzedur As String = "ACCESS_ROW_Update"
        Dim strSQL As String
        Dim intCount As Integer
        '----------------------------------------
 
        Try
 
            '------------------------------------
            If Not strKritMitWhere <> "" Then
                Exit Function
            End If
            '------------------------------------
 
            '------------------------------------
            strSQL = _
                "UPDATE " & _
                strTable & " " & _
                "SET "
            '------------------------------------
 
            '------------------------------------
            Dim strField As String
            Dim strFieldValue As String
            Dim strFieldFormat As String
            Dim intc As Integer
            For intc = 1 To 10
                strField = CStr(CONV_ColStringRead(strFields, intc, ";"))
                strFieldValue = CStr(CONV_ColStringRead(strFieldValues, intc, ";"))
                strFieldFormat = CStr(CONV_ColStringRead(strFieldFormats, intc, ";"))
 
                If Not strField <> "" Then Exit For
                If intc > 1 Then strSQL = strSQL & " , "
 
                If strFieldFormat = "N" Then
                    strSQL = strSQL & _
                        strField & " = " & strFieldValue & " "
                End If
                If strFieldFormat = "S" Then
                    strSQL = strSQL & _
                        strField & " = '" & strFieldValue & "' "
                End If
 
            Next intc
            '------------------------------------
 
            '------------------------------------
            strSQL = strSQL & _
                strKritMitWhere & " "
            '------------------------------------
 
            '------------------------------------
            intCount = ACCESS_ExecuteNonQuery(strSQL, gStrAccessProvider, strMdb)
            '------------------------------------
 
        Catch ex As Exception
            '------------------------------------
            MessageBox.Show(Err.Description)
            '------------------------------------
 
        Finally
            '------------------------------------
            ACCESS_ROW_Update = intCount
            '------------------------------------
 
        End Try
 
    End Function
 
    '============================================
    ' VB.NET Beispiel in Visual Basic .Net
    ' © wilfried.bitz@bridgesolution.de
    '============================================
    Public Function ACCESS_ROW_Delete( _
        ByVal strTable As String, _
        ByVal strKritMitWhere As String, _
        ByVal strMdb As String) As Integer
 
        'On Error Resume Next
 
        Dim intCount As Integer
        Dim strSQL As String
 
        Try
 
            '------------------------------------
            strSQL = _
                "DELETE from " & strTable & " " & " " & strKritMitWhere
            '------------------------------------
 
            '------------------------------------
            intCount = _
                ACCESS_ExecuteNonQuery(strSQL, gStrAccessProvider, strMdb)
            '------------------------------------
 
            '------------------------------------
            ACCESS_ROW_Delete = intCount
            '------------------------------------
 
        Catch ex As Exception
            '------------------------------------
            MessageBox.Show(Err.Description)
            '------------------------------------
 
        Finally
            '------------------------------------
            ACCESS_ROW_Delete = intCount
            '------------------------------------
 
        End Try
 
    End Function
 
    '============================================
    ' VB.NET Beispiel in Visual Basic .Net
    ' © wilfried.bitz@bridgesolution.de
    '============================================
    Public Function ACCESS_ROW_Insert( _
        ByVal strTable As String, _
        ByVal strFields As String, _
        ByVal strFieldValues As String, _
        ByVal strFieldFormats As String, _
        ByVal strMdb As String) As Integer
 
        '----------------------------------------
        Dim strProzedur As String = "ACCESS_ROW_Insert"
        Dim strSQL As String = ""
        Dim intCount As Integer
        '----------------------------------------
 
        Try
 
            '------------------------------------
            strSQL = _
                "INSERT INTO " & _
                strTable & " " & _
                "( "
            '------------------------------------
 
            '------------------------------------
            Dim strField As String
            Dim strFieldValue As String
            Dim strFieldFormat As String
            Dim intc As Integer
            For intc = 1 To 200 Step 1
                strField = CStr(CONV_ColStringRead(strFields, intc, ";"))
                strFieldValue = CStr(CONV_ColStringRead(strFieldValues, intc, ";"))
                strFieldFormat = CStr(CONV_ColStringRead(strFieldFormats, intc, ";"))
 
                If Not strField <> "" Then Exit For
                If intc > 1 Then strSQL = strSQL & " , "
 
                strSQL = strSQL & strField & " "
 
            Next intc
            '------------------------------------
 
            '------------------------------------
            strSQL = strSQL & " ) VALUES ( "
            '------------------------------------
 
            '------------------------------------
            For intc = 1 To 200 Step 1
                strField = CStr(CONV_ColStringRead(strFields, intc, ";"))
                strFieldValue = CStr(CONV_ColStringRead(strFieldValues, intc, ";"))
                strFieldFormat = CStr(CONV_ColStringRead(strFieldFormats, intc, ";"))
 
                If Not strField <> "" Then Exit For
                If intc > 1 Then strSQL = strSQL & " , "
 
                If strFieldFormat = "N" Then
                    strSQL = strSQL & _
                        " " & strFieldValue & " "
                End If
                If strFieldFormat = "S" Then
                    strSQL = strSQL & _
                        " '" & strFieldValue & "' "
                End If
 
            Next intc
            '------------------------------------
 
            '------------------------------------
            strSQL = strSQL & _
               " ) "
            '------------------------------------
 
            '------------------------------------
            intCount = ACCESS_ExecuteNonQuery(strSQL, gStrAccessProvider, strMdb)
            '------------------------------------
 
        Catch ex As Exception
            '------------------------------------
            MessageBox.Show(Err.Description)
            '------------------------------------
 
        Finally
            '------------------------------------
            ACCESS_ROW_Insert = intCount
            '------------------------------------
 
        End Try
 
    End Function
 
   Public Function ACCESS_ExecuteNonQuery( _
        ByVal strSql As String, _
        ByVal strMDB As String) As Integer
        Dim strProzedur As String = "ACCESS_ExecuteNonQuery"
 
        Try
 
            ' Verbindung zur Datenbank herstellen
            Dim myOleDbConnection As OleDb.OleDbConnection = _
                ACCESS_ConnectionInit(strMDB)
            If IsNothing(myOleDbConnection) Then Exit Function
 
            ' SelectCommand erstellen welches die "Select-Abfrage" gegen die
            ' Datenbank beinhaltet
            Dim myOleDbSelectCommand As New OleDb.OleDbCommand
            myOleDbSelectCommand.Connection = myOleDbConnection
            myOleDbSelectCommand.CommandText = strSql
 
            ' DataAdapter mit dem SelectCommand verbinden
            Dim myOleDbDataAdapter As New OleDb.OleDbDataAdapter
            myOleDbDataAdapter.SelectCommand = myOleDbSelectCommand
 
            Dim intCount As Integer = myOleDbSelectCommand.ExecuteNonQuery()
            myOleDbSelectCommand = Nothing
 
 
            ACCESS_ExecuteNonQuery = intCount
 
            '------------------------------------
            'Aufräumen
            myOleDbConnection = Nothing
            myOleDbSelectCommand = Nothing
            myOleDbDataAdapter = Nothing
            '------------------------------------
 
        Catch ex As Exception
            '------------------------------------
            ERROR_Handler(mStrModul, strProzedur, ex.Message)
            '------------------------------------
 
 
        End Try
 
    Public Function ACCESS_ConnectionInit( _
        ByVal strMdb As String) As OleDb.OleDbConnection
 
        ACCESS_ConnectionInit = Nothing
        Dim strProzedur As String = "ACCESS_ConnectionInit"
 
        ' Verbindung zur Datenbank herstellen
        'Dim myOleDbConnection As New OleDb.OleDbConnection
 
        Try
 
 
            If Not IsNothing(gMyOleDbConnection) Then
                'Access 97
                'gmyOleDbConnection.ConnectionString = _
                '    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                '    "Data Source=" & strMdb & ""
 
                'Access 2007
                If Not gMyOleDbConnection.ConnectionString <> "" Then
                    gMyOleDbConnection.ConnectionString = _
                        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & strMdb & ";Persist Security Info=False"
                End If
 
                If gMyOleDbConnection.State = ConnectionState.Closed Then
                    gMyOleDbConnection.Open()
                End If
                If gMyOleDbConnection.State = ConnectionState.Broken Then
                    gMyOleDbConnection.Close()
                    gMyOleDbConnection.Open()
                End If
 
                ACCESS_ConnectionInit = gMyOleDbConnection
            End If
 
        Catch ex As Exception
            ERROR_Handler(mStrModul, strProzedur, ex.Message)
            Exit Function
 
        Finally
        End Try
 
    End Function

Spendierst du mir eine Tasse Kaffee? Ich würd mich riesig freuen. DANKE !! 🙂

Author: admin

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.