Sunday, March 29, 2020

Chapter 6 Component Object Model









Introduction (COM)
To create applications that can interact with any other application requires that there can be a standard manner in which two applications will interact.

Microsoft has defined a model that set standards that will enable applications to interact with each other.  This model is referred to as Component Object Model (COM).  It defines rules when implemented will enable applications to interact with other applications in a Uniform Manner.

“Both calling application and called application need to follow rules specified by COM.
COM is defined as an Open, Extensible standard for software Inter-Operability.”

Ø  It is called an open standard because COM specifies rules in general terms and not specifies.
Ø  It is called an extensible because it is an evolving standard and newer rules are being added.

Implementing COM enables to use features of other applications; extending the capabilities of out application.

Object Linking and Embedding
COM defines a model that enables applications to interact with each other.  It is, an open standard as it defines rules in general and not specific terms.

For example for our application, to call another application the calling applications need to know the location of it.  Hence the calling application has to search for the application it needs and then to execute it.

To have the calling application search for the hard disk will be a time consuming affair.  Thus, COM states that all applications that can provide services to other applications must be registered at a Central Location. 

COM needs to be implemented in terms of specifics so that it can be used Object Linking Embedding (OLE) is one such implementation of COM.

How OLE Work?
Suppose you want to use Microsoft Word document where MS-Word is referred to as Client and Ms-Graph is called as Server application.  The graph that Ms-Graph creates in word document is referred as an object.  As server application creates an object for client application, it is sometimes also referred to as an Object Application.

So for doing this First we have to create a New document in word.  Where we have to insert a graph for Company’s Report for which we have to invoke Microsoft-Graph.  As Ms-Word doesn’t have capability to draw graphs and Ms-Graph has the ability to draw it, we can use it in our document.  In wordà Insert àObject.  We can invoke graph for our application.  After going through necessary steps a graph can be drawn on to our document.

Some points to remember
1)                  Ms-Word is calling application referred to as Client.
2)                  Ms-Graph is called Server Application or Object Application.
3)                  Graph created by Ms-Graph in word document is called as an object.
4)                  To modify graph object we need to use Microsoft-Graph as Microsoft Word can not modify this object that is created by Graph.

Registering A Server
COM states that all server applications; that are programmed to share their features with other applications, are to be registered at central location.  OLE states that, in case of Windows Central Location is the “Windows Registry”

On all computers running Windows Operating System there is a file system.dat called as Windows Registry.  When a Server Application installed, among other information it writes its name and location to the registry.  Thus when Ms-Office was installed, all products like Word, Excel, PowerPoint etc. got registered with the registry.

Hence all client application has to do is read registry to determine Server application it can use.  Each time when user clicks on “Object” under menu “Insert” Word reads the registry and displays the names of all Server Applications.

Note:   Only Server Applications are to be registered and not the Client Applications.

About The Object
Anything or any Object created on the client application is called as an Embedded Object.

User can also create objects such that they are stored separate from the document.  In this case only a reference to the object is stored in the document while object is stored as a separate file.  Document is said to contain a linked object.

Also, the object can be manipulated independent of the document.  However, when document containing a link to this object is opened the next time, changes made to the object are reflected in the document.

Linking V/s Embedding
Ø  Embedding creates documents of larger size because it stores a copy of the object within it.
Ø  Documents containing linked objects are smaller in size.
Ø  Linking is used when we need the object to be updated each time data on which the object is created, changes.



Advantages of COM
1)                  COM saves development time.  COM enables to build application with specialized abilities and use abilities of other applications as and when required.
2)                  COM promotes reusability if we know that a particular feature is required in multiple applications then we can code functionality just once and use COM from different applications.
3)                  COM enables to get the best in all the situations.  User can use the application that is the best in any category.

Types of COM Servers
1)                  Simple Servers
These are Servers where Client applications can only invoke the Server but can not access its custom variables and methods.  For example Microsoft-Graph many have variables like back Color that determine the background color of the graph.  We can not access this variable in the code of calling application to modify background color.

2)                  Automatic Servers
These are Servers that allows us to access certain methods and properties of the object.  We can use these methods and properties to manipulate the object via code.  Programmer of Automation Server decides which of the methods and properties the client needs to access to and exposes only those properties.

3)                  Active X
Typically, an ActiveX is a user interface component like a Button, List Box etc. and will have methods, properties and events that the client can manipulate.

Types of Libraries
In the case of Automation Servers, ActiveX Components like Client Application needs to determine the exposed methods, properties and events.  This is done via Type Libraries.

All Automation Servers and ActiveX components have associated with them a Type Library.  Location of the Type Library is written to registry along with all information when Server application is registered.

