VERSION 5.00
Begin VB.Form frmDataUpdate 
   Caption         =   "ADODB database Update"
   ClientHeight    =   5820
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7695
   LinkTopic       =   "Form1"
   ScaleHeight     =   5820
   ScaleWidth      =   7695
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton cmdSQL_Update 
      Caption         =   "Edit using SQL"
      Height          =   495
      Left            =   1320
      TabIndex        =   13
      Top             =   4800
      Width           =   1215
   End
   Begin VB.CommandButton cmdSQL_Insert 
      Caption         =   "Insert using SQL"
      Height          =   495
      Left            =   2880
      TabIndex        =   12
      Top             =   4800
      Width           =   1215
   End
   Begin VB.TextBox txtYear 
      Height          =   285
      Left            =   1320
      TabIndex        =   9
      Top             =   3600
      Width           =   2895
   End
   Begin VB.TextBox txtName 
      Height          =   285
      Left            =   1320
      TabIndex        =   8
      Top             =   3360
      Width           =   2895
   End
   Begin VB.CommandButton cmdEditRecord 
      Caption         =   "Change"
      Height          =   495
      Left            =   1320
      TabIndex        =   7
      Top             =   4080
      Width           =   1215
   End
   Begin VB.CommandButton cmdInsert 
      Caption         =   "New record"
      Height          =   495
      Left            =   2880
      TabIndex        =   6
      Top             =   4080
      Width           =   1215
   End
   Begin VB.CommandButton cmdDelete 
      Caption         =   "Delete This Record"
      Height          =   495
      Left            =   6240
      TabIndex        =   5
      Top             =   3360
      Width           =   1215
   End
   Begin VB.CommandButton cmdMovePrev 
      Caption         =   "<"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   9.75
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   6240
      TabIndex        =   4
      Top             =   2520
      Width           =   615
   End
   Begin VB.CommandButton cmdMoveNext 
      Caption         =   ">"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   9.75
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   6960
      TabIndex        =   3
      Top             =   2520
      Width           =   615
   End
   Begin VB.CommandButton cmdStop 
      Caption         =   "Stop"
      Height          =   495
      Left            =   6240
      TabIndex        =   2
      Top             =   840
      Width           =   1215
   End
   Begin VB.CommandButton cmdStart 
      Caption         =   "Start"
      Height          =   495
      Left            =   6240
      TabIndex        =   1
      Top             =   240
      Width           =   1215
   End
   Begin VB.ListBox lstOutput 
      BackColor       =   &H00000000&
      ForeColor       =   &H00FFFFFF&
      Height          =   3180
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   5895
   End
   Begin VB.Line Line2 
      X1              =   480
      X2              =   5880
      Y1              =   4680
      Y2              =   4680
   End
   Begin VB.Label lblName 
      Caption         =   "Naam"
      Height          =   255
      Left            =   600
      TabIndex        =   11
      Top             =   3360
      Width           =   615
   End
   Begin VB.Label lblYear 
      Caption         =   "Year Born"
      Height          =   255
      Left            =   360
      TabIndex        =   10
      Top             =   3600
      Width           =   855
   End
   Begin VB.Line Line1 
      X1              =   6240
      X2              =   7560
      Y1              =   1560
      Y2              =   1560
   End
End
Attribute VB_Name = "frmDataUpdate"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
'oefening :
'database connectie maken
'records ophalen
'(output dumpen in listbox)
'delete, insert en update van records uitproberen

'xxxxxxxxxxxxxxxxxxxxxxxxxxx

'declareer connectie;
'vereist Microsoft ActiveX Dataobjects 2.6 Library (references)
    Dim cn As ADODB.Connection

'declareer recordset, globaal zodat alle procedures hem kennen
    Dim rec As ADODB.Recordset


Private Sub cmdDelete_Click()
    Dim intDelete_ID As Integer
    
    'variabele met Author_ID van current record
    intDelete_ID = rec.Fields(0).Value
    
    'delete through SQL
    cn.Execute ("delete from [Title Author] where Au_ID = " & intDelete_ID & ";")
    cn.Execute ("delete from Authors where Au_ID = " & intDelete_ID & ";")
        
            'dit verondersteld dat tabel gekend is, tenzij die
            'veoeger in een variabele gevangen is.
            'idem voor veld dat ID bevat e.d.
     
     'user feedback
     lstOutput.AddItem "*** DELETED ***"

End Sub

Private Sub cmdEditRecord_Click()
    
    'gewijzigde waarde toekennen aan velden in recordset
    If txtName.Text <> "" Then
        rec.Fields(1).Value = txtName.Text
    Else
        'user feedback
        lstOutput.AddItem "unable to edit if no value for Name is given"
    End If
    
    'wijzigingen in recordset doorgeven aan database
    rec.Update
    
    'user feedbak
    With lstOutput
        .AddItem ""
        .AddItem "*** Modified***"
        .AddItem rec.Fields(1).Value
    End With

End Sub

