Docstoc

Programming

Document Sample
Programming Powered By Docstoc
					Programming with ADO
To program ADO Objects directly, you must set your VB project to refer to the latest
version of Microsoft Active X Data Objects Library from the Project References
menu dialog box
The following are the main concern when programming with ADO in VB:
   i) Setting up and maintaining a connection to data with connection object
  ii) Retriving raw or otherwise manipulating the data with recordset objects
 iii) Determining the exact behavior and nature of the row of data by connection or
        command objects
 The Connection Object
  The Connection object is used to establish connection sessions with data sources. i.e.
  it provides a mechanism for initializing and establishing a connection. Once the
  connection object is created its methods and properties can be used to manipulate
  the data source (in this case the database). The underlying OLE DB provider is used
  for connecting to the data source.

Methods of the connection object:

Method                            Description
Open                              Opens a new connection to a data source
Close                             Closes a connection as well as any dependant objects
Execute                           Executes a query, typically an SQL statement
BeginTrans                        Starts a new transaction
CommitTrans                       Commits changes made during the transaction
RollBackTrans                     Cancels any changes done during a transaction

Connection object's properties:

Property                          Description
ConnectionString                  Contains the necessary information to establish a connection
ConnectionTimeOut                 Determines how long to wait to establish a connection
CommandTimeOut                    Determines how long to wait while executing a command
State                             Indicates if a connection is open
Provider                          Indicates the name of the provider
Version                           Indicates the ADO version
CursorLocation                    Sets/returns the location of the cursor engine



Initializing the connection object in code
The following are steps for establishing a connection object: -
     i). Make sure that you have a reference to the ADO
    ii). Declare an object variable whose type is ADODB.Connection
   iii). Set the connection object’s connectstring property to reflect a valid OLEDB
          provider. Specify the provider string as follows: -
         conObject.ConnectionString = “provider = Microsoft.Jet.OLEDB.3.51;” _
         & “Data source = c:\folder\data.mdb”
    iv). Call the connection object’s open method. If you didn’t specify the
          ConnectString property above, pass the connection string as the first
          argument to the open method as follows:
         conObject.Open “provider = Microsoft.Jet.OLEDB.3.51;” _
         & “Data source = c:\folder\data.mdb”



Application Programming Notes ~ Wainaina                                          Page 1 of 7
Recordset object: -
A recordset object represents the entire set of records from a base table or results of an
executed command. At any time, the recordset object refers to only a single record
within the set as the current record.
Recordset objects are used to manipulate data from the provider. All recordset objects
are constructed using records (rows) and fields (columns).
Recordsets come in different types and they are often called cursor types. A cursor is
just another name for a recordset.
In VB, there are four different types of recordsets

 i).    Dynamic cursor: -
       Allows you to view additions, changes and deletion by other users and allows all
       types of movement through the recordset that don’t rely on bookmarks; allow
       bookmarks if the provider supports them.

ii). Keyset cursor: -
     It behaves like a dynamic cursor except that it prevents from viewing records
     that other users have added and prevents access to records that other users delete.
     Data changes by other users will still be visible. It always supports bookmarks
     and therefore allows all types of movement through the recordset

iii). Static cursor: -
      Provides a static copy of a set of records for you to use to find data or generate
      reports. Allows bookmarks and therefore allows all types of movement through
      the recordset. Additions, changes or deletions by other users will not be visible.

iv). Forward only cursor
     This cursor identically behaves like a dynamic cursor except that it allows you to
     scroll only forward through records. This improves performance in situations
     where you need to make only a single pass through the recordset.

Lock type
The following are different locktype supported by the ADO recordset : -
  i). LockReadOnly: -
      It is a read only lock and the data retrieved cannot be modified. It is a resource
      friendly lock but not practical.
 ii). LockPessimistic: -
      A lock is placed on records as soon as you start editing them and released when
      the update or cancel methods are called.

iii). LockOptimistic: -
      This option means, when the update is called, the record is locked, updated and
      then released all in one swoop. If someone else changed the record before you
      started editing it, an error will occur.

iv). LockBatchOptimistic
     It allows editing the records in the recordset, then do a complete database update
     on all those changed by running an updatebatch method

Initialising Data Connected Recordset in Code
The following are steps you need to make in order to initialize a data connected
recordset in the code: -