Client reads registry, determines location of Type Library and reads it to determine properties, methods and events of the object.

Using OLE Control
Visual Basic provides an OLE Control that enables to embed or link an OLE object an our application
àPicture

By placing it on the form we can create a link to other object to our Application.  The Property Class holds the String that defines the type of object associated with OLE control.  By double clicking on OLE control a Server application can be started.


Methods of OLE Control
Ø  Close
It closes Active Server application; if any.  It is responsibility of the Client application that it should shut down the Server.

Ø  Create Embed
Creates an embedded object from the specified file and associates it with OLE control.  It enables an existing file to be embedded in the application.
Syntax:
OLE1.CreateEmbed (“C:\Try.Doc”)

Ø  CreateLink
Creates a linked object from specified file and associates it with OLE Control.  This method enables an existing file to be embedded in the application.
Syntax:
OLE1.CrateLink (“C:\Try.Doc”)

Ø  Insert ObjDLg
Displays ‘Insert Object’ dialog box that enables to select the type of object to create.  When we click on OK of the ‘Insert Object’ dialog an object of selected type is created and associated with the control.

Using this function in our application enables us to use any of the registered servers with our application.  It reads registry for list of registered servers each time it is displayed and so each time a new server is registered; it appears in the list.
Syntax:
OLE.InsertObject ( )

Properties of OLE Control
Ø  Auto Activate 
This property decides how to activate the server.  The options available are
à    On Double_Click,
à    Get Focus,
à    Automatic and
à    Manual

Ø  Class
It is used to set or determine the name of the object class or type of object.

Ø  Object
Returns a reference to OLE object held in the OLE control.

Events of an OLE Control
Ø  Object Move
This event is called whenever active object is moved.

Ø  Update
Occurs when object’s data has been changes.  It occurs every time the server object is active and user changes it in any manner.
Top
Posted by at No comments:

Tuesday, March 24, 2020

Chapter 5 Error Handling & Classes









Introduction
Errors occur even if we design an application carefully.  VB procedures don’t need error handling, sometimes files are mistakenly deleted, and disk drives run out of space, network drives disconnect unexpectedly; which causes run-time errors in code.

VB includes default error handler which presents a message with error number and a short description of that error and terminates the application which does not allow the user to save any work or close the application properly.  These errors are handled by using error handling codes.  Codes that handle run-time errors are called “error-handler”.

Types of Errors

Syntax Errors
Occur when we enter incorrect lines of codes.

Runtime Errors
Occurs when a command attempts to perform an action that is not valid.  This error occurs after the application starts execution.
Actions that generate runtime errors are:-

à    Writing to a non existing file.
à    Trying to select a table that has been closed.
à    Assigning a numeric variable to string.
à    Two users saving same record.
à    Dividing a number by zero.

Common Visual Basic Error Numbers are:-

5                      à        Invalid Procedure Call
6                      à        Overflow
7                      à        Out of Memory
9                      à        Subscript out of range
11                    à        Divide by Zero
13                    à        Type mismatch
53                    à        File not found
55                    à        File already open
58                    à        File already exists
76                    à        Path not found
423                  à        Property/Method not found
482                  à        Printer Error

There are three basic steps in creating an error handler:-

1)                  Enabling an error handler.
2)                  Writing code that can handle errors.
3)                  Continuing execution of program.

Logic Errors
Occurs when an application executes without syntax errors or run time errors.

Error Handler
Run-time errors can be handled by enabling an error handler with on Error Go to Statement.  The label identifies a point in code which the execution will branch when error encountered.  This section is named as ‘Error Handler’.

Error Handler is usually placed at procedure immediately after an Exit Sub Statement in the Program.  Following is the code for overflow of data:-

Private Sub Command1_Click ( )
On Error Goto Data.Err
Dim Num As Integer, Var as Integer
Num = 10
Var = Num * 10000
Print Var
Exit Sub
Data Err:
MsgBox “Try to Multiply with small numer”
End Sub

Err Object
Is used for notifying errors.  It contains information about runtime errors.  It enables us to determine which error occurred, its description and place.

Properties of the Err object are set by those who generate the error. i. e. Visual Basic or the programmer.  When run time error occurs its properties are filled with information that uniquely identify the error and required action to be taken to handle it.

Properties of Err Object

Number
Returns / Sets a numeric value to specify an error.  Number is the Err Object’s default property.  It is used to determine which error occurred.  Value of the property is unique number that corresponds to error condition.

Syntax:
Object.Number

Description
Returns / Sets expression containing a descriptive string associated with an error.
Syntax:
Object.Description
Source
Returns / Sets a string specifying the name application that generated the error.  When an unexpected error occurs in the code, source property is automatically set.
Syntax:
Object.Source