Private Sub cmdInsert_Click()

    'alleen uitvoeren als er een waarde gegeven is
    If Trim(txtName.Text) <> "" And Val(txtYear.Text) > 0 Then
        'toevoegen met addNew method
        rec.AddNew
        
        'waarden
        rec.Fields(1).Value = txtName.Text
        rec.Fields(2).Value = Int(txtYear.Text)
        
        'wijziging doorgeven aan database
        rec.Update
    
        'gewijzigde record tonen
        lstOutput.AddItem "***  RECORD ADDED  ***"
        DisplayCurrentRecord
        rec.MoveLast
        DisplayCurrentRecord
        lstOutput.AddItem "***  RECORD ADDED  ***"
    
    Else
        lstOutput.AddItem "required field empty, no records added"
    End If
    
        
End Sub

Private Sub cmdMoveNext_Click()

    lstOutput.Clear
    ' naar vorig record, tenzij 'begin of file'
    If Not rec.EOF Then
        rec.MoveNext
    Else
        rec.MoveFirst
    End If
    
    'output
    DisplayCurrentRecord
End Sub

Private Sub cmdMovePrev_Click()

    lstOutput.Clear
    ' naar vorig record, tenzij 'begin of file'
    If Not rec.BOF Then
        rec.MovePrevious
    Else
        rec.MoveLast
    End If
    
    'output
    DisplayCurrentRecord
End Sub
Private Sub DisplayCurrentRecord()
    
    'refresh output listbox
    'lstOutput.Clear
    With lstOutput
        .AddItem rec.Fields(0).Name & " : " & rec.Fields(0).Value
        .AddItem rec.Fields(1).Name & " : " & rec.Fields(1).Value
        .AddItem rec.Fields(2).Name & " : " & rec.Fields(2).Value
   End With
   
   'show data in text boxes
   txtName.Text = rec.Fields(1).Value
   If rec.Fields(2).Value <> vbNullString Then
        txtYear.Text = rec.Fields(2).Value
    End If
   
End Sub

Private Sub cmdSQL_Insert_Click()
    Dim strSQL As String
    
    strSQL = "update Authors set " & rec.Fields(1).Name & " = " _
            & txtName.Text
    
    'create SQL update statement
    strSQL = "INSERT into Authors (" & rec.Fields(1).Name & ")" _
            & " VALUES ('" & txtName.Text & "');"
            
        MsgBox strSQL
        
    'execute sql statement
    cn.Execute (strSQL)
        
     
     'user feedback
     lstOutput.AddItem "*** RECORD ADDED ***"
     rec.MoveLast
     DisplayCurrentRecord

End Sub

Private Sub cmdSQL_Update_Click()
    Dim intID As Integer
    Dim strSQL As String
    
    strSQL = "update Authors set " & rec.Fields(1).Name & " = " _
            & txtName.Text
    
    'variabele met Author_ID van current record
    intID = rec.Fields(0).Value
    
    'create SQL update statement
    strSQL = "update Authors " _
            & " set " & rec.Fields(1).Name & " = '" & txtName.Text & "'" _
            & " where Au_ID = " & intID & ";"
            
        MsgBox strSQL
        
    'execute sql statement
    cn.Execute (strSQL)
        
     
     'user feedback
     lstOutput.AddItem "*** MODIFIED ***"
     DisplayCurrentRecord

End Sub

Private Sub cmdStart_Click()
'make database connection
    Set cn = New ADODB.Connection

'set properties of connection
    cn.ConnectionString = _
       "Provider=Microsoft.Jet.OLEDB.4.0;" _
       & "Data Source=" & App.Path & "\Biblio.mdb;"

'connectie met database maken
    cn.Open

'make record set, i.e select data
    
    Dim strSQL As String            'to hold an SQL statement
    
    Set rec = New ADODB.Recordset
    
    strSQL = "select * from Authors"
    
    'set recordset properties
    With rec
        .ActiveConnection = cn
        .Source = strSQL
        .LockType = adLockOptimistic    'wijze van recordvergrendeling tijdens update
        .CursorType = adOpenDynamic     'wijze van refresh & navigatie in recordset
    End With
    
    'recordset openen : de data worden nu opgehaald
    'en zijn beschikbaar voor navigatie en output
    
    rec.Open        'zie 'close'
    
    'extra : user feedback
    lstOutput.Clear
    lstOutput.AddItem "MS-DOS wordt gestart ..."    'grapje  :-)
    lstOutput.AddItem "connecting to database " & vbCrLf & cn.ConnectionString
    lstOutput.AddItem "database ready"
    lstOutput.AddItem "C:\>_"
    lstOutput.AddItem ""
    DisplayCurrentRecord
    
End Sub

Private Sub cmdStop_Click()
    
    'recordset afsluiten en vernietigen
    rec.Close
    Set rec = Nothing
    
    'idem voor connectie
    cn.Close
    Set rec = Nothing
    
    'form sluiten, toepassing beeindigen
    Unload Me
End Sub

Private Sub Command2_Click()

End Sub