Application Programming Notes ~ Wainaina                                     Page 2 of 7
   Have a valid connection object
   Declare an object variable of the type ADODB.Recordset
   Set the source property (SQL statement, name of the table or stored procedure)
    and active connection property (a valid connection object). You can omit this step
    to pass information about the source and the active connection as arguments in the
    next step.
   Call the recordset’s open method. If the step above is omitted, indicate the
    recordset’s record source and activeConnection as the open method’s first and
    second arguments.

Opening the ADO Recordset by setting ActiveConnection and souce property

       Dim conObject As ADODB.Connection
       Dim rsRecords As ADODB.Connection
       Dim sConnect As String

       Set conObject = New ADODB.Connection
       Set rsRecords = New ADODB.Recordset

       sConnect = “Provider = Microsoftt.Jet.OLEDB.3.51; Datasource = data.mdb”
       conObject.Open sConnect

       rsRecords.Source= “Select * from tables”
       Set rsRecords.ActiveConnection = conObject
       rsRecords. Open

Opening an ADO recordset with arguments to the open method

       Dim conObject As ADODB.Connection
       Dim rsRecords As ADODB.Connection
       Dim sConnect As String

       Set conObject = New ADODB.Connection
       Set rsRecords = New ADODB.Recordset

       sConnect = “Provider = Microsoftt.Jet.OLEDB.3.51; Datasource = data.mdb”
       conObject.Open sConnect

       rsRecords.Open “Select * from tables”,Cconn,adOpenStatic,adLockOptimistic

Example
The following is a simple code documentation for Student Details Module

Dim cConn As ADODB.Connection
Dim rsStudents As ADODB.Recordset

Private Sub OpenConnection (strPath As String)
 'Create an instance of connection
 Set cConn = New ADODB.Connection
 'create an instance of recordset object
  Set rsStudents = New ADODB.Recordset
 'set the cursor location
 cConn.CursorLocation = adUseClient
 ' Open a new connection


Application Programming Notes ~ Wainaina                                  Page 3 of 7
 cConn.Open "Provider=microsoft.jet.OLEDB.3.51;" _
  & "Data source= " & strPath
  rsStudents.Open "select * from tblstudents", cConn, adOpenKeyset, adLockOptimistic
End Sub

Private Sub Form_Load ()
 OpenConnection App.Path & "\students.mdb"

 Call ClearControls
 Call DisableControls
 With rsStudents
    txtAdimissionNumber.Text = ![admission number] & ""
    txtSurname.Text = !Name & ""
    txtCourse.Text = !course & ""
    txtCost.Text = !cost & ""
  End With
End Sub

Private Sub cmdAdd_Click ()
 Dim strEntry As Integer
 strEntry = InputBox("Enter the Admission number of the student")
  With rsStudents
    .MoveFirst
    .Find "[admission number] = " & strEntry
    If .EOF = True Then
        .AddNew
        Call ClearControls
        Call EnableControls
        txtAdimissionNumber.Text = strEntry
        txtSurname.SetFocus
    Else
        txtAdimissionNumber.Text = rsStudents![admission number] & ""
        txtSurname.Text = rsStudents!Name & ""
        txtCourse.Text = rsStudents!course & ""
        txtCost.Text = rsStudents!cost & ""
        MsgBox "Record already exists"
    End If
   End With
End Sub

Private Sub cmdCancel_Click ()
 rsStudents.CancelUpdate
 Call ClearControls
 Call DisableControls
End Sub

Private Sub cmdDelete_Click ()
 Dim strEntry As Integer, intResponse As Integer
strEntry = InputBox("Enter the Admission number of the student, to Delete")
 With rsStudents
   .Find "[admission number] = " & strEntry
   If .EOF = True Then
       MsgBox "Record does not Exists"
       Call ClearControls
   Else
       txtAdimissionNumber.Text = rsStudents![admission number] & ""
       txtSurname.Text = rsStudents!Name & ""
       txtCourse.Text = rsStudents!course & ""
       txtCost.Text = rsStudents!cost & ""
       intResponse = MsgBox("Do you want to delete this Record", vbYesNo +
       vbExclamation, "Delete Record")

Application Programming Notes ~ Wainaina                                      Page 4 of 7
     If intResponse = vbYes Then
        .Delete
        Call ClearControls
     End If
   End If
  End With