Help file
Returns / Sets a string containing path.
Syntax:
Object.Helpfile

Help Context
Returns / Sets a string containing the context Id for a topic in a file.
Syntax:
Object.HelpContext

Resuming Execution
There are three Resume Statements in Visual Basic.
Ø  Resume
Application Resume execution on the line of code that caused error.
Ø  Resume Next
Application resumes processing on the line of code following the line that caused the error.
Ø  Resume Line
Execution resumes at the point designated by the line label or the line number and must be in the same procedure as error handler.
Example
Private Sub Result_Click ( )
On Error GoTo Err1
Text3.Text = Text1.Text / Text2.Text
MsgBox “Error Resolved”
Exit Sub
Err1:
MsgBox Err.Number
MsgBox Err.Description
MsgBox Err.Source
If Err.Number = 11 then
Text2.Text = Text2.Text + 1
End If
Resume
End Sub

Methods of Err Object

1)                  Clear Method
Clears all properties or Err object after an Error has been handled.  This method is called automatically if we use Resume Statement, on Error Statement or when we Exit a Sub or function procedure.
Syntax:
Object.Clear
2)                  Raise Method
Used to generate an error.  It is useful for testing and evaluation.  We cause the error to be generated so that it can be handled by an error handler elsewhere in the program.
Syntax:
Object.RaiseNumber, Source, Description, Helpfile, Helpcontext.

Example:
Private Sub CmdError_Click ( )
Dim Num As Integer
Num = Val (Text1.Text)
On Error GoTo NoErr
Err.RaiseNum
Exit Sub
NoErr:
Select Case Err.Number
Case 6
MsgBox  “Data Overflow”
Text1.SetFocus
Case 11
MsgBox  “Division by Zero”
Text1.SetFocus
Case Else
MsgBox  “Error Not Known”
Exit Sub
End Select
Text1.Text =” “
End Sub

Trapping Errors
To handle errors efficiently we need to capture an error that occurs in a program called as trapping errors.  After the error is trapped we can take appropriate action based on the type of error and the decision that user takes.  Error trap is enabled when Visual Basic executes On Error Statement, which specifies an error handler.  Error trap is disabled on executing Exit Sub, Exit Function, Property, End sub, End Function or End Property etc for that procedure.  Error is disabled using On Error Goto 0.

Immediate Handling Errors
It causes the program to branch off to an error handling routine when error occurs.  “On Error GoTo Line” Statement indicates location of an error handling routine.  Error handler routine starts with a label, corresponding to the identifier specified in On Error GoTo Line.
In this method we need a statement immediately before error handling routine to exit the procedure that is Exit Sub.

Deferred Error Handling
In this method Visual Basic ignores the error when it occurs, but code checks the error later in the procedure.  The error handling code can be used to reset values of the variable which will enable the processing to continue.  This method can be invoked by using ‘On Error Resume Next’.  It prompts Visual Basic to continue with program execution after an error has occurred.
Example
Private Sub Command1_Click ( )
On Error Resume Next
Text3.Text = Text1.Text/ Text2.Text
If Err.Number> 0 Then
If Err.Number = 11 Then
Text3.Text =0
Else
MsgBox “Input is Invalid”
Exit Sub
End If
End If
End Sub

Creating Common Error Handling Procedure
When we add error handling code to an application, we notice that we are handling same errors over and over.  This can be reduced by writing few procedures that an error handling code can call to handle common error situations.  This reduces the code size.

Example:
Function Check ( )
Err.Raise900, , “No Blanks allowed”
Else
MsgBox “Record Saved”
End If
End Function

Private Sub Save_Click ( )
Call Check
End Sub

Classes
Class can be defined as a collection of code that forms a template for creating other objects in an application.  Three major principles required in a collection of objects are:-

1)                  Encapsulation
Information about an object like internal data and code is hidden.  Encapsulation isolates internal complexity of an object’s operation from the rest of the application.

2)                  Polymorphism
This implies the ability for various objects to have methods with the same name, but different content.

3)                  Inheritance
Ability of a sub class to take on the characteristics of different / parent class from which it is derived.  If characteristics of parent class change, sub class also inherits those characteristics.


Creating a Class
There are five steps for creating a class:-

1)      Adding a Class Module and selecting its properties.
2)      Creating properties of the class.
3)      Creating methods of the class.
4)      Creating events of the class.

Class Module
Class module contains definition of a class, including its property and method definitions.  Each class is created in special file with .CLS extension indicating that the code is the class module.

A standard module .BAS as file name extension and contains procedures and declarations commonly accessed by other modules within the application.

Adding a Class Module
To create a class we have to add a class module to our project by choosing ‘Add Class Module’ from Project Menu.

Creating Properties of the Class
Properties enables to pass data between a program and a class.  Properties in a class module can be created in two ways:-

à    Creating Public Variable.
à    Creating Property Procedure.
Public variable are created by using Public Keyword in Declaration Section of Class Module.
à        Public Ac_Num As Integer

This is simplest way of creating Property of a Class.  But it provides an open access to the information in the class.  Also using public variable does not enable us to create Read Only properties that are often required by a class.  Another alternate is to create Private variable, which can only accessed by code in the class module.

à        Private Ac_Num As Integer

Hence the recommended method would be to create procedures which enforce data hiding.  Ability to protect part of an object data while exposing rest of the data is called data hiding.  Visual Basic enables to create three types of property procedures.

1)                  Property Set
Only procedure that can not be created automatically with procedure dialog box.  It is used when the value passed to the procedure itself is an object.

2)                  Property Let
Specifies an argument that contains the value passed to the property.  Argument is passed by value to prevent procedure from changing the value of the variable passed to the procedure.

3)                  Property Get
Used to return value from the class.  To create a read-only property we create a Get Procedure omitting the Set and Let procedures.

Example:(Text to be displayed in Upper Case)

Dim GetCap As String
Public Property Get CapText ( ) As String
CapText = GetCap
End property

Public Property Let CapText (By Val NewCap As String)
GetCap = UCase(NewCap)
End Property

Private Sub Command1_Click ( )
Dim CS As Class1
Set CS = New Class1
CS.CapText = Text1.Text
Text1.Text = CS.CapText
End Sub

Where
Variable GetCap is used to hold text entered by user in the text box. 
Proerty Let procedure uses UCase function to convert text in Text Box to Upper Case.
Set CS = New Class1
This statement creates an object variable ‘CS’ of the class (Class1)
Property Set procedure takes an object as an argument.  Set keyword is used to assign the object to an internal object in the class.

Property Data Source Behavior
Used when we want an object to act as a source of data for other objects.  Set only at run time.
Syntax:
Object.DataSourceBehavipr[ =Number]

Parameter number is an integer specifies data source behavior according to settings:-

vbNone                       =          0          à Default Object can not act as data source.
vbDataSource            =          1          à Object can act as data source.

When data source behavior is set to 1 the Get Data Member event is added automatically.
Syntax:
Private Sub Object_GetDataMember (Data Member As String, Data As Object)

Where

DataMember Is a String containing the name of the data member to be bound as a data source.
Data Is a reference to Recordset object or a class.
We can add code to GetDataMember event procedure to select data from a data source within an object.
Following is the code, selects customer table as data member to retrieve data assuming that connection ‘cnCust’ is already exists.

Example
Dim RsCust as ADODB.Recordset
Set RsCust = New Recordset
Rscust.Open “Customer”, CnCust

Private Sub Class_GetDataMember(Datamember As String, Data As Object)
Set Data = RsCust
End Sub

Creating methods of the class
After creating properties for a class functionality to the class can be provided by adding methods to the class.  Public Sub and function procedures are used to create methods of the class.

Example (Method)
Public Function Name(ByVal Newstr As Str)
If IsNumeric (Newstr) = True OR Newstr = Empty Then
MsgBox “Enter a Valid Name”
End If
End Function

Creating Events for a class
Visual Basic have events for a class which notify the application of the occurrence of some action in the class.  Some important events are as:-

1)                  Initialize Event
Occurs when an instance of class is created.  It is triggered before any properties are set and is used to initialize any data that the class uses.  It can also be used to load any forms used by the class.

Syntax:
Private Sub Object_Initialize ( )

Following is the code in initialize event of the class that creates and opens connection and a Recordset.

Example
Private Sub Class_Initilize ( )
Set CnCust = New ADODB.Connection
CnCust.Open “Provider = Microsoft.Jet.OLEDB.3.51; Data Source = C:\Program Files\ Microsoft Visual Studio\VB98\NWind.MDB”
Set RsCust = New ADODB.Recordset
RsCust.Open “Customers” CnCust
End Sub

2)                  Terminate Event
Occurs when object is set to ‘Nothing’ or when last reference to the object goes out of scope.
Syntax:
Private Sub Object.Terminate ( )

Example
Private Sub Class_Terminate ( )
Unload Form1
End Sub