End Sub

Private Sub cmdExit_Click ()
 Unload Me
End Sub

Private Sub cmdFind_Click ()
   Dim strEntry As Integer
 strEntry = InputBox("Enter the Admission number of the student, to Search")
 With rsStudents
    .MoveFirst
    .Find "[admission number] = " & strEntry
    If .EOF = True Then
        MsgBox "Record does not Exists"
        Call ClearControls
    Else
        txtAdimissionNumber.Text = rsStudents![admission number] & ""
        txtSurname.Text = rsStudents!Name & ""
        txtCourse.Text = rsStudents!course & ""
        txtCost.Text = rsStudents!cost & ""
    End If
   End With
End Sub

Private Sub cmdFirst_Click ()
  With rsStudents
   .MoveFirst
    txtAdimissionNumber.Text = rsStudents![admission number] & ""
    txtSurname.Text = rsStudents!Name & ""
    txtCourse.Text = rsStudents!course & ""
    txtCost.Text = rsStudents!cost & ""
  End With
End Sub

Private Sub cmdLast_Click ()
  With rsStudents
    .MoveLast
    txtAdimissionNumber.Text = rsStudents![admission number] & ""
    txtSurname.Text = rsStudents!Name & ""
    txtCourse.Text = rsStudents!course & ""
    txtCost.Text = rsStudents!cost & ""
  End With
End Sub

Private Sub cmdNext_Click ()
 With rsStudents
   .MoveNext
   If .EOF = True Then
     .MoveLast
     txtAdimissionNumber.Text = rsStudents![admission number] & ""
     txtSurname.Text = rsStudents!Name & ""
     txtCourse.Text = rsStudents!course & ""
     txtCost.Text = rsStudents!cost & ""
     MsgBox "This is the last record"
   Else

Application Programming Notes ~ Wainaina                                       Page 5 of 7
     txtAdimissionNumber.Text = rsStudents![admission number] & ""
     txtSurname.Text = rsStudents!Name & ""
     txtCourse.Text = rsStudents!course & ""
     txtCost.Text = rsStudents!cost & ""
   End If
  End With
End Sub

Private Sub cmdPrevious_Click ()
 With rsStudents
   .MovePrevious
   If .BOF = True Then
     .MoveFirst
     txtAdimissionNumber.Text = rsStudents![admission number] & ""
     txtSurname.Text = rsStudents!Name & ""
     txtCourse.Text = rsStudents!course & ""
     txtCost.Text = rsStudents!cost & ""
     MsgBox "This is the first record"
   Else
      txtAdimissionNumber.Text = rsStudents![admission number] & ""
      txtSurname.Text = rsStudents!Name & ""
      txtCourse.Text = rsStudents!course & ""
      txtCost.Text = rsStudents!cost & ""
   End If
  End With
End Sub

Private Sub cmdSave_Click ()
 With rsStudents
    rsStudents![admission number] = txtAdimissionNumber.Text
    rsStudents!Name = txtSurname.Text
    rsStudents!course = txtCourse.Text
    rsStudents!cost = txtCost.Text
   .Update
   MsgBox "Update Completed"
    End With
End Sub

Private Sub EnableControls ()
 Dim MyControl As Control
 For Each MyControl In Controls
   If TypeOf MyControl Is TextBox Then
     MyControl.Enabled = True
   End If
 Next
End Sub

Private Sub ClearControls ()
 Dim MyControl As Control
 For Each MyControl In Controls
   If TypeOf MyControl Is TextBox Then
     MyControl.Text = ""
   End If
 Next
End Sub

Private Sub DisableControls ()
  Dim MyControl As Control
 For Each MyControl In Controls
   If TypeOf MyControl Is TextBox Then
     MyControl.Enabled = False

Application Programming Notes ~ Wainaina                              Page 6 of 7
  End If
 Next
End Sub

Private Sub Form_Unload(Cancel As Integer)
 If Not (rsStudents Is Nothing) Then
    rsStudents.Close
    Set rsStudents = Nothing
 End If
 If Not (cConn Is Nothing) Then
   cConn.Close
   Set cConn = Nothing
 End If
End Sub




Application Programming Notes ~ Wainaina     Page 7 of 7

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:38
posted:2/23/2010
language:English
pages:7