Custom events can also be created in Visual Basic by:-
à    Defining the Event.
à    Write Code to trigger the event.
Following is the code required to create and trigger custom event: CaseText.  We place label and command button on the form an add class module to the application.  To support CaseText Event we declare an object variable MyObject using with Events keyword.  In Load event of the Form an instance of object Variable MyObject is created.  When command button is clicked the check procedure of MyObject is called which raises Event CaseText.

Example
‘     Code Window of Class Module

Public Event CaseText (ByVal StrVal as String)
Public Sub Check ( )
RaiseEvent CaseText(“Raising an Event”)
End Sub

‘ Code Window of Form Module

Public WithEvents MyObject As Class1
Private Sub Form_Load ( )
Set MyObject = New Class1
End Sub

Private Sub MyObject_CaseText (ByVal MStr As String)
Label1.Caption = MStr
End Sub

Private Sub Command1_Click ( )
Call MyObject.Check
End sub



 Top
Posted by at No comments:

Thursday, March 19, 2020

Chapter 4 ADO and Record sets









OLEDB an Introduction
Each DBMS stores data in a particular format.  For example:
Microsoft Access        à        MDB
Microsoft FoxPro        à        DBF
Hence changing from one DBMS to another means rewriting the application to handle data in a new format.  This meant that a lot of time and effort is spent in recreating the application.

Open Database Connectivity
The solution to above problem is implemented in Open Database Connectivity (ODBC).
ODBC defines a standard method for communication between application and the DBMS accomplished by defining an Application Programming Interface (API).  The ODBC driver accepts the command from the application and converts it to a format understood by the target DBMS. 
In addition ODBC drover also receives the result of the command execution from the DBMS and passes it back to the application.  OLEDB is a database architecture that enables Application to have uniform access to data stored in diverse information sources, regardless of the location or type.

ActiveX Data Objects (ADO)
ADO is an interface to OLEDB.  It enables accessing data from relational and non-relational databases, it allows to access other data sources also.

Accessing Data with ADO Data Control
ADO data control is the key element in creating data applications with a minimum code.  It provides visual interface for setting data access properties.  It provides link between an application and data, we need to access.  We create this by placing ADO data control on a form and setting its properties.  Once we establish link we can display data on the form by binding controls to ADO data control without writing code.

 ßAdodc Data Control
Connecting to a Data Source
Connection to a data source is created by setting connection string property of ADO data control.  This property specifies the type and location of the data base that we need to access.
Ø  Use Data Link File
This specifies connection string to connect a data source.

Ø  Use ODBC Data Source Name
This allows to use system-defined data source name (DSN) for connection string.
Ø  Use Connection String
Specifies a connection string to connect to data source.  Clicking Build button displays data link properties dialog box, from which we can specify the Provider Name and other information.

Record Source Property
Specifies where the records will come from after a connection is established to a database.  There are four command type options for this property.

Ø  adCmdUnknown      à        Unknown command.  This is default value.
Ø  adCmdText                à        Enables to specify SQL command.
Ø  adCmdTable              à        Displays list of tables in connected database.
Ø  adCmdStoredProce  à        Displays list of tables in connected database.

Creating Record Set
Recordset property of ADODC is an object that represents entire set of records from a table or results of an executed command.  Recordset object is used to access records returned from a query.  At any point of time, Recordset object points to a single record called the current record, in set of records returned from a query.  Recordset objects can be used for:-

  1. Adding Record
AddNew method is used to add new records to a Recordset.  While adding new records ADODC clears information in the bound controls to prepare them for addition.

à        adoEmployees.Recordset.AddNew

To cancel any changes made to record CancelUpdate method is used.

à        adoEmployees.Recordset.CancelUpdate

  1. Modifying Record
ADODC automatically modifies records when we move to the record that we wish to change.

à        adoEmployees.Recordset.Update

  1. Deleting Record
Delete method enables to delete a record.  Record is removed from the database, but the bound controls still display information of the deleted records until we reposition the record pointer to another record.
à        adoEmployees.Recordset.Delete

Finding Record
Searching for a specific record requires invoking the Find method of Recordset and specifying condition for the Search.

Syntax:
Object.Find (Critaria, Skiprows, SearchDirection, Start) where

Criteria
Is a string that specifies column name, comparison operator ( >, <=  or the LIKE) and value to used in the search.

Skip Rows
Optional, Default value is Zero.  It specifies offset from current row or the ‘Start’ parameter to begin Search.

Search Direction
Optional, specifies whether search should begin on current row or next available row in search direction.  Its values could be adSearchForward or adSearchBackward.

Start
Optional, specifies starting position.  Position for search its values are adBookmarkCurrent for Current Record adBookmarkFirst for  First Record and adBookmarkLast for Last Record.

àadoEmployees.Recordset.Find “Emp_Name” = ‘David’”, 0, adSearchBackward, adBookmarkCurrent

Working with ActiveX Data Objects
ADO objects provide us all data access and manipulation features required by most applications.  ADO provides minimum number of layers between front-end application and Data Source, to provide high performance interface.  It gives a greater control over how an application interacts with data source and enables us to create powerful and flexible applications.

Advantages of ADO Objects
à    Validation of any information entered by the user before it goes into database.
à    Database applications that don’t require user interface can be created.
à    SQL statements can be used to modify multiple records at a time.

ADO object model has following three main objects, that required to access data from an existing database:-

  1. Connection
Connection object makes a connection between an application and data source as MS SQL Server etc.  Creating connection object is similar to setting connection string property an ADODC.

  1. Record Set
This represents entire set of records from a table or results of an executed command.  Recordset object used to access records returned from a query.  Data can be entirely manipulated using Recordset objects which is constructed using records (rows) and fields (columns).

  1. Command
Command object can be used to created Recordset object and obtain records, to execute structure of a database.
Creating and Opening a Connection
By declaring a variable to create a connection string we can create connection object.

à        Dim Con as ADODB.Connection
            Set Con = New Connection

Creating a Connection Object
By declaring a variable to create a connection string we can create a connection object.

à        Dim Con as ADODB.Connection
            Set Con = New connection.

ADODB is ProgId (Programmatic Identifier) enables us to create connection object.  To create connection object we need to set ConnectionString and ConnectionTimeout properties.

Connection String
Contains information used to establish a connection to a data source.  This property supports following arguments:-
Ø  Provider
Specifies name of provider used for connection.  This property identifies the type of database for connection.
Ø  Data source
Specifies name of data source for connection.  For Example: Microsoft Access, SQL Server, Oracle etc.
Ø  User Id
Specifies Username to be used while opening a connection.
Ø  Password
Specifies password to be used while opening a connection.

Parameter Values for different OLEDB provides are:
Microsoft Jet                          à        Microsoft.Jet.OLEDB.3.51 or 4.0
Oracle                                    à        MSDAORA
Microsoft ODBC Driver         à         MSDASQL
SQL Server                            à        SQLOLEDB

Connection TimeOut
This property indicates how long to wait while establishing a connection before terminating the attempt and generating an error.
Example
à   Con.ConnectionString = “Provider = Microsoft.Jet.OLEDB.3.51; DataSource = C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb”
            Con.ConnectionTimeout =30
           
Opening a Connection
Establish a physical connection to data source using open method.  After this method successfully completes the connection still exists and we can issue commands against it and process results.
Syntax:
à        Connection.open[Connection String], [UserId],[Password]
Where
Connection String     Optional.  A string contains connection information.
User Id                       Optional.  User Name is required to establish a connection.
Password                    Optional.  Password required when establishing the connection.

Example1
à        Dim Con as ADODB.Connection
            Set Con = New Connection
            Con.ConnectionString = “Provider = Microsoft.Jet.OLEDB.3.51;”&_
            “Data Source = C:\Program Files\ Microsoft Visual Studio\VB98\ Biblio.mdb”
            Con.open

Closing the Connection
Close method is used to close the connection object.  Closing an object does not remove it from the memory.  To completely eliminate an object from memory set object variable to Nothing.

à        Con.Close
            Set Con = Nothing

Creating and Opening a RecordSet
After connecting to data source, we create Recordset object consisting of the data required from the data source.  It can created by using Recordset object itself or by using command object.  Recordset can contain a few fields and records from the table or combined information from two or more tables or entire continents of a table.

Recordset is primary means of examining and modifying data in a database.  It allows to
Ø  Specify which rows are available for manipulation.
Ø  Navigate the rows.
Ø  Specify order in which rows may be navigated.
Ø  Add, Change or Delete rows.
Ø  Update data source with changed rows.

The object method is used to create Recordset object with following steps:-
à        Declare Recordset object variable
Dim rs as ADODB.Recordset

à        Initiate object variable
            Set rs = New Recordset

à        Use open method to retrieve records from data source.
Syntax:
Recordset.Open [source], [ActiveConnection], [CursorType], [LockType], [Options]
Where
1)         Source
Is optional.  Variable evaluates to table name or SQL statement.  Source depends on the provider we connected to.
2)         Active Connection
Optional.  If a connection is already created to data source then we can used name of the connection for further communication required to create and maintain the Recordset.  If connection has not be established then this parameter supports use of all information required to make a connection.
3)         Cursor Type
Optional.  Indicates type of cursor the Recordset uses.  ADO model supports four types of cursors.

Ø  Forward Only
adOpenForwardOnly.  Behaves like a dynamic cursor.  It allows to scroll only forward through records. 
Ø  KeySet
adOpenKeyset. Prevents user form seeing records that other users add, and prevents access to records that other users delete.
Ø  Dynamic
adOpenDynamic.  Allows additions, changes and deletions by other users, and allows all types of movements through the Recordset.
Ø  Static
adOpenStatic.  Allows all types of movements through Recordset.  Additions, changes or deletions by other users will not be visible.

4)         LockType
Optional.  Indicates types of locks places on records during editing.  By default all ADO Recordsets  are read-only.  Lock types are as follows:

Ø  adlockReadOnly  Default.  Data can not be altered.

Ø  adLockPessimistic  Provider does what is required to ensure successful editing of the records by “Locking”  page containing record immediately upon editing.  It prevents other users from editing records.

Ø  adLockOptimistic  Page containing record is locked while records are updated.  Conflict arises when two users attempt to update same record.  Update is granted to user who first issues the update method.

Ø  adLockBatchOptimistic  User Batch update method to write entire batch of rows to the Recordset.  Allows to make modifications on a number of rows without having to write those changes to Recordset after each row.

5)         Options
Optional.  Indicates how provider should evaluate source arguments.  Values of this parameter are similar to command type option of record source property of ADODC.

Example: Pessimistic LockType
Dim Con as ADODB.Connection
Dim Rs as New Recordset
Set Con = New Connection
Con.ConnectionString = “Provider=Microsoft.Jet.OLEDB.3.51; Data Source = C:=Program Files\ Microsoft Visual Studio\VB98\Biblio.MDB”
Con.Open
Rs.Open “Select * from Author”,Con.adOpenKeyset, adLockPessimistic

Example : Open Connection in Active Connection parameter of Recordset object.
Dim Rs as New Recordset
Rs.Open “Select * from Author”, &_
“Provider=Microsoft.Jet.OLEDB.3.51; ‘ &_
Data Source = C:=Program Files\ Microsoft Visual Studio\VB98\Biblio.MDB” &_
adOpenKeyset, adLockPessimistic

Displaying data in Recordset
After creating Recordset the fields of each record can be accessed.

à        Dim Name as String
à        Dim Address as String
Name    = Rs.Fields(“C_Name”)
Address = Rs.Fields(“C_Add”)

Above code can also be written with their respective orders, (Field Order).

à        Dim Name as String
            Dim Address as String
            Name       = Rs.Fields(0)
            Address   = Rs.Fields(2)

A short hand method of extracting data by using exclamation point operator (!) called as ‘bang’ can be used to extract data.

à        Dim Name as String
            Dim Address as String
            Name       = Rs!C_Name
            Address   = Rs!C_Add

Following code retrieves information from customer table and places information in the appropriate text boxes.

Example
Private Sub Form_Load ( )
Dim Con As ADODB.Connection
Dim RsCust As ADODB.Recordset
Set Con = New Connection
Con.ConnectionString = “Provider = Microsoft.Jet.OLEDB.3.51; Data Source = C:\Program Files\ Microsoft Visual Studio\VB98\ NWind.MDB”
Con.Open

Set RsCust = New Recordset
RsCust.Open “Customers”, Con
txtCustId.Text = RsCust! CustomerID
txtCustName.Text = RsCust!Customer.Name
End Sub

Navigating through RecordSet
ADO model supports four primary methods of navigating through Recordset object.
Ø  MoveNext
Takes user the next record in the Recordset.  If an attempt is made to move to the record after EOF, an error occurs.

Ø  MovePrevious
Moves user to previous record in the Recordset.  Moving past BOF returns in an error.

Ø  MoveFirst
Takes the user to the first record.

Ø  MoveLast
Takes the user to last record.

Example MovePrevious and MoveFirst Methods
Private Sub CmdPrev_Click ( )
Rs.MovePrevious
If Rs.BOF = True Then
MsgBox “Already at the beginning of the Recordset”
Rs.MoveFirst
End If
End Sub

Finding Records
User can search a Recordset for record that satisfies a condition.  If a condition is met, Recordset position is set on the found Record; otherwise position is set on end of the Recordset using find method.

Syntax:
Object.Find(Criteria, Skiprows, SearchDirection, Start)
RsCust.Find “Age>40”, 0, adSearchForward, adBookmarkCurrent

Editing Records
User can make changes and save results back to data source.

Syntax:
Recordset.Update Fields, values where:
Fields  Optional, Represents a name of the field/ fields to modify.
Values Optional, represents values in the field of new record.

Private Sub CmdSave_Click ( )
RsPub!Name   =          txtName.Text
RsPub!City     =          txtCity.Text
RsPub.Update
End Sub

Adding Records
AddNew method is used to insert new record.  This method is invoked prior to inserting values.  Once required values are inserted the values are saved back to data source using update method.  Newly inserted records are added at the end of Recordset.
Private Sub Add ( )
If(TxtId.Text < > “”) And (TxtName.Text<> “” )Then
rsTemp.AddNew
rsTemp.Emp_Id          = txtId.Text
rsTemp.                       = txtName.Text
rsTemp.Update
End If
End Sub

Deleting Records
Delete method deletes current record from a Recordset, only one record at a time can be deleted from the database.After deleting current record, it is deleted from database, but deleted records remains current until we move to different record.
Private Sub CmdDelete_Click ( )
Dim Ans as Integer
Ans = MsgBox(“Want to Delete Record?”,vbYesNo)
If Ans = vbyes then
rsEmp.Delete
rsEmp.MoveFirst
Else
Exit Sub
End If
End Sub

Closing Recordset
Close method is used to close a Recordset and free system resources.
Syntax:
à        Recordset.Close

Using Command Object
Using command object SQL command can be executed to retrieve data.  Properties and methods of command object are:-

  1. Active Connection Property
References a connection for command execution.
  1. Command Text Property
Contains text of a command that you want to issue against a provider.
  1. Command Type Property
Indicates type of a command object.  This could be adCmdText, adCmdUnknown, adCmdTable, adCmdStoredProc.  This is similar to option parameter of the open method.
  1. Create Parameter Method
Adds parameter to parameters collection of command object.
  1. Execute Method
Executes command object.
Syntax:
à    Commandobject.Parameters.Append.object
à    Command.CreateParameter (Name, Type, Direction, Size, Value ) Where
Name       String specifying name of the parameter object.
Type         Specifies data type of parameter object.
Direction  Specifies whether data is to be sent to a query or returned from query.
Size           Specifies maximum length of the parameter value in character or bytes.
Value        Specifies value of parameter object.

Following code update student name in student table:-

Private Sub Form_Load ( )
Dim Con as ADODB.Connection
Dim Cmd as ADODB.Command
Dim Str as String
Set Con = New ADODB.Connection
Set Cmd = New ADODB.Command
Con.ConnectionString = “Provider = Microsoft.Jet.OLEDB.3.51; Data Source = C:\ Program Files \ Microsoft Visual Studio\VB98\StudentData.mdb”
Con.Open
Set Cmd.ActiveConnection =Con
Str = “Update Student Set StudentName = “Jim Waugh’ where StudentName = ‘Lis Nizan’”
cmd.CommandText =Str
Cmd.Execute
End Sub

Collections
Collection is way of grouping a set of related items.  It keeps track of loaded forms in a program or controls of a form.
Collections have their own properties and methods.  Objects in a collection are referred to a ‘Members’ of the collection i.e. numbered sequentially beginning from 0.  This is the ‘index number’ of the member.  Collections have following features:-

1)                  We can add items to collection.
2)                  We can remove items from a collection.
3)                  We can refer to specific members of collection.
4)                  Collection object keeps a count of number of members in a collection.

Private Sub CmdSave_Click( )
Dim Ctrl as Control
For each Ctrl in Controls
If type of Ctrl is TextBox Then
If Ctrl.Text = “” Then
MsgBox “All fiels must be entered”
Exit Sub
End If
End If
Next Ctrl
End Sub

Methods of collection are:-

Ø  Add Method Used to Add members to a collection.
Syntax:
Object.AddItem, Key, before, after

Where
Item                Is the member to be added to collection.
Key                 Is a String used to identify the member
Before             Specifies New Item should be placed in collection before member specified in Before parameter.
After               Specifies that new item should be placed in a collection after member specified in After parameter.

à                    Col.Add inst, “Mkey”

Ø  Remove Method
Used to remove items from collection just as we can add items to the collection.
Syntax:
à                    Object.Remove(index)

Index can be either numeric index of the member of the key property of the member.
à        Coll.Remove3             OR
à        Coll.Remove “E003”

Ø  Item Method
Used to access specific item from collection.
Syntax:
à        object.item(index)
Index can be either numeric of the member or its key property.
à        Col.Item(3)                 OR
à        Col.Item(“E003”)

Count Property
Collection has Single property and is used to tell how many items are there in the collection.
Syntax:
à        Object.Count

Count Property in Code is as follows:
Dim I as Integer
For I =1 to Col.(Count)
Print Col.Item (I)
Next I




Top


Posted by at No comments:
Subscribe to: Comments (Atom)