CERTIFICATE

This is to certify that this project entitled “Library Management

System” submitted by Shaveta Rani and Sapna Sharma to Punjab Technical

University, Jalandhar, is an original work and has not been submitted earlier

whether to Punjab Technical University or to any other Institution or

fulfillment of the requirement of a course of study .

Date:

Mr. Jayant Ahuja Mr. Manraj Mangat

Project Instructor Head of the Institute

STUDENT DECLAATION

We hereby declare that project report entitled

“Library Management System”

In “PTU LEARNING CENTER,LUDHIANA” submitted by Shaveta Rani

and Sapna Sharma in Partial fulfillment of the requirement for

BACHELORS OF SCIENCES [IMFORMATION TECHNOLOGY] to

“Punjab Technical University, Jalandhar”, is an original work and has not

been submitted the award of any degree/ Diploma/ Scholarship or any other

title or prizes.

Place: Ludhiana Shaveta Rani

Date: Sapna Sharma

TABLE OF CONTENTS

Preface

Introduction

Objectives

Existing System

Proposed System

Advantages of Computerization

Hardware and software Specifications

Analysis

System Design- Needs, Proposed solutions and Best Solutions

Implementation

Testing Techniques

System maintenance

Introduction of MS Access

Introduction of Visual Basic

Coding

Output Screens

Conclusion

Bibliography

ACKNOWLEDGEMENT

It has been a great privilege to conduct the present product under the

aegis of our Major Advisor Mr. Jayant Ahuja, Department of computer

Science. Our sincere thank and deep gratitude are due to his effective

guidance and encouragement, which gave us the confidence to complete the

poject work.

We are also grateful to Miss Lalita Madam, Mr. Manraj Mangat, Head

of the Institute for all their suppot during the project.

Our cordial and sincere thanks to all the teachers of Department of

computer Science.

We are deeply indebted to our Families and our friends for their unconditional support, deep affection and unabated inspiration, which kept us going.

We wish to thank all the respondents who extended their whole-hearted cooperation in carrying out this study.

Last but not the Least, we thank GOD who always showers His blessings upon all of us.

Place: Ludhiana Shaveta Rani

Date: Sapna Sharma

PREFACE

The preface of creation is a long process requiring time, energy and

dedication, as well as the skill and experience of all those persons engaged

in tasks, ultimately culminating in the outcome as the final form of

embodiment of the creator’s vision.

The process of System Analysis and Design is analogous to the above

fact. Here, the process of creation began long before the first line of the

program code for the new system is written, when the existing system is

systematically evaluated, analyzed, planned, properly described and

documented, criticized and appropriate modifications are suggested. The

facts and findings thus obtained are then synthesized as the new system I in

the designing phase.

The theoretical study and knowledge of this practice oriented subject

is further enhanced by applying it and understanding analysis and design of

real life installation of automated system, provided valuable hand practical

experience to the students. Our contemporary lives have been influenced by

advancement in computer science to such an extent that without them, the

ultra modern and advanced lifestyle of the 21st century is simply

unthinkable.

Computerization introduces accuracy, efficiency, integrity etc., and

reduces paper work, errors and time demands. The initial investment is in

the form of cost of computerization and justified in long run.

INTRODUCTION

Computers have brought a revolution in our day to day life. One of the most

significant uses of computer is to manage data and present information in a

particular form that is needed by the user. This is due to the high speed

processing, accuracy and efficiency provided by modern day computers. In

the same way as computers help us to manage data in different fields, the

same the does in the Library management. This project is dealing with the

Library management system.

Library system makes it easy for the institute to take care of the books

properly. Technology has changed the way you live. The invention of the

currency has ushered in a new breed of humans. The average adult spends

approximately one-third of his or her life in working. A considerable portion

of the expenditure of the business is on the compensation given to its

employees, therefore it become necessary to maintain the complete record of

the books and students.

The Library department of an organization maintains a record of

The books

The books issued

The students

The fine

We are closely related to the computers, now these days the computer is

our necessity. So, we are developing our project of computerized Library

system for the well known institute of Ludhiana “PUNJAB COLLEGE OF

TECHNICAL EDUCATION”. This institution has a great reputation. The

existing system in the institution is manual. In existing manual system lots

of time is needed to manage records and information accuracy in work is

less as compared to automated computerized system. Moreover, possibilities

of error, incorrect information cannot be ruled out. The only solution lies

with the modern system of computerization.

In our project we have done all things to be maintained properly so that

they can be maintained easily and no information should be loss. So I hope

that my new computerization Library system is helpful for the institute.

OBJECTIVES

As we know that the “Library Management System” is very large and complex system. There are many difficulties that are faced in the manual system. In out project we tried to minimize all these difficulties. We made this project computerized. In this all things will be done easily. The main objective of this project is to minimize the labour and increase the speed and make the user comfortable. The main objectives of my project are as follows:

To make understand all the process so that he could easily handle the

project.

The computerized system will lessen the burden of the books stored.

To provide the user the information which he needs i.e. to make it more

users friendly.

The fine will be calculated automatically.

It will be easy to handle all the records.

There will not be loss of any data.

The user could know about all the books according to ID number.

It will reduce anomalies to a great extent; the system will be more error

free.

Accepting the data from a user and send it to the database.

Validating the data entered by the user.

EXISTING SYSTEM

We know that the institute today is using this system manually. But we know that the manual system is very prone to errors. This work is very complex and tiresome so in such a long process many errors can occur. It is really very difficult for a man to maintain all the records correctly. If one error occurs it will produce many errors. There are many long processes in calculating the payment and we need registers to keep all these things.

The difficulties faced in the existing system are given below:

Much Laborious: In Library system we have to maintain the record of

each student and calculate his or her fine, which is very laborious work.

Need of High Time: These all works are very lengthy and complex. But

the time is limited a perfect man can do this work. Thus we need much

more time which is the wastage.

High Search Time: We need very high time to search any particular

record from such registers. It is very tiresome to find any particular

record or calculate the whole payment.

Redundancy of the Work: There may be the duplication of the work a

man can be recorded two or more times in a register or his payment or

allowances can be calculated many times which may result I the

redundancy.

More Prone to Human Errors: We know that man is prone to make

errors. If he is tired he may do many mistakes. These mistakes will result

in the wrong information, which will result in bias fault in financial

accounting.

Costly: These systems are much more costly. We have to keep a large

staff. A lot of money is wasted in this process.

Lack in Speed: Manual systems are very slow. A man cannot do much

more or fast work as a machine can do. Thus we can say that these

systems lack in speed.

PROPOSED SYSTEM

In our proposed system we tried to reduce all the discrepancies which are faced in the existing system. We made whole the system computerized.

We tried to make easy for the user to keep the records easily and easy to maintain them accurately. I set the query on each field so that the user could get the knowledge of each information about any book or anything that he needs just by putting one of the fields.

The main objectives that I mentioned in the proposed system are as follows:

To provide the record of books in the library and whole information

about them.

The system is made fast. It can do more work than a man without tiring.

It can do calculations in few minutes, which a man cannot do in hours.

It calculates the fines that are to be given by the students automatically.

Administrative personal has direct access to the database to make easy

alteration or updating.

No unauthorized person will access the database due to security feature.

Chances of errors are less.

ADVANTAGES OF COMPUTERIZATION

A number of pre-requisites are required to be satisfied for computerization. This leads to improvement in the working of the organization. Some of the advantages of the computerization are listed below:

Time saving

Control data redundancy

Control inconsistency

Increase reliability

Easy to use

User friendly

Saving of stationary

Easy to transport

Security

Accuracy

HARDWARE AND SOFTWARE SPECIFICATIONS

(A) MINIMUM SOFTWARE REQUIREMENT SPECIFICATIONS:

First of all we need software package in which we develop the proposed

system. Without this package, we can’t achieve our target. So, the software

is most essential thing to run the proposed system. The system opted for our

computers be of authorized company.

The proposed software is developed in VISUAL BASIC and

ORACLE. So, we need the following minimum software requirement for

our system to run sufficiently.

Operating System : WINDOWS ‘98’ or above

Front End : VISUAL BASIC

Back End : Ms Access

(B) MINIMUM HARDARE REQUIREMENT SPECIFICATIONS:

Pentium processor with 16MB of Ram

Floppy Drives :1.44MB

Input Devices :Keyboard, Mouse

Output Devices :Visual Display Unit, Printer

ANALYSIS

Analysis is the detailed study of various operations performed by a system and relationships within and outside the system. A key question is: what must be done to solve the problem. One aspect of the analysis is defining the boundaries of the system and determining whether or not a candidate system should consider other related system. During the analysis, data are collected on the available files, decision points and transaction handled by the present system. Some logical system models and tools are used in the analysis. Data flow diagrams, interviews, on-site observations and questionnaires are examples.

Analysis include following things:

Planning

Initial Investigation

Information Gathering

Structured Analysis

Feasibility Study

Cost/Benefit Analysis

Planning: The first step in analysis is planning. Planning is a software

project means to prepare in the customer’s terminology, concise

statement of the problem, to be solved and constrained that exists or its

solution. The definitive problem should include a description of the

present system and the goals to be achieved by the system.

Initial Investigation: The first step in initial investigation is to define the

problem that led to the user’s request. The problem must be stated clearly

understood and agreed upon by the user and the analyst. It must state the

objectives the user is trying to achieve and the result the user wants to

see. Given user identification of need, the analyst proceeds to verify the

problem by separating symptoms from causes. For this purpose the

strategies-asking, eliciting information from exiting manual system and

prototyping can be used.

Information Gathering: Information Gathering is art and science. The

analyst must know what information to gather, where to find it, how to

collect it and what to made of it. If information is gathered from sources

within the organization and from the organization’ environment. The

primary information tools are documentation, on-site observation,

interviews and questionnaires.

Structured Analysis: Structure analysis is a set of techniques and

graphical tool that allow the analyst to develop a new kind of system

specification that are easily understandable, to user, structured analysis

considered new goals and structured tool for analysis. Tools uses in

structured analysis are Data flow diagram, Data dictionary, Structured,

English, Decision tree and Decision tables.

Feasibility Study: Feasibility study describe and evaluates candidate

system and provides for the solution of the best system and meets system

performance requirements .To do a feasibility study we need to consider

the economic, technical and behavioral factors in system development.

First a project team is formed. The team developed system flow chart that

identify the characteristics of systems, evaluate the performance of each

System, weigh system performance and cost data and select the best

candidate system for the job.

Technical Feasibility: - the system is developed with in Windows &

Dos as an Operating system. The System is User Friendly; thus report

generation of data entry is made easy. Easy retrieval and access of data

is provided.

Economical Feasibility: - economical feasibility is cost – benefit

analysis. The system is economical feasible. The benefits in reducing

cost are in form cutoff, saving of paper work the other incurred to

implement the system are payment of operator, little maintenance

required for hardware & software from time to time consistency in

efficiently.

Operational Feasibility: - This system benefits only if they can be

turned into information systems that will meet the organization

operating requirements. As the system is user friendly, so the system is

also well liked and proved by the user with no difficulty at all, if the

user wants more facility, it can also be provided. After system is

implemented the user will need no changes as the system is developed

while keeping in view the requirements of the user.

The system will cause no harm to any organization; it will enhance the result in better respect. This Computerized system will avoid confusion and resistance by catching user attention.

Cost /Benefit Analysis: Cost /Benefit analysis of each alternative guide

the selection of the best system for the job. Cost and benefits may be

tangible and intangible, direct or indirect, fixed or variable. Cost

estimates also take into consideration. Hardware, personnel, facility and

supply costs for final evaluations. Cost/Benefit analysis, then identifies

the costs and benefits of the given system and categories then for

analysis. Then a method of evaluation is selected and the results are

interpreted for action.

Once analysis is completed, the analyst has a firm understanding of what is to be done. The next step is to decide how the problem might to be solved. Thus, in system design, we move from the logical to the physical aspects of life cycle.

In the analysis of my project I want through following steps:-

First of all I study what all the requirements of the library.

Then I study the existing Library system.

Studies all the components of Library management like how the

application is to be developed.

So we selected Visual Basic as it is Developing Platform as it’s more

flexible and versatile.

And after all these steps we started all the designing process.

SYSTEM DESIGN

After the system analysis phase, what follows is the system design. Once the system design part is over, the coding becomes very easy. Design is essentially the bridge between requirement specification and the final solution for satisfying the requirements. Designing the software means to plan how the various parts of the software are going to meet the desired goals. It includes design specification, input/output, files and procedures. Program construction (coding) is the main part of design.

Design is of two types:

High Level Design (Signature):

High Level design consists of the basic model that shows how the flow of control is throughout the project execution. It also contains the basic classes and functions.

Low Level Design (Algorithms and code):

Low Level Design consists of the detailed description of the classes, their functions, data types and header files.

It defines all the inner details of the project coding.

The purpose of the design phase is to plan a solution of the problem specified by the requirements document. This phase is the first step in moving from the problem domain to the solution domain. In other words, starting with what is needed; design takes us toward how to satisfy the needs. The design of a system is perhaps the most critical factor affecting the quality of the software; it has a major impact in the later phases, particularly testing and maintenance. The output of this phase is the design document.

This document is similar to a blue print or plans for the solution and is used later during implementation, testing and maintenance.

The design activity is often divided into two separate phases- system design and detailed design. System design, which is sometimes also called high-

level design, aims to identify the modules, and how they interact with each other to produce desired results.

At the end of system design all the major data structure, file formats, output formats and the major module in the system and their specification is decided. During detail design the entomological of each of the modules specified in the system design is decided. During this phase further details of the data structure and algorithm.

Design of each of the module is specified. The logic of a module is usually specified in a high-level design description language, which is independent of the target language in which the software will eventually be implemented.

In system design the focus is on identifying the modules, where as during detail design the focus is on designing the logic of each of the modules. In other words, in system design the attention is on what components are needed while in detail design how the components can be implemented in software is the issue. A design methodology is a systematic approach to create a design by application of a set of techniques and guidelines. Most methodologies focus on system design.

NEEDS:

To maintain different data files on the machines and thus save stationary

storage and lot of paper work.

To simplify the present and manual tasks.

To reduce time, manpower and paperwork.

To facilitate easy retrieval of data and information.

To update the various files after input, processing and validation.

Reports generated by the project will help management persons in

decision making.

To help all the persons in stock store by updating with latest information.

To reduce redundancy.

PROPOSED SOLUTIONS:

1. Computerized the whole system, with centralized database with

ORACLE and VIISUAL BASIC.

2. Provide extra manpower for need record keeping

It will reduce the workload.

Save Time.

3. Work in shifts.

Work is equally divided among all persons of that particular

department.

Work quality is improved.

Saves Time.

BEST SOLUTIONS:

Among all above solutions 1st solution is the best and optimal one, due to the following reasons:

Time Saving.

Saving Stationary.

Accuracy.

Easy to transport.

Security.

Considering all aspects, the introduction of the computerization is done to overcome the difficulties of the present system and provide a complete justification to computerize the existing system.

Considering the above analysis and record keeping procedures we have suggested this system. This system suggested by us is to reduce the human interaction and to facilitate record keeping processes.

IMPLEMENTATION

Implementation is the process of converting a new or revised design into operational one. It includes conversion of files, training users, creating accurate files and verifying printouts for integrity. It is the process of personnel checking out, installing the required equipment and application. Depending upon the size of the organization to use the information system and its requirements, the implementation is divided according to the requirement.

Parallel Conversion: In the parallel conversion old system and new system run together in parallel unit all the bugs in the new system have been identified and eliminated.We can adopt this category while also working on the manual system, so that there should be minimum chance of data loss and safe data storage is there. This strategy is quite safe, but somewhat expensive because both the systems will run simultaneously.

Direct Cutover: With this strategy, old system is replaced with the new one directly. We can also direct implementation replacing manual system. This approach is risky because in case of system stops working, data may be lost.

Pilot Study: With this approach one department or unit is tested before installing whole project throughout the organization.

Phased Conversion: The new system is slowly phased into its operational environment by replacing the part of the old system with the part of the new one system.

TESTING TECHNIQUES

Testing is the process of evaluating the software with an intention of finding errors. The software tested is executed with the step of sample test cases and outputs are then observed to determine whether the performance of the software adheres to as expected. System is tested for correct outputs, online responses and volume of data. It makes a logical assumption that if all parts of the system are correct, the goal will be successfully achieved. The success of test in revealing errors depends critically on the different cases in software systems. The testing is used later on during maintenance also. This project has been tested for various test cases and the result or the outputs have been checked for validation.

Various testing operations were performed to find out errors in the system and to reduce the defects.

Unit Testing: In the three major parts. Each part is checked

independently. Making whole project into three independent units are

checked efficiently.

System Testing: After the unit testing the three units are combined

together under one system and after combining the units a whole system

project testing was performed.

Acceptance Testing: After passing Unit Testing and System Testing

project is adjusted and checked so that it can be accepted by the user, so

in the last acceptance testing was performed with the various data.

SYSTEM MAINTENANCE

Programmers and analysts spend more time in maintaining programs and writing them. The problems in maintaining occur largely because software is handmade product. Poor documentation makes maintenance of the programs even more difficult. The more carefully is the system throughout and developed, with attention paid to external influence over a reasonable lifetime, the lesser is the maintenance required.

INTODUCTION TO MS ACCESS

A database is a collection of coherent, meaningful data. A data base

management system is the software system that allows users to define, create

and maintain a database and provides controlled access to the data. It is

basically a collection of programs that enable users to store, modify and

extract information from the database. It provides services for accessing a

database, while maintaining all the features of data. Example: dbase,

FoxPro, Oracle.

Features

Productivity tool: The DBMS act as productivity tool of a programmer;

it gives the programmer the capacity to work with database enhancing his

productivity. As DBMS takes care of the Database management, the

programmer now need not bother about the handling of database, can

now in turn use his time for other productive activities.

User Interface: DBMS provides a programmer with a good user

interface, which enables him to interact with database very easily. The

DBMS itself can be taken as an interface between the programmer and

the database.

Database Support and Structure: DBMS provides powerful database

support and has a powerful structure. The data is fit into the structure, so

that it does not pose any threats and problems to the data organization.

Easy to work with: DBMS provides interfaces, which are quite easy to

work. One need not do much of programming. The language syntax is

very easy; it is usually simple English like which can be learnt and

remembered easily.

Advanced Data Manipulation: All data manipulations like adding,

inserting, updating etc. of record/data is provided for. Apart from these

various mechanisms are available which take care about the security

aspect of data.

Data Independence: This is one of the excellent features of DBMS. This

makes the data to stand on its own, without depending on anything may it

be hardware or the access strategy. The Data Independence is further

classified into

Physical data independence

Logical data independence

Advantages of DBMS

Reduction of Redundancies: Centralized control of data by the DBA

avoids unnecessary duplication of data and effectively reduces the total

amount of data storage required. It also eliminates the extra processing

necessary to trace the required data in large mass of data. Another

advantage of avoiding the duplication is the elimination of

inconsistencies that tend to be present in redundant data files.

Shared Data: A database allows the sharing of data under its control by

any number of applications programs or users.

Integrity: Centralized control can also ensure that adequate checks are

incorporated in the DBMS to provide data integrity. Data integrity means

that the data contained in the database is both accurate and consistent.

Therefore, data values being entered for storage could be checked to

ensure that they fall within the specified range and are of the correct

format.

Security: In order to ensure that the database is accessed through proper

channels, various security measures are applied in DBMS. Rules can be

established to restrict the type of access to the information stored in the

database.

Data Independence: It is considered from two points of views: physical

data independence and logical data independence. Physical data

independence allows changes in the physical storage devices or

organization of the files to be made without requiring any changes in the

external schema. Logical data independence implies that application

programs need not to be changed if fields are added to an existing record;

nor do they have to be changed if fields not used by application programs

are deleted. It implies that conceptual schema can change without

affecting external schema.

Disadvantages of DBMS

Complexity of backup and recovery.

Expensive hardware, software and transformation cost requirement.

Increased data security requirements.

Failure and down time problems associated with the centralized database.

Introduction to SQL

Structured query language (SQL) is a language that provides an interface to

relational database systems. IBM developed SQL in the 1970s. In common

usage SQL also encompasses DML (Data Manipulation Language), for

INSERTs, UPDATEs, DELETEs and DDL (Data definition Language), used

for creating and modifying tables and other database structures.

The development of SQL is governed by standards. The ANSI is an

organization that approves certain standards in many different industries. It

was originally approved in 1986 based on IBM’s implementation. In 1987,

the International Standards Organization (ISO) accepted the ANSI SQL

standard as the international standard. The standard was revised again in

1992 and was called SQL-92. The newest standard is now called SQL-99, it

is also referred to as SQL3. SQL3 supports object extensions and are

partially implemented on Oracle8 and 9.

When an SQL statement is entered, it is stored in a part of memory called

the SQL buffer and remains their until a new SQL statement is entered..

Features of SQL

A range of users can use SQL, including those with little or no programming

experience.

It is a non-procedural language.

It reduces the amount of time required for creating and maintaining systems.

It is an English-like language.

Components of SQL

DDL (Data Definition Language): - It is a set of SQL commands used

to create, modify and delete database structures but not data. These

commands are normally not used by a general user, who should be

accessing the database via an application. They are normally used by the

DBA to limited extent, database designer or an application developer.

Every DDL commands implicitly issues a COMMIT command to the

database.

DML (Data Manipulation Language): - It is the area of SQL that

allows changing data within the database.

DCL (Data Control Language): - It is the component of SQL statement

that control access to data and to the database.

DQL (Data Query Language): - It is the component of SQL statement

that allows getting data from the database and imposing ordering upon it.

It includes the SELECT statement. When a SELECT is fired against a

table or tables the result is compiled into further temporary table, which

is displayed or perhaps received by a program i.e. a front-end.

Examples of DDL, DML and DCL commands

DDL: Data Definition Language statements

Examples:

CREATE To create object in the data base

ALTER Alters the structure of the data base

DROP Delete object from the database.

TRUNCATE Remove all the records from the table, including all spaces

allocated for the records are removed.

COMMENT Add comments to the data dictionary.

GRANT Gives users access privileges to the database

REVOKE Withdraw access privilege gives with GRANT command.

DML: Data manipulation language statements

Examples:

INSERT Data into a table

UPDATE Updates existing data within a table

DELETE Deletes all the records from a table, the space for the records

remain

CALL Call a PL\SQL or Java subprogram

EXPLAIN PLAN Explain access path to data

LOCK TABLE Control concurrency

DCL: Data Control Language statements

Examples:

COMMIT Save work done

SAVEPOINT Identify a point in a transaction to which you can later roll

back

ROLLBACK Restore database to original since the last COMMIT

SET TRANSACTION Change transaction options like what rollback

segment to use

GRANT/REVOKE Grant or take back permission to or from the oracle

users

DQL: Data Query Language statements

Examples:

SELECT Retrieve data from the a database

Description of commands

The CREATE TABLE command

The CREATE TABLE command defines each column of the table uniquely.

Each column has a minimum of three attributes, a name, datatype and size.

Each table column definition is a single clause in the create table syntax. A

comma separates each table column definition from other. Finally, the SQL

statement is terminated with semicolon.

Syntax:

CREATE TABLE<Table Name>

(<Column Name1><data type>(<size>),<column name2><data type

(<size>));

Example:

CREATE TABLE branch_mstr ( branch_no varchar2(10), name

varchar2(25));

Output:

Table created

Inserting data into tables:

Once a table is created, then load this table with data to be manipulated later.

When inserting a single row of data into the table, the insert operation:

Creates a new row in the database table

Loads the values passed into the columns specified.

Syntax:

INSERT INTO <table name>(<columnname1>,<columnname2>)

VALUES(<expression>,<expression2>);

Example:

Insert into branch_mstr ( branch_no, name) values ( ‘B1’,’VileParle’);

Output:

1 row created

Viewing data in the tables:

The SELECT SQL verb is used to retrieve rows selected from one or more

tables

all rows and all columns.

Syntax:

SELECT <column name> TO<columnname1> FROM Table name;

Syntax: SELECT *from <Table name>;

Example:

Select emp_no, fname, mname, lname from emp_mstr;

Output:

Emp_no fname mname lname

E1 Ivan Nelson Bayross

E2 Amit Desai

E3 Maya Mahima Joshi

ELIMINATING DUPLICATION ROWS WHEN USING A SELECT

STATEMENT

A table could hold duplicate rows. In such a case to view only unique rows

the DISTINCT clause can only be used with select statements.

Syntax:

SELECT DISTINCT <COLUMNNAME1>, <COLUMNNAME2> FROM

<Table name>;

Example:

Select DISTINCT Occup from cust_mstr;

Output:

Occup

Business

Community Welfare

Executive

Information Technology

4 rows selected

The select distinct * SQL syntax scans through entire rows, and eliminates

rows that have exactly the same contents in each column.

Syntax:

SELECT DISTINCT * FROM <Tablename>;

Example:

SELECT DISTINCT * FROM branch_mstr;

Output:

Branch_no Name

B1 Vile Parle (HO )

B2 Andheri

B3 Churchgate

B4 Sion

4 rows selected

Sorting data in a table:

Oracle allows data from a table to be viewed in a sorted order. The rows

retrieved from a table will be sorted in either ascending or descending order

depending on the condition specified in the SELECT sentence.

Syntax:

SELECT * FROM <Tablename>

ORDER BY <columnname1>, <columnname2><[sort order]>;

Example:

SELECT * FROM branch_mstr ORDER BY Name;

Output:

Branch_no Name

B2 Andheri

B3 Churchgate

B4 Sion

B1 Vile Parle (HO)

4 rows selected

Creating a table from a table

Syntax:

CREATE TABLE <TABLENAME>(<COLUMN

NAME>,<COLUMNNAME>)

AS SELECT <COLUMNNAME>, <COLUMNNAME> FROM

<TABLENAME>

Example:

Create table acct_dtls ( acct_no,branch_no, balance)

As select from acct_no,branch_no,curbal from acct_mstr;

Output:

Table created

Delete operations:

The DELETE command deletes row from the table that satisfies the

condition provided by its where clause and returns the number of records

deleted.

Syntax:

DELETE FROM <tablename>;

Example:

DELETE from acc_dtls;

Output:

16 rows deleted.

Updating the contents of a table:

The UPDATE command is used to change data values in a table.

The UPDATE statement updates the column in the existing table’s rows

with new values.

The SET clause indicates which column data should be modified and the

new values that they should hold.The WHERE clause ,if given specifies

which rows should be updated.

Syntax:

UPDATE <tablename>

SET <column name1>=<expression1>, <columnname2>=<expression>;

Example:

UPDATE addr_dtls SET city =’bombay’;

Output:

44 rows updated.

Modifying the structure of tables:

Using ALTER TABLE command modifies the structure of a table. ALTER

TABLE allows changing the structure of an existing table. With ALTER

TABLE command it is possible to add or delete columns, create or destroy

indexes, change the data type of existing column or rename columns or the

table itself.

ALTER TABLE works by making a temporary copy of original table. The

alteration is performed on the copy then the original table is deleted and the

new one is renamed. While ALTER TABLE is executing the original table is

still readable by users of oracle.

Syntax:

ALTER TABLE <tablename>

ADD (<new column name><datatype>(<size>),

<new column name><datatype>(<size>)….);

Example:

ALTER TABLE BRANCH_MSTR ADD (CITY VARCHAR(25));

Output:

Table altered.

Renaming table:

Oracle allows renaming of tables.

To rename a table the syntax is

Syntax:

RENAME <tablename> to <new table name>

Example:

RENAME BRANCH_MSTR TO BRANCHES;

Output:

Table renamed.

Destroying tables:

Syntax:

DROP TABLE <Table name>;

Example:

DROP TABLE branch-mstr;

Output:

Table dropped

Displaying table structure:

Syntax:

DESCRIBE < Tablename>;

Example:

DESCRIBE branch_mstr;

Output:

Name Null? Type

Branch_no varchar2(10)

Name varchar2( 25)

Constraints

Constraints allow you to define certain validations and restrictions that have to be

followed while entering into the tables.

Oracle allows programmers to define constraints at:

Column Level

Table level

Column level constraints:

If data constraints are defined as an attribute of a column definition when

creating or altering a table structure, they are column level constraints.

Table level constraints:

If data constraints are defined after deleting all table level constraints

when creating or altering a table structure, it is a table level

constraint.

Primary key constraint:

A Primary key is a one or more column in a table used to uniquely identify each

row in a table. A table can have only one primary key. A primary key column in a

table has special attributes:

It defines the column (i.e. the column cannot be left blank). As the NOT NULL

attribute is active.

The data held across the column must be UNIQUE.

A single column primary key is called a Simple key. A multicolumn primary key is called a Composite primary key.

Primary key Constraint Defined At Column Level

Syntax:

<Column name> <data type>(<size>) primary key

Example:

CREATE TABLE cust_mstr(

cust_no varchar2(10) primary key, fname varchar2(25), mname varchar2(25),

lname varchar2(25),DOB_inc date, occup varchar2(25));

Primary key Constraint Defined At Table Level

Syntax:

Primary key(<column name>, <column name>)

Example:

Create table banksys( serno varchar2(10), sfno varchar2(10),

branchno varchar2(10), acno varchar2(10), primary key(serno, branchno);

Foreign key constraint:

Foreign keys represent relationships between tables. A foreign key is a

column whose values are derived from the primary key or unique key of

some other table.

The table in which the foreign key is defined is called a foreign table or

detail table. The table that defines the primary key or unique key and is

referenced by the foreign key is called the primary table or master table. The

master table can be referenced in the foreign key definition by using the

clause REFERENCES Table name. This relationship ensures:

Records cannot be inserted into a detail table if corresponding records in

the master table do not exist.

Records of the master table cannot be deleted if corresponding records in

the detail table actually exist.

A foreign key must have a corresponding primary key or unique key

value in the master table.

Foreign key Constraint Defined At The Column Level

Syntax:

<column name> <data type>(<size>)

references <table name> [(<column name>)]

Example:

Create table emp_sys( empno number(5) primary key, branchno

number(3) references banksys , fname varchar2(10));

Foreign key Constraint Defined At the Table Level

Syntax:

Foreign key(<column name>[,<column name>])

References <table name> [(<column name>, <column name>)]

Example:

Create table emp_sys(accno varchar2(10), custno varchar2(10),

Foreign key(custno) references banksys(branchno);

Unique key constraint:

The unique column constraint permits multiple entries of NULL into the

column. This is the essential difference between the primary key and the

unique constraints when applied to the table columns.

A table can have more than one unique key, which is not possible in primary

key.

Unique key Constraint Defined At The Column Level

Syntax:

<column name> <data type>(<size>) unique

Example:

Create table cust(custno number(5) unique, fname varchar2(10), mname

varchar2(10));

Unique key Constraint Defined At The Table Level

Syntax:

Create table table name(<column name1> <datatype>(<size>),

<column name2> <data type>(<size>) unique(<column name1>, <column

name2>));

Subqueries

A subquery is a form of a SQL statement that appears inside another SQL

statement. It is also termed as nested query. The statement containing a

subquery is called a parent statement. The parent statement uses the rows

returned by the subquery. When we give equality condition or > or <

condition the inner query should give only one value.

It is used for the following:

To insert record in a target table.

To create table and insert record in the table created.

To update table in a target table.

To create views.

To provide values for conditions in WHERE, HAVING, IN etc. used

with SELECT, UPDATE, and DELETE statements.

Example:

Retrieve the address of a customer named ‘Ivan Bayross’.

Synopsis

Tables Cust_mstr, addr_dtls

Colum

ns

Cust_mstr:cust_no, fname, lname

Addr_dtls:code_no, addr1, addr2, city, state,

pincode

Techni

que

Subqueries, operators: IN, Clauses: WHERE,

Other: Concat(||)

Solution

SELECT code_no “Cust.no”, addr1 || ‘’ || addr2 || ‘’|| city ‘,’|| state || ‘,’ ||

pincode “address” FROM Addr_dtls WHERE code_no IN( SELECT

cust_no FROM cust_mstr

WHERE fname= ‘Ivan’ AND lname= ‘Bayross’);

Output:

Cust.no address

C1 F-12, Diamond Palace, West Avenue, North Avenue,

Santacruz(West},

Mambas, Maharashtra, 40056

In the above solution the subquery is as follows:

SELECT cust_no FROM cust_mstr

WHERE fname= ‘Ivan’ AND lname= ‘Bayross’;

The target table will be as follows:

Output

Cust.no

C1

The outer subquery output will simplify the solution as shown below:

SELECT code_no “Cust.no”, addr1 || ‘’ || addr2 || ‘’|| city ‘,’|| state || ‘,’ ||

pincode “address” FROM Addr_dtls WHERE code_no IN (C1);

Joins

Join returns a relation containing all possible tuples that are the combination

of two tuples, one from each of the two specified relations such that the two

tuples contributing to any given combination have a common value for the

common attributes of the two relations.It means combining of two or more

tables to form a single table. It must require ‘where’ condition.

Syntax:

SELECT <Column name1>, <Column name2>, <column nameN> FROM

<Table name1>, <Table name2>

WHERE <Table name1>.<Column name1> = <Table name2>.<Column

name2>

AND <Condition>

ORDER BY <Column name1>, <Column name2>, <Column nameN>

Example

List the employee details along with branch names to which they belong.

Synopsis

Tables: EMP_MSTR, BRANCH_MSTR

Columns: EMP_MSTR: EMP_NO, FNAME, MNAME, LNAME, DEPT,

DESIG, BRANCH_NO

BRANCH_MSTR: NAME, BRANCH_NO

Technique

:

Clauses: WHERE , Others: Concat (||)

SELECT E.EMP_NO, (E.FNAME || ’ ‘ || E.MNAME || ‘ ‘ || E.LNAME)

“Name”, B.NAME “Branch”, E.DEPT, E.DESIG

FROM EMP_MSTR E, BRANCH_MSTR B WHERE B.BRANCH_NO =

E.BRANCH_NO;

Using the union, intersect and minus clause

Union Clause

The Union clause merges the output of two or more queries into a single set

of rows and columns.

While working with the Union clause the following points should be

considered:

The number of columns and the data types of the columns being selected

must be identical in all the SELECT statement used in the query. The

names of the column need not to be identical.

UNION operates over all of the columns being selected.

NULL values are not ignored during duplicate checking.

The IN operator has higher precedence than UNION operator.

By default, the output is stored in ascending order of the first column of

the SELECT statement.

RecordsOnly in Query One

Records only in

Query Two

RecordsFrom bothQueries

Example:

Retrieve the names of all the customers and employees residing in the city

of Mumbai.

Synopsis:

Tables: CUST_NO, FNAME, LNAME

Columns: CUST_MSTR, EMP_MSTR, ADDR_DTLS

Technique

s:

Operators: LIKE, Clauses: WHERE, UNION,

Others: Alias

Solution

SELECT CUST_NO “ID”, FNAME|| ‘ ‘ || LNAME

“Customers/Employees”

FROM CUST_MSTR, ADDR_DTLS

WHERE CUST_MSTR.CUST_NO = ADDR_DTLS.CODE_NO

AND ADDR_DTLS.CITY = ‘Mumbai’ AND ADDR_DTLS.CODE_NO

LIKE’C%’

UNION

SELECT EMP_NO “ID”, FNAME || ‘ ‘ || LNAME “Customers/Employees”

FROM EMP_MSTR, ADDR_DTLS

WHERE EMP_MSTR.EMP_NO = ADDR_DTLS.CODE_NO

AND ADDR_DTLS.CITY = ‘Mumbai’ AND ADDR_DTLS.CODE_NO

LIKE’E%’;

Intersect Clause

The Intersect clause outputs only rows produced by both the queries

intersected i.e. the output in an Intersect clause will include only those

queries that are common to both the queries.

While working with the Intersect clause the following points should be kept

in considered:

The number of columns and the data types of the columns being selected

by the SELECT statement in the queries must be identical in all the

SELECT statements used in the query. The names of the columns need

not to be identical.

Reversing the order of the intersected tables does not alter the result.

INTERSECT does not ignore NULL values

Example:

Retrieve the customers holding accounts as well as fixed deposits in the

bank.

Synopsis:

Tables ACCT_FD_CUST_DTLS

Columns CUST_NO

Techniques Operators: LIKE, Clauses: WHERE,

INTERSECT

Solution:

SELECT DISTINCT CUST_NO FROM ACCT_FD_CUST_DTLS

WHERE ACCT_NO

LIKE ‘CA%’ OR ACCT_FD_NO LIKE ‘SB%’

INTERSECT

SELECT DISTINCT CUST_NO FROM ACCT_FD_CUST_DTLS

WHERE ACCT_FD_NO LIKE ‘FS%’;

CommonRecordsFrom bothQueries

Minus Clause

The Minus clause outputs the rows produced by the first query, after filtering

the rows retrieved by the second query.

While working with the Minus clause the following points should be

considered:

The number of columns and the data types of the columns being selected

by the SELECT statement in the queries must be identical in all the

SELECT statements used in the query. The names of the columns need

not be identical.

All on the columns in the WHERE clause must be in the SELECT clause

for the MINUS operator to work.

Example:

Retrieve the customers holding accounts but not holding any fixed deposits

in the bank.

Synopsis:

Tables ACCT_FD_CUST_DTLS

Columns CUST_NO

Techniques Operators: LIKE, Clauses: WHERE,

MINUS

RecordsOnly in Query One

Solution:

SELECT DISTINCT CUST_NO FROM ACCT_FD_CUST_DTLS

WHERE ACCT_NO LIKE ‘CA%’ OR ACCT_FD_NO LIKE ‘SB%’

MINUS

SELECT DISTINCT CUST_NO FROM ACCT_FD_CUST_DTLS

WHERE ACCT_FD_NO LIKE ‘FS%’;

Views

Oracle allows the creation of an object called a view to reduce the redundant

data. A view is mapped, to SELECT sentence. The table on which the view

is based is described in the FROM clause of the SELECT statement.

When a reference is made to a view, its definition is scanned, the base table

is opened and a view is created on top of the base table. Hence a view holds

no data at all until a specific call to the view is made. This reduces the

redundant data. When a view is used to manipulate table data, the underlying

base table will be completely invisible. This will give the level of data

security required.

The reasons why views are created:

When data security is required.

When data redundancy is to be kept to the minimum while maintaining

data security.

Creation of views:

Syntax:

CREATE VIEW <view name> AS

SELECT <column name1>, <column name2>FROM <table name>

WHERE <column name> = <expression list>;

GROUP BY <grouping criteria> HAVING <predicate>

Example:

CREATE VIEW vw_customers AS SELECT * FROM cust_mstr;

Renaming the Columns of A View

Example:

CREATE VIEW vw_transactions AS

SELECT acct_no “Account No.”, type, dr_cr “Mode”, amt “Amount”

FROM trans_mstr;

Selecting a Data from a View

Syntax

SELECT <column name1>, <column name2> FROM <view name>;

Example

SELECT FNAME, LNAME, DEPT FROM vw_Employees

WHERE DEPT IN (‘Marketing’, ‘Loans And Financing’);

Updateable views:

Views can also be used for data manipulation. Views on which data

manipulation can be done are called updateable views. When you give an

updateable view name in the update, insert or delete SQL statement,

modification to data will be passed to the underlying table.

For a view to be updateable it should meet the following criteria:

If the user wants to insert record with the help of a view the primary key

column and the entire not null column must be included in the view.

The user can UPDATE, DELETE records with the help of a view even if

the PRIMARY KEY column and NOT NULL column are excluded from

the view definition.

Syntax for creating and updateable view:

CREATE VIEW ww_client AS

SELECT client_no, name, addres1, bal_due

FROM client_master;

A View can be created from more than one table. For the purpose of

creating the view these tables will be linked by a join condition specified

in the where the clause of the view definition.

Views defined from multiple tables:

If a view is created from multiple tables, which were created without

using a ‘referencing clause’ then though the PRIMARY KEY column as

well as the NOT NULL columns are included in the view definition the

view behavior will be as follows:

The INSERT, UPDATE or DELETE operation is not allowed.

If a view is created from multiple tables, which were created using a

‘referencing clause’ then though the PRIMARY KEY column as well as

the NOT NULL columns are included in the view definition the view

behavior will be as follows:

An INSERT operation is not allowed.

The DELETE or MODIFY operations do no effect the master table

The view can be used to MODIFY the columns of the detail table

included in the view.

If a DELETE operation is executed on the view the corresponding

records form the delete table will be deleting.

Example:

Table Name: BRANCH_MSTR

Column

Name

Data

Type

Si

z

e

Attributes

BRANCH

_NO

VarC

har2

1

0

Primary Key/ First letter

must be ‘B’

NAME VarC

har2

2

5

Table Name: ADDR_DTLS

Column

Name

Data-

Type

S

i

z

e

Attributes

ADDR_N Number 6 Primary Key

O

CODE_NO VarChar

2

1

0

Foreign Key references

BRANCH_NO of the

BRANCH_MSTR table.

ADDR_TY

PE

VarChar

2

1 Can hold the values: H for Head

Office or B for Branch

ADDR1 VarChar

2

5

0

ADDR2 VarChar

2

5

0

CITY VarChar

2

2

5

STATE VarChar

2

2

5

PINCODE VarChar

2

6

Syntax for creating a Master/Detail View

CREATE VIEW vw_Branch AS

SELECT FROM BRANCH_NO, NAME, ADDR_TYPE, ADDR1,

ADDR2, CITY, STATE, PINCODE FROM BRANCH_MSTR,

ADDR_DTS

WHERE ADDR_DTLS.CODE_NO= BRANCH_MSTR.BRANCH_NO;

Destroying a view

The DROP VIEW command is used to remove a view from the database.

Syntax:

DROP VIEW viewname;

Example:

Remove the view ww_clientadmin from the database.

DROP VIEW ww_clientad

Index

An Index is an ordered list of the contents of the column or group of

columns of the table. Indexing involves forming a two dimensional matrix

completely independent of the table on which the index is being created.

This two-dimensional matrix will have a single column, which will hold

sorted data, extracted from the table columns on which the index is created.

Another column called the address field identifies the location of the record

in the Oracle database.

Data retrieval from the table using an index is much faster than data retrieval

from the table where indexes are not defined.

Duplicate/Unique Index

Oracle allows the creation of two types of indexes. These are:

Indexes that allow duplicate values for the indexed columns i.e. Duplicate Index

Indexes that deny duplicate values for the indexed columns i.e. Unique Index

Creation of an Index

An index can be created on one or more columns. Based on the number of

columns included in the index, an index can be:

Simple Index

Complex Index

Creating Simple Index

An index created on a single column of a table is called a Simple Index. The

syntax for simple index that allows duplicate values is as described.

Syntax:

CREATE INDEX <Index Name> ON <Table Name> (<Column Name>);

Example:

Create a simple index on VERI_EMP_NO column of the ACCT_MSTR

table.

Solution:

CREATE INDEX idxVeriEmpNo ON ACCT_MSTR (VERI_EMP_NO);

Output:

Index created.

Creating Composite Index

An index created on more than one column is called a Composite Index. The

syntax for the creating a composite index that allows duplicate values is:

Syntax:

CREATE INDEX <Index Name>

ON <Table Name> (<ColumnName1>, <ColumnName2>);

Example:

Create a composite index on the TRANS_MSTR table on columns

TRANS_NO and ACCT_NO

Solution:

CREATE INDEX idxTransAcctNo ON TRANS_MSTR (TRANS_NO,

ACCT_NO);

Output:

Index created

Creation of Unique Index

A unique index can also be created on one or more columns. If an index is

created on a single column, it is called a Simple Unique Index. The syntax

for creating a simple unique index is as follows:

Syntax:

CREATE UNIQUE INDEX <Index Name> ON <Table Name> (<Column

Name>);

If an index is created on more than one column, it is called a Composite

Unique Index. The syntax for creating a composite unique index is as

follows:

Syntax:

CREATE UNIQUE INDEX <Index Name>

ON <Table Name> (<Column Name>, <Column Name>);

Example:

Create a unique index on CUST_NO column of the CUST_MSTR table.

Solution:

CREATE UNIQUE INDEX idx_CustNo ON CUST_MSTR (CUST_NO);

Output:

Index created

Dropping Indexes

Indexes associated with the tables can be removed by using the DROP

INDEX command.

Syntax:

DROP INDEX idx_CustNo;

Sequences

Oracle provides an object called sequence that can generate numeric value.

The value generated can have a maximum of 38 digits. A sequence can be

defined to

Generate numbers in ascending or descending

Provide intervals between number.

Caching of sequence number in memory etc.

A sequence is an independent object and can be used with any table that

requires its output.

Creating Sequences:

The minimum information required for generating numbers using a sequence

is:

The starting number

The maximum number that can be generated by a sequence

The increment value for generating the next number

This information is provided to Oracle at the time of the sequence

creation. The SQL statement used for creating a sequence is:

Syntax:

CREATING SEQUENCE sequence_name

Example:

CREATE SEQUENCE order_seq

INCREMENT BY1

STAER WITH1

MINVALUE1

MAXVALUE9999

CYCLE;

Alter a sequence

A sequence once created can be altered. This is achieved by using

ALTER SEQUENCE statement.

Syntax:

ALTER SEQUENCE sequence_name

Example:

ALTER SEQUENCE order_seq

INCREMENT BY 2

CACHE 30;

Dropping a sequence

The DROP SEQUENCE command is used to remove the sequence from

the data base.

Syntax:

DROP SEQUENCE sequence_name;

Example

DROP SEQUENCE order_seq;

INTRODUCTION TO VISUAL BASIC

VISUAL BASIC:

Visual Basic was developed from the BASIC programming language. In 1970 Microsoft started developing ROM based interpreted BASIC for the early microprocessor based computers.

Visual Basic is that ideal programming language for developing sophisticated professional application for Microsoft Windows. It makes use of graphical user interface for creating robust and powerful applications.

Coding in GUI is different from traditional, Linear programming methods where the user is guided through a linear path of execution and is limited to small set of operations. In GUI environment, the number of operations open to user is much greater, allowing more freedom to user and developer.

Reasons for using Visual Basic as front end tool:

Its features such as easier comprehension, user friendly, faster

application development etc.

Simple

Powerful

Windows native development

Rapid application development

Provision for wizards

There is no restriction on the no. of controls that can be placed on the

form.

A programmer need not write code to display the required component.

The component can be moved, resized and even deleted, if so

required.

Some Visual Interface Components:

Window:

A window sometimes also called a form, is the most important of all the visual interface components. The window is the base for a user interface of application. All the components that make a user interface are placed in the window and can’t exist independent of the window.

When the application starts it opens a window. Once the window is loaded into the memory, all the components contained in it come into existence and can be accessed.

The Form:

The window called a form in visual basic. The form is a main part of the user interface. It is the base on which the user interface is built.

Form property:- Some of the important properties are listed below:-

Caption:- The text that appears on the title bar of the form.

Name:- Text used in the code to identify the form. By default the form

name is form1.

Form methods:-

Load:- The statement initialize and load the form into the memory

without displaying it on the screen.

Load form1

Unload:- Unload removes a form from memory.

Unload me

Hide:- The hide method removes a form from the screen.

Form1.hide

Show:- The show method loads a form into the memory if not loaded

already and display it on the screen.

Form events:-

Form events are generally triggered in the following order:-

Initialize:- The initialize event is triggered before the form is loaded or

displayed it. It is generally used to prepare an application for use.

Load:- The load event perform action that are required before a form is

displayed. It occurs each time that a form is loaded into the memory.

Activate/Deactivate:- An activate event is triggered when a form receives

focus from another form in the same project and the deactivate event is

fired after the lost focus event that is, after a form loses focus to another

form.

Query Unload:- The query unload event determines how a form was

closed.

Terminate:- Terminate event is triggered when all instances of a form are

removed from memory. It occurs after the unload event.

Set form1= nothing

Buttons:

The button, also reffered to as command button or push button, is one of the

most popular user interface components.

Button Properties:

Caption:- the text that appears on the command button.

Picture:- Sets the graphic to be displayed on the image button.

Enabled:- sets a value that determines whether a form or control can

respond to the user generated events.

The button methods:-

Move

Button

The button events:-

Click

List Box:-

List box are used to present the user with the possible list options. The user can select one or more of the listed options by default the choice is displayed vertically in a single column, although we can set up multiple columns as well.

List Box Properties:

List Index:- The list index started with 0 for the first item in the list box.

Sorted:- It is used to display the items in the sorted order.

List Box

Multiselect:- It is used to specify if the user can select multiple items in

the list.

List count:- It is used to return the no. of items in a list box.

List box methods:-

Add item:- This method adds the specified items to the list box.

Remove item:- It is used to delete the items from the list.

Setfocus:- It is used to make the list box the current active element.

List box event:-

Click:- The user click on the list box.

Scroll:- the user scroll through the list in the list box.

Combo box or drop down List box:-

A Combo box control combines the feature of a text box and a list box. This control allows the user to select an item either by typing text into the combo box or by selecting it from the list.

Combo box properties:-

Style:- There are two combo box styles.

Simple combo box:- Specifically a simple Combo box in which the list

is displayed at all times.

Drop down combo box:- The user can either enter text directly or click

the combo box list.

Locked:- It is used to specify whether the user can enter a value in the

text box section of the control.

Combo Box

Combo box methods:-

Click:- The user click on the Combo box.

Scroll:- The user scroll through the list in the combo box.

Change:- It occurs when the user changes the text in the textbox portion

of the combo box.

Drop down:-It occurs when the user clicks on the arrow to display the

list.

The radio button or option button:

The radio button also referred to as option buttons, are used when the use can select one and only one of the multiple options.

The option button properties:-

Caption:- The text that appears next to the option button.

Value:- This property specify whether the option button is selected.

Enabled:- sets a value that determines whether a form or control can

respond to the user generated events.

The option button methods:-

Move:- Moves a control on the form.

The option button events:-

Click:- Occurs when the user clicks on the option button.

The frame Control:-

The frame control provides an identifiable grouping for controls.

Radio Button

The frame properties:-

Caption:- The text that appears on the screen.

The frame methods:-

Move:- Moves the frame control along with the controls placed within it.

Drag:- Begins, ends or cancels a drag operation of the control.

The frame events:-

Click:- Occurs when the user clicks on the frame control.

The check box:-

A checkbox indicates whether a particular condition is on or off. Checkbox are used in an application to give users true/false or yes/no options. Check

Frame

box controls are used to display multiple choices from which the user can select one or more.

The Check box properties:-

Caption:- The text that appears next to the check box.

Value:- This property specifies whether the check box is selected.

The Check box methods:-

Move

Drag

The Check box events:-

Click:- occurs when the user ticks on the check box.

The Scroll bars:-

Check Box

Scroll bars provides easy navigation through a long list of items or a large amount of information. Scroll bars are actually graphical representation of numeric values. The values can be range from-32768 to 32767 and the minimum value to 0.

Visual basic allows us to create two types of scroll bars:-

Horizontal and vertical:- Horizontal scroll bars scroll from left to right and vertical scroll bars scroll up and down.

The scroll bar properties:-

Min:- An integer which defines the smallest value for a scroll bar. The

possible values are from –32768 to 32767.

Max:- An integer which defines the largest value for a scroll bar.

Value:- A numeric value determined by the current position of the scroll

box within the scroll bar. The value depends on the value of Max and

Min properties.

The Scroll bar methods:-

Scroll Bar

Move

The Scroll bar events:-

Scroll:- Occurs when the scroll box on a scroll bar control is repositioned

or scrolled horizontally or vertically.

Change:- Occurs when the user scrolls through the scroll bar.

The text box:-

A text box also called an edit field or edit control. It is used to display text or to accept user input.

The text box properties:-

Text Box

Text:- Text entered into the text box control.

Multiline:- Sets a value indicating whether a text box control can accept

and display multiple lines of text.

Maxlength:- Specifies the maximum number of characters that can be

entered.

Passwordchar:- This property hides or masks the characters entered into

the text box.

Locked:- The locked property determines whether a user can edit a text if

this property is set to true the text box is read only that is the user cannot

edit text.

The text box methods:-

The below methods are working same as list box:

Move

Drag’

Setfocus

The text box events:-

Key press:- Occurs when the user presses and releases the key.

Click:- Occurs when the user clicks in the text box.

Change:- Occurs when the text in the textbox changes.

Lostfocus:- Occurs when a control loses the focus

The button:-

A button is a control which allows the user to click on it to perform an action. Visual basic allows us to create two types of buttons:

Command button and Image buttons

Command buttons are also called push button and allows the usr to click them to perform the required action. Image buttons respectively as displayed on the visual basic toolbox.

The button properties:-

Caption:- the text that appears on the command button.

Picture:- Sets the graphic to be displayed on the image button.

Enabled:- sets a value that determines whether a form or control can

respond to the user generated events.

The button methods:-

Image Button

Move

The button events:-

Click

The label:-

The label is the control used to place text in a form.

The label properties:-

Caption:- This property holds the text to be displayed by the control.

Autosize:- Sets a value that determines whether a control automatically

resized to display its entire contents.

Word-wrap:- When set false, this property will ensure that the text of the

label is displayed as a single line

The label methods:-

Label

Move:- used to move the label.

The label events:-

Click:- The user click with a mouse on the label.

Menus:-

Application can perform a number of tasks. For effective use of the application it must:

Inform the users of the tasks that are available.

Give the users the means to specify the task that is to be performed.

The application does this via a “Menu”.

Menus provide a convenient and consistent way to group commands and an easy way for users to access them. It allows an application to present the user with a means of initiating different actions. A menu bar appears below the title bar.

Designing a Menu:-

In visual basic we use the menu editor to design a menu. While most menu control properties can be set using the menu editor, all menu properties are available in the properties window. The property important to menu controls are:-

Menu Bar

Caption:- This is the text that appears on the control.

Name:- This the name we use to reference the menu control from code.

Shortcut:- Shortcut keys run the menu item immediately when pressed.

Checked:- Sets a value that determined whether a check mark is

displayed next to the menu item.

Enabled:- Sets a value that determined whether a menu item can respond

to events.

Pop-up Menus:-

A pop up menu is a menu displayed at the cursor location when the user clicks the right mouse button. The code of the pop up menu is added in the mouse down event of a form.

Tool Bar:-

The tool bar is situated, typically, immediately below the menu bar. The buttons in a tool bar are analogous to the items in a menu. Tool bars also be made to display tool tip.

Tool bar properties:

Style:- It determines the appearance of the control.

Text alignment:- It determines the position of text relative to the button.

Tool Tip Text:- Sets a tool tip.

Tool Bar

The tool bar events:-

Button click:- When the user clicks on a button object in a tool bar control.

The tool bar methods:

Move

Drag

Status bar:-

A status bar is a window which is usually displayed at the bottom of a form. It inform the user of the status of an application.

Status Bar

Status bar properties:-

Panel:- refers to a collection of panel objective.

Style:- sets the style of the status bar control.

Tool tip text:- Sets the tool tip.

Status bar methods:-

Move

Drag

Status bar events:-

Panelclick & Paneldblclick

Dialog boxes:-

A dialog box is a temporary window that contains controls. We can use it.

To display information and messages for the user.

As a means of accepting user input for a specified action.

Features of a dialog box:-

A dialog box does not have a menu.

A dialog box cannot be resized.

Types of dialog box:-

Modal dialog box:- A modal dialog box will not let the user perform any

action while it is on display. For example Print dialog, File Open dialog

box.

Modeless:- Modeless dialog box let the user shift the attention between

the dialog box and another form without having to close the dialog box.

For example Find dialog box.

Common dialogs:- A common dialog box is a system defined dialog box

that standardizes how users perform complex operations that are common

to most applications. The color open, save as and print are some

examples of common dialog boxes.

Custom dialog boxes:- These are dialog boxes designed to meet the

requirements of the application and are designed by the programmer.

Data Controls:-

The data control of visual basic allows us to access and manipulate databases.

Using a data control:-

By setting the properties of the data control we watch the data control to a specific database and the table in it. Data control itself does not display data. It only conducts the flow of information back and forth between our application and database.

Controls that can work with the data control to access the data are said to be data aware or bound and the process of attaching a data aware to the data control is called binding. Some examples of bound controls are Picture, label, Text box, List box, Check box. The data control enables us to move from record to record and to display and manipulate the data from the records in bound controls.

Record set:- A record set represents the records in a table or the records that results from running a query.

Data control properties:-

Connect:- sets a value that provides information about the source of a

database.

Database name:- Sets the name and location of the source of data for a

data control.

Record source:- Sets the underlying table.

Recordset type:- Sets a value indicating the type of Recordset we want

the data control to create. It has the following three types of recodsets:

Dynaset-type:- A dynamic set of records can add, change or delete

records from a dynamic set type recordset and the changes will be

reflected in the underlying tables.

Table-type:- A set of records that represents a single database table

that can be used to add, change or delete records.

Snapshot-type:- A copy of a set of records that can be used to find

data or generate reports, but cannot be updated.

Data control events:-

Validity:- It occurs when a different record becomes the current record.

Data control methods:-

Move

Drag

Bound control properties:-

Datafield:- specifies the name of a datafield in the record set created by

the data control.

Data source:- specifies the name of the data control to which the control

is bound.

Active data object:-

ADO is an interface OLEDB. Introduction in version 6 of visual basic. This is Microsoft newest data access technology and provides access to almost any data stored in different formats. ADO is a replacement for the old DAO and RDO gives us features not found in either.

Objects of ADO:-

Connection:- The connection object creates a connection between an

application and the data source.

Recordset:- The recordset object is used to access records returned from

a query.

Command:- A command object is used to query a database and access

the records that are returned from the database.

How to create a connection:-

To create a connection objects we need to set the connection string and the connection timeout property.

We establish the physical connection to a data source using the open method of connection object.

Methods of recordset:-

Movefirst:- takes the user to first record of the recordset.

Movenext:- takes the user to next record of the recordset.

Movelast:- takes the user to last record of the recordset.

Moveprevious:- Moves the user to previous record of the recordset.

Single document interface:-

In a SDI application all the windows can be moved anywhere on the screen. SDI are used by application where the interface of the application can remain constant throughout in SDI the user allows to create or use one file at a time.

Drawbacks of SDI:-

It allows one file to be opened at a time.

If the user do work on multiple files he has to open multiple files.

Multiple document interface:-

MDI uses an outer frame window to hold inner windows. Inner windows present the interface. An application that provides varied functionality and requires a different interface each time.

Components of MDI interface:-

MDI frame:- the outer or the container window.

MDI child:- the inner window which represents the interface.

Characteristics of MDI components:-

When an application with a MDI is started, it is the MDI frame that gets

displayed.

It acts as a container for all other windows in the application.

The each inner window is referred to as a MDI child.

There can be multiple child windows opened at a time.

None of the MDI child window can be moved out of the MDI frame.

Code of Loin Form:-

Option Explicit

Public LoginSucceeded As Boolean

Private Sub cmdCancel_Click ()

LoginSucceeded = False

Me.Hide

Unload mdi_start

End

End Sub

Private Sub cmdOK_Click ()

'check for correct password

If (txtUserName = "shaan" And txtpassword = "bawa") Or (txtUserName = "sapna" And txtpassword = "sharma") Then

LoginSucceeded = True

Me.Hide

mdi_start.Enabled = True

mdi_start.Show

Else

MsgBox "Invalid Username/Password, try again!", , "Login"

txtUserName.SetFocus

txtUserName.Text = ""

txtpassword.Text = ""

End If

End Sub

Private Sub Form_Activate()

mdi_start.Enabled = False

End Sub

Private Sub Form_Load()

txtUserName.Text = ""

txtpassword.Text = ""

End Sub

Code of MDI Form:-

Private Declare Function OSWinHelp% Lib "User32" Alias "WinHelpA" (ByVal hWnd&, ByVal HelpFile$, ByVal wCommand%, dwData As Any)

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub MDIForm_Load()

Me.Show

Me.Enabled = False

'setting toolbar images

With Toolbar2

Set .ImageList = ImageList1

.Buttons(2).Image = 1

.Buttons(3).Image = 7

.Buttons(5).Image = 5

.Buttons(6).Image = 6

.Buttons(7).Image = 14

.Buttons(8).Image = 2

.Buttons(9).Image = 3

.Buttons(11).Image = 10

.Buttons(13).Image = 8

.Buttons(14).Image = 9

.Buttons(16).Image = 12

.Buttons(17).Image = 13

.Buttons(19).Image = 4

.Buttons(20).Image = 11

End With

sbStatusBar.Panels(3).Text = "Login"

End Sub

Private Sub MDIForm_Unload(Cancel As Integer)

On Error Resume Next

If MsgBox("Are You Sure you want to Quit ?", vbExclamation + vbOKCancel, "Library Management System") = vbOK Then

Unload frmLogin

Else

Cancel = True

End If

End Sub

Private Sub mnu_cascade_Click()

Me.Arrange vbCascade

End Sub

Private Sub mnu_hoizontal_Click()

Me.Arrange vbTileHorizontal

End Sub

Private Sub mnu_vertical_Click()

Me.Arrange vbTileVertical

End Sub

Private Sub sbStatusBar_PanelClick(ByVal Panel As MSComctlLib.Panel)

ShellExecute Me.hWnd, vbNullString, "http://geocities.com/priyank_modi/", vbNullString, vbNullString, SW_SHOWNORMAL

End Sub

Private Sub sm_about_Click()

Load frmAbout

frmAbout.Show

End Sub

Private Sub sm_bookrpt_Click()

Load Frm_bookrpt

Frm_bookrpt.Show

End Sub

Private Sub sm_books_Click()

Load Frm_books

Frm_books.Show

End Sub

Private Sub sm_calculator_Click()

On Error GoTo errHandle

Dim a As Double

a = Shell("C:\WINDOWS\System32\calc.exe", vbNormalFocus)

Exit Sub

errHandle:

MsgBox "Unable to run Calculator Utility on your computer", vbInformation, "Error in opening!!!"

Resume Next

End Sub

Private Sub sm_employees_Click()

Load Frm_Employees

Frm_Employees.Show

End Sub

Private Sub sm_exit_Click()

Unload Me

End Sub

Private Sub sm_fine_Click()

Load Frm_Fine

Frm_Fine.Show

End Sub

Private Sub sm_global_Click()

Load Frm_global

Frm_global.Show

End Sub

Private Sub sm_help_Click()

Dim nRet As Integer

If Len(App.HelpFile) = 0 Then

MsgBox "Unable to display Help Contents. There is no Help associated with this project.", vbInformation, Me.Caption

Else

On Error Resume Next

nRet = OSWinHelp(Me.hWnd, App.HelpFile, 3, 0)

If Err Then

MsgBox Err.Description

End If

End If

End Sub

Private Sub sm_hsearch_Click()

Dim nRet As Integer

'if there is no helpfile for this project display a message to the user

'you can set the HelpFile for your application in the

'Project Properties dialog

If Len(App.HelpFile) = 0 Then

MsgBox "Unable to display Help Contents. There is no Help associated with this project.", vbInformation, Me.Caption

Else

On Error Resume Next

nRet = OSWinHelp(Me.hWnd, App.HelpFile, 261, 0)

If Err Then

MsgBox Err.Description

End If

End If

End Sub

Private Sub sm_issret_Click()

Load Frm_issretrpt

Frm_issretrpt.Show

End Sub

Private Sub sm_issue_Click()

Load Frm_issue

Frm_issue.Show

End Sub

Private Sub sm_logoff_Click()

If MsgBox("Are You Sure you want to logoff ?", vbExclamation + vbOKCancel, "Library Management System") = vbOK Then

Call logoff

DoEvents

End If

End Sub

Private Sub sm_member_Click()

Load Frm_memrpt

Frm_memrpt.Show

End Sub

Private Sub sm_members_Click()

Load Frm_members

Frm_members.Show

End Sub

Private Sub sm_notepad_Click()

On Error GoTo errcode

Dim a As Double

a = Shell("C:\WINDOWS\System32\notepad.exe", vbNormalFocus)

Exit Sub

errcode:

MsgBox "Unable to run Notepad Utility on your computer", vbInformation, "Error in opening!!!"

Resume Next

End Sub

Private Sub sm_return_Click()

Load Frm_return

Frm_return.Show

End Sub

Private Sub sm_search_Click()

Load Frm_search

Frm_search.Show

End Sub

Private Sub smnu_keyboard_Click()

Load Frm_keyboard

Frm_keyboard.Show

End Sub

Private Sub Toolbar2_ButtonClick(ByVal button As MSComctlLib.button)

Select Case button.Index

Case 2: Call sm_books_Click

Case 3: Call sm_members_Click

Case 5: Call sm_issue_Click

Case 6: Call sm_return_Click

Case 7: Call sm_fine_Click

Case 8: Call sm_search_Click

Case 9: Call sm_global_Click

Case 11: 'add report

Case 13: Call sm_calculator_Click

Case 14: Call sm_notepad_Click

Case 16: Call smnu_keyboard_Click

Case 17: Call sm_about_Click

Case 19: Call sm_logoff_Click

Case 20: Call sm_exit_Click

End Select

End Sub

Private Sub Toolbar2_ButtonMenuClick(ByVal ButtonMenu As MSComctlLib.ButtonMenu)

Select Case ButtonMenu.Index

Case 1:

Call sm_bookrpt_Click

Case 2:

Call sm_member_Click

Case 3:

Call sm_issret_Click

End Select

End Sub

Code of Book Form:-

Dim Bookrecord As ADODB.Recordset

Dim Bookconnection As ADODB.Connection

Dim str As String

Dim slct As String

Dim saveflag As Boolean

'Function cheaking validity of textbox

Private Function cheak() As Boolean

'declaring variable

Dim status As Boolean

status = False

If txt_title.Text = "" Then

MsgBox ("Please enter the Title."), vbInformation, "Information required"

ElseIf txt_publication.Text = "" Then

MsgBox ("Please enter the Publications."), vbInformation, "Information required"

ElseIf txt_author1.Text = "" Then

MsgBox ("Please enter the First Authors name."), vbInformation, "Information required"

ElseIf txt_bookid.Text = "" Then

MsgBox ("Please enter bookid distinct from other"), vbInformation, "Information required"

ElseIf txt_pages.Text = "" Then

MsgBox ("Please enter no of pages of book."), vbInformation, "Information required"

ElseIf txt_price.Text = "" Then

MsgBox ("Please enter the price."), vbInformation, "Information required"

ElseIf txt_totalno.Text = "" Then

MsgBox ("Please enter no of copies."), vbInformation, "Information required"

ElseIf txt_issue.Text = "" Then

MsgBox ("Please enter no of copies issued."), vbInformation, "Information required"

ElseIf txt_avano.Text = "" Then

MsgBox ("Please enter no of copies available."), vbInformation, "Information required"

ElseIf txt_edition = "" Then

MsgBox ("Please enter the detail about edition of book."), vbInformation, "Information required"

ElseIf txt_subject.Text = "" Then

MsgBox ("Please enter subject related to the book."), vbInformation, "Information required"

ElseIf txt_isbn.Text = "" Then

MsgBox ("Please enter ISBN no. for book."), vbInformation, "Information required"

ElseIf IsNumeric(txt_author1.Text) Then

MsgBox ("Enter the valid author name."), vbInformation, "Invalid information"

ElseIf IsNumeric(txt_author2.Text) Then

MsgBox ("Enter the valid author name."), vbInformation, "Invalid information"

ElseIf IsNumeric(txt_author3.Text) Then

MsgBox ("Enter the valid author name."), vbInformation, "Invalid information"

ElseIf Not IsNumeric(txt_bookid.Text) Then

MsgBox ("Bookid must be numeric."), vbInformation, "Invalid information"

ElseIf Not IsNumeric(txt_pages.Text) Then

MsgBox ("Enter page as in form of string of digits."), vbInformation, "Invalid information"

ElseIf Not IsNumeric(txt_price.Text) Then

MsgBox ("Price must be digit form,enter valid price."), vbInformation, "Invalid information"

ElseIf IsNumeric(txt_edition.Text) Then

MsgBox ("Enter the valid string for edition."), vbInformation, "Invalid information"

ElseIf IsNumeric(txt_subject.Text) Then

MsgBox ("Subject name can not be Numeric."), vbInformation, "Invalid information"

ElseIf Not IsNumeric(txt_totalno.Text) Then

MsgBox ("Total no of copy must be Numeric."), vbInformation, "Invalid information"

ElseIf Not IsNumeric(txt_avano.Text) Then

MsgBox ("Available no of copy must be Numeric."), vbInformation, "Invalid information"

ElseIf Not IsNumeric(txt_issue.Text) Then

MsgBox ("Issue no of copy must be Numeric."), vbInformation, "Invalid information"

ElseIf Not (CDbl(txt_totalno.Text) = (CDbl(txt_avano.Text) + CDbl(txt_issue.Text))) Then

MsgBox ("Possibly incorrect data in copy info. frame."), vbInformation, "Invalid information"

Else

status = True

End If

cheak = status

End Function

'subroutin for setting text box mode

Private Sub setlock(val As Boolean)

txt_title.Locked = val

txt_publication.Locked = val

txt_author1.Locked = val

txt_author2.Locked = val

txt_author3.Locked = val

txt_price.Locked = val

txt_pages.Locked = val

txt_subject.Locked = val

txt_isbn.Locked = val

txt_totalno.Locked = val

txt_edition.Locked = val

txt_bookid.Locked = val

txt_issue.Locked = val

txt_avano.Locked = val

End Sub

'make blank the text box

Private Sub clear()

txt_title.Text = ""

txt_publication.Text = ""

txt_author1.Text = ""

txt_author2.Text = ""

txt_author3.Text = ""

txt_price.Text = ""

txt_subject.Text = ""

txt_isbn.Text = ""

txt_pages.Text = ""

txt_totalno.Text = ""

txt_avano.Text = ""

txt_issue.Text = ""

txt_edition.Text = ""

txt_bookid.Text = ""

'set focus to fiRSt textbox

txt_title.SetFocus

End Sub

Private Sub showdata()

If Bookrecord.EOF = False And Bookrecord.BOF = False Then

txt_author1.Text = Bookrecord.Fields(0)

txt_author2.Text = Bookrecord.Fields(1)

txt_author3.Text = Bookrecord.Fields(2)

txt_avano.Text = Bookrecord.Fields(3)

txt_bookid.Text = Bookrecord.Fields(4)

txt_edition.Text = Bookrecord.Fields(5)

txt_isbn.Text = Bookrecord.Fields(6)

txt_issue.Text = Bookrecord.Fields(7)

txt_pages.Text = Bookrecord.Fields(8)

txt_price.Text = Bookrecord.Fields(9)

txt_publication.Text = Bookrecord.Fields(10)

txt_subject.Text = Bookrecord.Fields(11)

txt_title.Text = Bookrecord.Fields(12)

txt_totalno.Text = Bookrecord.Fields(13)

End If

Call locate

End Sub

Private Sub setbutton(val As Boolean)

cmdFirst.Enabled = val

cmdPrevious.Enabled = val

cmdNext.Enabled = val

cmdLast.Enabled = val

cmd_delete.Enabled = val

cmd_edit.Enabled = val

cmd_new.Enabled = val

cmd_save.Enabled = Not val

cmdCancel.Enabled = Not val

End Sub

Private Sub cmd_close_Click()

Unload Me

End Sub

Private Sub cmdCancel_Click()

On erro GoTo cancelerr

'disablink control

setlock (True)

If Bookrecord.BOF And Bookrecord.EOF Then

GoTo newproc

Else

Bookrecord.MoveFirst

Call showdata

End If

newproc:

txt_title.SetFocus

Call setbutton(True)

Exit Sub

cancelerr:

MsgBox Err.Description

End Sub

Private Sub cmdFirst_Click()

On Error GoTo GoFirstError

Bookrecord.MoveFirst

'show thw current data record

Call showdata

Exit Sub

GoFirstError:

MsgBox Err.Description

End Sub

Private Sub cmdLast_Click()

On Error GoTo GoLastError

'lblStatus.Caption = " Move >>"

Bookrecord.MoveLast

'show thw current data record

Call showdata

Exit Sub

GoLastError:

MsgBox Err.Description

End Sub

Private Sub cmdNext_Click()

On Error GoTo GoNextError

'lblStatus.Caption = " Move >"

If Not Bookrecord.EOF Then Bookrecord.MoveNext

If Bookrecord.EOF And Bookrecord.RecordCount > 0 Then

Beep

'moved off the end so go back

Bookrecord.MoveLast

End If

'show thw current data record

Call showdata

Exit Sub

GoNextError:

MsgBox Err.Description

End Sub

Private Sub cmdPrevious_Click()

On Error GoTo GoPrevError

' lblStatus.Caption = " < Move"

If Not Bookrecord.BOF Then Bookrecord.MovePrevious

If Bookrecord.BOF And Bookrecord.RecordCount > 0 Then

Beep

'moved off the end so go back

Bookrecord.MovePrevious

End If

'show thw current data record

Call showdata

Exit Sub

GoPrevError:

If Err.Number = 3021 Then

MsgBox ("This is first Record."), vbInformation, "First record"

Bookrecord.MoveNext

ElseIf Err.Number <> 0 Then

MsgBox Err.Number & Err.Description

End If

End Sub

Private Sub Form_Load()

On Error GoTo errlable

If (view = 1) Then

Me.Top = 50

Me.Left = 50

ElseIf (view = 2) Then

Me.Top = 700

Me.Left = (Screen.Width - Me.Width) / 2

End If

Image1.Picture = mdi_start.ImageList1.ListImages(1).Picture

Set Bookconnection = New ADODB.Connection

Bookconnection.CursorLocation = adUseClient

Bookconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

slct = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book Order by Bookid"

Set Bookrecord = New ADODB.Recordset

Bookrecord.Open slct, Bookconnection, adOpenStatic, adLockOptimistic

'show current record

Call showdata

Set Datagrid.DataSource = Bookrecord

Datagrid.ReBind

'disable buttons

cmd_save.Enabled = False

cmdCancel.Enabled = False

Exit Sub

errlable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub locate()

lbl_total.Caption = Bookrecord.RecordCount

lbl_rec.Caption = Bookrecord.AbsolutePosition

End Sub

Private Sub cmd_delete_Click()

On erro GoTo lable

Beep

If MsgBox("Execution of command will delete current Datarecord,Are you sure you wan't to delete Datarecord ?", vbYesNo + vbExclamation, "Confirm Delete") = vbYes Then

str = "DELETE FROM Book WHERE "

str = str & "Bookid = "

str = str & CDbl(txt_bookid.Text)

Bookconnection.Execute str

Bookrecord.Requery

MsgBox "Record deleted sucessfully.", vbinformayion, "Delete"

If Bookrecord.BOF And Bookrecord.EOF Then

Call clear

MsgBox ("The previous record was last record,Now no record left."), vbInformation, "Last record"

cmd_delete.Enabled = False

Else

Bookrecord.MoveNext

If Bookrecord.EOF Then

Bookrecord.MoveLast

End If

Call showdata

End If

'message for status of mode

'lblStatus.Caption = "Record deleted."

End If

Exit Sub

lable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub cmd_edit_Click()

On Error GoTo lable

'Make all entries in input mode

Call setlock(False)

saveflag = False

'message for status of mode

' lblStatus.Caption = " Edit record"

Call setbutton(False)

' cmdcancel.Enabled = False

'set focus

txt_title.SetFocus

Exit Sub

lable:

MsgBox Err.Description

End Sub

Private Sub cmd_new_Click()

On Error GoTo lable

'Make all entries in input mode enable

Call setlock(False)

'clear the text field

Call clear

saveflag = True

'lblStatus.Caption = " Add new record."

Call setbutton(False)

Exit Sub

lable:

'Error handling statement

MsgBox Err.Description

End Sub

Private Sub cmd_save_Click()

On Error GoTo lable

'Make all entries in input mode enable

Call setlock(False)

'cheaking for validity condition

If cheak = True Then

If txt_author2.Text = "" Then

txt_author2.Text = "None"

End If

If txt_author3.Text = "" Then

txt_author3.Text = "None"

End If

'saving new record

If saveflag = True Then

str = "INSERT INTO Book"

str = str & "(Author1, Author2, Author3, Avano, Bookid, Edition, ISBNNumber, Issno, Pages, Price, Publication, Subject, Title, Totalno) "

str = str & "VALUES('" & Trim(txt_author1.Text) & "', "

str = str & "'" & Trim(txt_author2.Text) & "', "

str = str & "'" & Trim(txt_author3.Text) & "', "

str = str & CDbl(txt_avano.Text) & ", "

str = str & CDbl(txt_bookid.Text) & ", "

str = str & "'" & Trim(txt_edition.Text) & "', "

str = str & "'" & Trim(txt_isbn.Text) & "', "

str = str & CDbl(txt_issue.Text) & ", "

str = str & CDbl(txt_pages.Text) & ", "

str = str & CDbl(txt_price.Text) & ", "

str = str & "'" & Trim(txt_publication.Text) & "', "

str = str & "'" & Trim(txt_subject.Text) & "', "

str = str & "'" & Trim(txt_title.Text) & "', "

str = str & CDbl(txt_totalno.Text) & ")"

Bookconnection.Execute str

Else 'for editing the record

str = "UPDATE Book SET "

str = str & "Author1='" & Trim(txt_author1.Text) & "',"

str = str & "Author2='" & Trim(txt_author2.Text) & "',"

str = str & "Author3='" & Trim(txt_author3.Text) & "',"

str = str & "Avano=" & CDbl(txt_avano.Text) & ","

str = str & "Bookid=" & CDbl(txt_bookid.Text) & ","

str = str & "Edition='" & Trim(txt_edition.Text) & "',"

str = str & "ISBNNumber='" & Trim(txt_isbn.Text) & "',"

str = str & "Issno=" & CDbl(txt_issue.Text) & ","

str = str & "Pages=" & CDbl(txt_pages.Text) & ","

str = str & "Price=" & CDbl(txt_price.Text) & ","

str = str & "Publication='" & Trim(txt_publication.Text) & "',"

str = str & "Subject='" & Trim(txt_subject.Text) & "',"

str = str & "Title='" & Trim(txt_title.Text) & "',"

str = str & "Totalno=" & CDbl(txt_totalno.Text)

str = str & " WHERE Bookid=" & CDbl(txt_bookid.Text)

Bookconnection.Execute str

End If

'Make all entries input mode disable

Call setlock(True)

Bookrecord.Requery

Bookrecord.MoveLast

'show thw current data record

Call showdata

'message for status of mode

'lblStatus.Caption = " New record Saved."

MsgBox ("Record has been suceefully saved."), vbInformation, "Saving Record"

Call setbutton(True)

End If

Exit Sub

lable:

If Err.Number = -2147467259 Then

MsgBox ("BookID already exist,please enter anothe ID."), vbCritical, "BookID exist"

txt_bookid.SetFocus

ElseIf Err.Number <> 0 Then

MsgBox Err.Number & Err.Description

End If

End Sub

Private Sub SSTab1_Click(PreviousTab As Integer)

Call locate

Call showdata

End Sub

Code of Members Form:-

Dim Memconnection As ADODB.Connection

Dim Memrecordset As ADODB.Recordset

Dim Flexgridset As ADODB.Recordset

Dim temp As ADODB.Recordset

Dim bookshow As Boolean

Dim saveflag As Boolean

Dim lodbook As Boolean

Dim slct As String

Dim str As String

Private Sub clear()

txt_add.Text = ""

msk_bdate.Text = "__/__/____"

txt_bookhnd.Text = ""

txt_deposite.Text = ""

msk_expr.Text = "__/__/____"

msk_join.Text = "__/__/____"

txt_mail.Text = ""

txt_fname.Text = ""

txt_lname.Text = ""

txt_memid.Text = ""

txt_note.Text = ""

txt_phone.Text = ""

cmb_sex.Text = ""

End Sub

Private Sub locktext(val As Boolean)

txt_add.Locked = val

msk_bdate.Enabled = Not val

'txt_bookhnd.Locked = val

txt_deposite.Locked = val

msk_expr.Enabled = Not val

msk_join.Enabled = Not val

txt_mail.Locked = val

txt_fname.Locked = val

txt_lname.Locked = val

txt_memid.Locked = val

txt_note.Locked = val

txt_phone.Locked = val

cmb_sex.Locked = val

End Sub

Private Sub setbutton(val As Boolean)

cmd_new.Enabled = val

cmd_edit.Enabled = val

cmd_delete.Enabled = val

cmdFirst.Enabled = val

cmdLast.Enabled = val

cmdNext.Enabled = val

cmdPrevious.Enabled = val

cmd_cancel.Enabled = Not val

cmd_save.Enabled = Not val

End Sub

Private Function cheak() As Boolean

Dim flag As Boolean

flag = False

If txt_add.Text = "" Then

MsgBox "Please enter member's address.", vbInformation, "Information required"

ElseIf msk_bdate.Text = "__/__/____" Then

MsgBox "Please enter member's date of birth.", vbInformation, "Information required"

' ElseIf txt_bookhnd.Text = "" Then

' MsgBox "Please enter no of books contain by member.", vbInformation, "Information required"

ElseIf txt_deposite.Text = "" Then

MsgBox "Please enter deposite amount.", vbInformation, "Information required"

ElseIf msk_expr.Text = "__/__/____" Then

MsgBox "Please enter date of account expire.", vbInformation, "Information required"

ElseIf msk_join.Text = "__/__/____" Then

MsgBox "Please enter date of join.", vbInformation, "Information required"

ElseIf txt_fname.Text = "" Then

MsgBox "Please enter member's first name.", vbInformation, "Information required"

ElseIf txt_lname.Text = "" Then

MsgBox "Please enter member's last name or family name.", vbInformation, "Information required"

ElseIf txt_memid.Text = "" Then

MsgBox "Please enter member ID no.", vbInformation, "Information required"

ElseIf cmb_sex.Text = "" Then

MsgBox "Please select sex.", vbInformation, "Information required"

ElseIf (cmb_sex.Text <> "Male" And cmb_sex.Text <> "Female") Then

MsgBox ("Please select the sex."), vbInformation, "Invalid arguments"

ElseIf Not IsNumeric(txt_deposite.Text) Then

MsgBox ("Deposite must be Numeric value."), vbInformation, "Invalid arguments"

' ElseIf Not IsNumeric(txt_bookhnd.Text) Then

' MsgBox ("Book in hand must be Numeric."), vbInformation, "Invalid arguments"

ElseIf Not IsNumeric(txt_memid.Text) Then

MsgBox ("MemberID must be Numeric."), vbInformation, "Invalid arguments"

Else

flag = True

End If

cheak = flag

End Function

Private Sub cmd_books_Click()

If (bookshow = True) Then

Me.Height = 6900

cmd_book1.Visible = False

cmd_book2.Visible = True

Else

Me.Height = 8445

cmd_book1.Visible = True

cmd_book2.Visible = False

End If

bookshow = Not bookshow

End Sub

Private Sub cmd_book1_Click()

Call cmd_books_Click

End Sub

Private Sub cmd_book2_Click()

Call cmd_books_Click

End Sub

Private Sub cmd_close_Click()

Unload Me

End Sub

Private Sub cmd_cancel_Click()

On erro GoTo cancelerr

'disablink control

Call locktext(True)

' lblStatus.Caption = " Cancel."

If Memrecordset.BOF And Memrecordset.EOF Then

GoTo newproc

Else

Memrecordset.MoveFirst

Call showdata

End If

newproc:

txt_fname.SetFocus

Call setbutton(True)

Exit Sub

cancelerr:

MsgBox Err.Description

End Sub

Private Sub cmd_delete_Click()

On erro GoTo lable

Beep

str = "select Bookinhand from Member where Memid = " & CDbl(txt_memid.Text)

temp.Open str, Memconnection, adOpenStatic, adLockOptimistic

If temp(0) <> 0 Then

MsgBox "Member account cannot be deleeted because member has not returned books.", vbInformation, "Books not returned"

temp.Close

Exit Sub

End If

temp.Close

If MsgBox("Execution of command will delete current Datarecord,Are you sure you wan't to delete Datarecord ?", vbYesNo + vbExclamation, "Confirm Delete") = vbYes Then

str = "DELETE FROM Member WHERE "

str = str & "Memid = "

str = str & CDbl(txt_memid.Text)

Memconnection.Execute str

Memrecordset.Requery

MsgBox "Record deleted sucessfully.", vbinformayion, "Delete"

If Memrecordset.BOF And Memrecordset.EOF Then

Call clear

MsgBox ("The previous record was last record,Now no record left."), vbInformation, "Last record"

cmd_delete.Enabled = False

Else

Memrecordset.MoveNext

If Memrecordset.EOF Then

Memrecordset.MoveLast

End If

Call showdata

End If

'message for status of mode

'lblStatus.Caption = " Record deleted."

End If

Exit Sub

lable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub cmd_edit_Click()

Call locktext(False)

Call setbutton(False)

msk_bdate.Enabled = True

msk_expr.Enabled = True

msk_join.Enabled = True

txt_bookhnd.Locked = True

'cmd_cancel.Enabled = False

txt_fname.SetFocus

saveflag = False

'lblStatus.Caption = " Edit record."

End Sub

Private Sub cmd_new_Click()

Call locktext(False)

Call clear

Call setbutton(False)

msk_bdate.Enabled = True

msk_expr.Enabled = True

msk_join.Enabled = True

txt_bookhnd.Text = 0

txt_fname.SetFocus

saveflag = True

'lblStatus.Caption = " Add new record."

End Sub

Private Sub cmd_save_Click()

'error cheaking and autocorrection handle

On Error GoTo errlable

If (cheak = True) Then

If (txt_note.Text = "") Then

txt_note.Text = "None"

End If

If (txt_phone.Text = "") Then

txt_phone.Text = "None"

End If

If (txt_mail.Text = "") Then

txt_mail.Text = "None"

End If

If (saveflag = True) Then

txt_bookhnd.Text = 0

str = "INSERT INTO Member "

str = str & "(Address, Birthdate, Bookinhand, Deposite, Doexpire, Dojoin, Email, Fname, Lname, Memid, Noted, Phone, Sex) "

str = str & "VALUES('" & Trim(txt_add.Text) & "', "

str = str & "'" & Trim(msk_bdate.Text) & "', "

str = str & CDbl(txt_bookhnd.Text) & ", "

str = str & CDbl(Trim(txt_deposite.Text)) & ", "

str = str & "'" & Trim(msk_expr.Text) & "', "

str = str & "'" & Trim(msk_join.Text) & "', "

str = str & "'" & Trim(txt_mail.Text) & "', "

str = str & "'" & Trim(txt_fname.Text) & "', "

str = str & "'" & Trim(txt_lname.Text) & "', "

str = str & CDbl(Trim(txt_memid.Text)) & ", "

str = str & "'" & Trim(txt_note.Text) & "', "

str = str & "'" & Trim(txt_phone.Text) & "', "

str = str & "'" & Trim(cmb_sex.Text) & "' )"

'MsgBox str

Memconnection.Execute str

Else

str = "UPDATE Member SET "

str = str & " Address = '" & Trim(txt_add.Text) & "',"

str = str & " Birthdate = '" & Trim(msk_bdate.Text) & "',"

str = str & " Bookinhand = '" & Trim(txt_bookhnd.Text) & "',"

str = str & " Deposite = " & CDbl(txt_deposite.Text) & ","

str = str & " Doexpire = '" & Trim(msk_expr.Text) & "',"

str = str & " Dojoin = '" & Trim(msk_join.Text) & "',"

str = str & " Email = '" & Trim(txt_mail.Text) & "',"

str = str & " Fname = '" & Trim(txt_fname.Text) & "',"

str = str & " Lname = '" & Trim(txt_lname.Text) & "',"

str = str & " Memid = " & CDbl(txt_memid.Text) & ","

str = str & " Noted = '" & Trim(txt_note.Text) & "',"

str = str & " Phone = '" & Trim(txt_phone.Text) & "',"

str = str & " Sex = '" & Trim(cmb_sex.Text) & "'"

str = str & " WHERE Memid= " & CDbl(txt_memid.Text)

'MsgBox str

Memconnection.Execute str

End If

Memrecordset.Requery

Memrecordset.MoveFirst

MsgBox ("Record saved successfully."), vbInformation, "Save"

Call locktext(True)

Call setbutton(True)

Call showdata

End If

Exit Sub

errlable:

If (Err.Number = -2147467259) Then

MsgBox ("Member ID already exist,please enter anothe ID."), vbCritical, "MemberID exist"

txt_memid.SetFocus

ElseIf (Err.Number = -2147217913) Then

MsgBox ("May be date field pattern wrong."), vbCritical, "Date"

ElseIf Err.Number <> 0 Then

MsgBox Err.Number & Err.Description

End If

End Sub

Private Sub Form_Load()

On Error GoTo errlable

If (view = 1) Then

Me.Top = 50

Me.Left = 50

ElseIf (view = 2) Then

Me.Top = 700

Me.Left = (Screen.Width - Me.Width) / 2

End If

Image1.Picture = mdi_start.ImageList1.ListImages(7).Picture

Set Memconnection = New ADODB.Connection

Memconnection.CursorLocation = adUseClient

Memconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

Set Memrecordset = New ADODB.Recordset

Memrecordset.Open "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member Order by Memid", Memconnection, adOpenStatic, adLockOptimistic

Set DataGrid1.DataSource = Memrecordset

DataGrid1.ReBind

bookshow = False

lodbook = False

Set Flexgridset = New ADODB.Recordset

Set temp = New ADODB.Recordset

Call showdata

Call setbutton(True)

msk_bdate.Enabled = False

msk_expr.Enabled = False

msk_join.Enabled = False

cmd_book1.Visible = False

Exit Sub

errlable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub loadbook()

If Memrecordset.EOF = False And Memrecordset.BOF = False Then

again:

If (lodbook = False) Then

Flexgridset.Open "select Author1,Author2,Author3,Bookid,Edition,ISBNNumber,Pages,Price,Publication,Subject,Title,Avano,Issno,Totalno from Book where Bookid in(select Bookid from Issue where Memid=" & Trim(txt_memid.Text) & ")", Memconnection, adOpenStatic, adLockOptimistic

lodbook = True

Set Datagrid.DataSource = Flexgridset

Datagrid.ReBind

Else

Flexgridset.Close

lodbook = False

GoTo again

End If

End If

End Sub

Private Sub locate()

lbl_total.Caption = Memrecordset.RecordCount

lbl_rec.Caption = Memrecordset.AbsolutePosition

End Sub

Private Sub showdata()

If Memrecordset.EOF = False And Memrecordset.BOF = False Then

txt_add.Text = Memrecordset.Fields(0)

msk_bdate.Text = Format$(Memrecordset.Fields(1), "MM/dd/yyyy")

txt_bookhnd.Text = Memrecordset.Fields(2)

txt_deposite.Text = Memrecordset.Fields(3)

msk_expr.Text = Format$(Memrecordset.Fields(4), "MM/dd/yyyy")

msk_join.Text = Format$(Memrecordset.Fields(5), "MM/dd/yyyy")

txt_mail.Text = Memrecordset.Fields(6)

txt_fname.Text = Memrecordset.Fields(7)

txt_lname.Text = Memrecordset.Fields(8)

txt_memid.Text = Memrecordset.Fields(9)

txt_note.Text = Memrecordset.Fields(10)

txt_phone.Text = Memrecordset.Fields(11)

cmb_sex.Text = Memrecordset.Fields(12)

End If

Call locate

If bookshow Then

Call loadbook

End If

End Sub

Private Sub cmdFirst_Click()

On Error GoTo GoFirstError

Memrecordset.MoveFirst

' lblStatus.Caption = " << Move"

'show thw current data record

Call showdata

Exit Sub

GoFirstError:

MsgBox Err.Description

End Sub

Private Sub cmdLast_Click()

On Error GoTo GoLastError

' lblStatus.Caption = " Move >>"

Memrecordset.MoveLast

'show thw current data record

Call showdata

Exit Sub

GoLastError:

MsgBox Err.Description

End Sub

Private Sub cmdNext_Click()

Dim my As String

On Error GoTo GoNextError

'lblStatus.Caption = " Move >"

If Not Memrecordset.EOF Then Memrecordset.MoveNext

If Memrecordset.EOF And Memrecordset.RecordCount > 0 Then

Beep

'moved off the end so go back

Memrecordset.MoveLast

End If

'show thw current data record

Call showdata

Exit Sub

GoNextError:

MsgBox Err.Description

End Sub

Private Sub cmdPrevious_Click()

On Error GoTo GoPrevError

' lblStatus.Caption = " < Move"

If Not Memrecordset.BOF Then Memrecordset.MovePrevious

If Memrecordset.BOF And Memrecordset.RecordCount > 0 Then

Beep

'moved off the end so go back

Memrecordset.MovePrevious

End If

'show thw current data record

Call showdata

Exit Sub

GoPrevError:

If Err.Number = 3021 Then

MsgBox ("This is first Record."), vbInformation, "First record"

Memrecordset.MoveNext

ElseIf Err.Number <> 0 Then

MsgBox Err.Number & Err.Description

End If

End Sub

Private Sub SSTab1_Click(PreviousTab As Integer)

Call locate

Call showdata

End Sub

Code of Fine Form:-

Dim str As String

Dim Fineconn As ADODB.Connection

Dim Finerecord As ADODB.Recordset

Private Sub cmd_back_Click()

Load Frm_return

Frm_return.Show

Unload Me

End Sub

Private Sub Datagrid_Click()

End Sub

Private Sub Form_Load()

On Error GoTo errlable

If (view = 1) Then

Me.Top = 50

Me.Left = 50

ElseIf (view = 2) Then

Me.Top = 700

Me.Left = (Screen.Width - Me.Width) / 2

End If

Image1.Picture = mdi_start.ImageList1.ListImages(14).Picture

Set Fineconn = New ADODB.Connection

Fineconn.CursorLocation = adUseClient

Fineconn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

str = "Select Memid,Bookid,Fine,Areturndate from Fine order by Memid"

Set Finerecord = New ADODB.Recordset

Finerecord.Open str, Fineconn, adOpenStatic, adLockOptimistic

Datagrid.Visible = True

Set Datagrid.DataSource = Finerecord

Datagrid.ReBind

Call locate

Exit Sub

errlable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub cmdFirst_Click()

On Error GoTo GoFirstError

Finerecord.MoveFirst

'show thw current data record

Call locate

Exit Sub

GoFirstError:

MsgBox Err.Description

End Sub

Private Sub cmdLast_Click()

On Error GoTo GoLastError

Finerecord.MoveLast

'show thw current data record

Call locate

Exit Sub

GoLastError:

MsgBox Err.Description

End Sub

Private Sub cmdNext_Click()

On Error GoTo GoNextError

If Not Finerecord.EOF Then Finerecord.MoveNext

If Finerecord.EOF And Finerecord.RecordCount > 0 Then

Beep

'moved off the end so go back

Finerecord.MoveLast

End If

'show thw current data record

Call locate

Exit Sub

GoNextError:

MsgBox Err.Description

End Sub

Private Sub locate()

lbl_total.Caption = Finerecord.RecordCount

lbl_rec.Caption = Finerecord.AbsolutePosition

End Sub

Private Sub cmdPrevious_Click()

On Error GoTo GoPrevError

If Not Finerecord.BOF Then Finerecord.MovePrevious

If Finerecord.BOF And Finerecord.RecordCount > 0 Then

Beep

'moved off the end so go back

Finerecord.MovePrevious

End If

'show thw current data record

Call locate

Exit Sub

GoPrevError:

If Err.Number = 3021 Then

MsgBox ("This is first Record."), vbInformation, "First record"

Finerecord.MoveNext

ElseIf Err.Number <> 0 Then

MsgBox Err.Number & Err.Description

End If

End Sub

Code of Issue Form:-

Dim str As String

Dim rmem As ADODB.Recordset

Dim rbook As ADODB.Recordset

Dim riss As ADODB.Recordset

Dim Issueconnection As ADODB.Connection

Dim Issuerecord As ADODB.Recordset

Private Sub cmd_add_Click()

Call cleartext

Call setbutton(False)

Call locktext(False)

msk_issue.Text = Format$(Now, "mm/dd/yyyy")

'msk_issue.Enabled = False

msk_return.Text = Format$(Now + dayslimit, "mm/dd/yyyy")

'msk_return.Enabled = False

End Sub

Private Sub locate()

lbl_total.Caption = Issuerecord.RecordCount

lbl_rec.Caption = Issuerecord.AbsolutePosition

End Sub

Private Sub locktext(val As Boolean)

txt_bookid.Locked = val

msk_issue.Enabled = Not val

msk_return.Enabled = Not val

txt_memid.Locked = val

End Sub

Private Sub setbutton(val As Boolean)

cmd_add.Enabled = val

cmd_Return.Enabled = val

cmdFirst.Enabled = val

cmdLast.Enabled = val

cmdNext.Enabled = val

cmdPrevious.Enabled = val

cmd_issue.Enabled = Not val

cmd_cancel.Enabled = Not val

End Sub

Private Function cheak() As Boolean

Dim flag As Boolean

flag = False

If msk_return.Text = "__/__/____" Then

MsgBox "Please select the date.", vbInformation, "Field missing"

ElseIf msk_issue.Text = "__/__/____" Then

ElseIf txt_bookid.Text = "" Then

MsgBox "Please enter the Bookid.", vbInformation, "Field missing"

ElseIf txt_memid.Text = "" Then

MsgBox "Please enter the Memberid.", vbInformation, "Field missing"

Else

flag = True

End If

cheak = flag

End Function

Private Sub cleartext()

txt_bookid.Text = ""

msk_issue.Text = "__/__/____"

msk_return.Text = "__/__/____"

txt_memid.Text = ""

End Sub

Private Sub cmd_cancel_Click()

Call locktext(True)

Call setbutton(True)

If Not (Issuerecord.BOF And Issuerecord.EOF) Then

Issuerecord.MoveFirst

Call showdata

End If

End Sub

Private Sub cmd_issue_Click()

On Error GoTo errlable

If (cheak = True) Then

'If member id exists

str = "select count(*) from Member where Memid = " & Trim(txt_memid.Text)

rmem.Open str, Issueconnection, adOpenStatic, adLockOptimistic

If rmem(0) = 0 Then

MsgBox ("Member with mentioned memberID does not exists."), vbCritical, "Invalid arguments"

rmem.Close

Exit Sub

Else

'Is capable of holding book.

rmem.Close

str = "select Bookinhand from Member where Memid = " & Trim(txt_memid.Text)

rmem.Open str, Issueconnection, adOpenStatic, adLockOptimistic

If rmem(0) = maxhold Then

MsgBox ("Members can not hold books greater then " & maxhold & "."), vbCritical, "Invalid arguments"

rmem.Close

GoTo recycle

End If

End If

rmem.Close

'if book is present for specified bookid

str = "select count(*) from Book where Bookid = " & Trim(txt_bookid.Text)

rbook.Open str, Issueconnection, adOpenStatic, adLockOptimistic

If rbook(0) = 0 Then

MsgBox ("Book with mentioned bookid does not exists."), vbCritical, "Invalid arguments"

rbook.Close

Exit Sub

Else

'is there available copy

rbook.Close

str = "select Avano from Book where Bookid = " & Trim(txt_bookid.Text)

rbook.Open str, Issueconnection, adOpenStatic, adLockOptimistic

If rbook(0) <= refcopy Then

MsgBox ("Book contains only refrence copies which cannot be issued."), vbCritical, "Invalid arguments"

rbook.Close

GoTo recycle

End If

End If

rbook.Close

'member has same book or not

str = "Select count(*) from Issue where Bookid = " & Trim(txt_bookid.Text) & " And Memid = " & Trim(txt_memid.Text)

riss.Open str, Issueconnection, adOpenStatic, adLockOptimistic

If (riss(0) <> 0) Then

MsgBox ("Member has already issue mentioned book copy.member can not take same book again."), vbCritical, "Invalid arguments"

riss.Close

Exit Sub

End If

Beep

If MsgBox("Issue Info.:MemberId=" & CDbl(txt_memid.Text) & " And BookId=" & CDbl(txt_bookid.Text), vbYesNo, "Confirm Data") = vbYes Then

str = "INSERT INTO Issue"

str = str & " (Areturndate,Bookid,Issuedate,Returndate,Memid) "

str = str & "VALUES('" & Trim(msk_return.Text) & "', "

str = str & CDbl(txt_bookid.Text) & ", "

str = str & "'" & Trim(msk_issue.Text) & "', "

str = str & "'" & Trim(msk_return.Text) & "', "

str = str & CDbl(txt_memid.Text) & ")"

Issueconnection.Execute str

str = "UPDATE Book SET "

str = str & "Avano = Avano-1,"

str = str & "Issno = Issno+1 where Bookid = " & Trim(txt_bookid.Text)

Issueconnection.Execute str

str = "UPDATE Member SET "

str = str & "Bookinhand = Bookinhand+1 where Memid = " & Trim(txt_memid.Text)

Issueconnection.Execute str

Issuerecord.Requery

MsgBox "All entry Updated sucessfully.", vbInformation, "Record saved"

Call locktext(True)

Call setbutton(True)

Else

recycle:

Call locktext(True)

Call setbutton(True)

Call cleartext

End If

End If

Exit Sub

errlable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub cmd_Return_Click()

Load Frm_return

Frm_return.Show

Unload Me

End Sub

Private Sub Form_Load()

On Error GoTo lable

If (view = 1) Then

Me.Top = 50

Me.Left = 50

ElseIf (view = 2) Then

Me.Top = 700

Me.Left = (Screen.Width - Me.Width) / 2

End If

Image1.Picture = mdi_start.ImageList1.ListImages(5).Picture

Set Issueconnection = New ADODB.Connection

Issueconnection.CursorLocation = adUseClient

Issueconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

Set Issuerecord = New ADODB.Recordset

Issuerecord.Open "Select Areturndate,Bookid,Issuedate,Returndate,Memid from Issue Order by Memid", Issueconnection, adOpenStatic, adLockOptimistic

Set rmem = New ADODB.Recordset

Set rbook = New ADODB.Recordset

Set riss = New ADODB.Recordset

Call showdata

Call setbutton(True)

Call locktext(True)

Exit Sub

lable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub showdata()

If Issuerecord.EOF = False And Issuerecord.BOF = False Then

'msk_return.Text = Issuerecord.Fields(0)

txt_bookid.Text = Issuerecord.Fields(1)

msk_issue.Text = Format$(Issuerecord.Fields(2), "mm/dd/yyyy")

msk_return.Text = Format$(Issuerecord.Fields(3), "dd/mm/yyyy")

txt_memid.Text = Issuerecord.Fields(4)

End If

Call locate

End Sub

Private Sub cmdFirst_Click()

On Error GoTo GoFirstError

Issuerecord.MoveFirst

'show thw current data record

Call showdata

Exit Sub

GoFirstError:

MsgBox Err.Description

End Sub

Private Sub cmdLast_Click()

On Error GoTo GoLastError

Issuerecord.MoveLast

'show thw current data record

Call showdata

Exit Sub

GoLastError:

MsgBox Err.Description

End Sub

Private Sub cmdNext_Click()

Dim my As String

On Error GoTo GoNextError

If Not Issuerecord.EOF Then Issuerecord.MoveNext

If Issuerecord.EOF And Issuerecord.RecordCount > 0 Then

Beep

'moved off the end so go back

Issuerecord.MoveLast

End If

'show thw current data record

Call showdata

Exit Sub

GoNextError:

MsgBox Err.Description

End Sub

Private Sub cmdPrevious_Click()

On Error GoTo GoPrevError

If Not Issuerecord.BOF Then Issuerecord.MovePrevious

If Issuerecord.BOF And Issuerecord.RecordCount > 0 Then

Beep

'moved off the end so go back

Issuerecord.MovePrevious

End If

'show thw current data record

Call showdata

Exit Sub

GoPrevError:

If Err.Number = 3021 Then

MsgBox ("This is first Record."), vbInformation, "First record"

Issuerecord.MoveNext

ElseIf Err.Number <> 0 Then

MsgBox Err.Number & Err.Description

End If

End Sub

Code of Return Form:-

Dim i As Integer

Dim amount As Integer

Dim str As String

Dim temp As ADODB.Recordset

Dim Returnconnection As ADODB.Connection

Private Sub setlock(val As Boolean)

msk_return.Enabled = Not val

txt_bookid.Locked = val

txt_memid.Locked = val

End Sub

Private Sub setbutton(val As Boolean)

cmd_add.Enabled = val

cmd_Return.Enabled = Not val

cmd_cancel.Enabled = Not val

End Sub

Private Sub cleartext()

msk_return.Text = "__/__/____"

txt_bookid.Text = ""

txt_memid.Text = ""

txt_fine.Text = ""

End Sub

Private Function cheak() As Boolean

Dim flag As Boolean

flag = False

If msk_return.Text = "__/__/____" Then

MsgBox "Please select the date.", vbInformation, "Field missing"

ElseIf txt_bookid.Text = "" Then

MsgBox "Please enter the Bookid.", vbInformation, "Field missing"

ElseIf txt_memid.Text = "" Then

MsgBox "Please enter the Memberid.", vbInformation, "Field missing"

Else

flag = True

End If

cheak = flag

End Function

Private Sub cmd_add_Click()

Call setlock(False)

Call setbutton(False)

Call cleartext

msk_return.Text = Format$(Now, "mm/dd/yyyy")

'msk_return.Enabled = False

End Sub

Private Sub cmd_cancel_Click()

Call setlock(True)

Call cleartext

Call setbutton(True)

End Sub

Private Sub cmd_fine_Click()

Load Frm_Fine

Frm_Fine.Show

Unload Me

End Sub

Private Sub cmd_issue_Click()

Load Frm_issue

Frm_issue.Show

Unload Me

End Sub

Private Sub cmd_Return_Click()

On Error GoTo errlable

If (cheak = True) Then

'Search for return bookid and memid entry

str = "select count(*) from Issue where Memid = " & CDbl(txt_memid.Text) & " and Bookid = " & CDbl(txt_bookid.Text)

temp.Open str, Returnconnection, adOpenStatic, adLockOptimistic

If (temp(0) = 0) Then

MsgBox "There is no such book issued for specified fields.", vbCritical, "Invalid arguments "

temp.Close

Call setlock(True)

Call setbutton(True)

Exit Sub

End If

temp.Close

'display info. & ask user for allow

If MsgBox("Return Info.:MemberId=" & CDbl(txt_memid.Text) & " And BookId=" & CDbl(txt_bookid.Text), vbYesNo, "Confirm Data") = vbYes Then

str = "select Areturndate,Bookid,Issuedate,Returndate,Memid from Issue where Memid = " & CDbl(txt_memid.Text) & " and Bookid = " & CDbl(txt_bookid.Text)

temp.Open str, Returnconnection, adOpenStatic, adLockOptimistic

amount = (Date - temp.Fields(3)) * fratepday

ignoreoverflow:

If (amount < 0) Then

amount = 0 'convert negative amount to zero

End If

' for amount case

If (amount <= 0) Then

GoTo withoutfine 'submit book without fine

ElseIf (amount > 0) Then

'option for providing fine amount

i = MsgBox("Members Total fine amount Rs : " & amount & " as per Rs : " & fratepday & " per Day charge.click yes if paying or click No if fine is collected from Members Deposite.", vbYesNoCancel + vbExclamation, "Confirm Data")

Select Case i

Case vbYes

Case vbNo

'transfer from deposite

str = "UPDATE Member SET Deposite = Deposite-" & CDbl(amount) & " WHERE Memid= " & Trim(txt_memid.Text)

Returnconnection.Execute str

MsgBox "The fine amount is transfer from members deposite.", vbInformation, "Fine"

Case vbCancel

'cancelling process of making entry

Call setlock(True)

Call setbutton(True)

MsgBox "Return process was cancelled.No more entry Updated.", vbInformation, "Fine"

Exit Sub

End Select

'make entry in fine table

str = "INSERT INTO Fine (Areturndate,Bookid,Fine,Memid)"

str = str & "VALUES ('" & Format$(msk_return.Text, "mm/dd/yyyy") & "', "

str = str & CDbl(txt_bookid.Text) & ", "

str = str & CDbl(amount) & ", "

str = str & CDbl(txt_memid.Text) & ")"

Returnconnection.Execute str

withoutfine: 'Update entry in Book table

str = "UPDATE Book SET "

str = str & "Avano = Avano+1,"

str = str & "Issno = Issno-1 WHERE Bookid = " & Trim(txt_bookid.Text)

Returnconnection.Execute str

'Update entry in member table

str = "UPDATE Member SET "

str = str & "Bookinhand = Bookinhand-1 WHERE Memid = " & Trim(txt_memid.Text)

Returnconnection.Execute str

'delete entry from Issue table

str = "DELETE * FROM Issue WHERE Memid = " & CDbl(txt_memid.Text) & " and Bookid = " & CDbl(txt_bookid.Text)

Returnconnection.Execute str

txt_fine.Text = amount

MsgBox "fields entry Updated succesfully", vbInformation, "Book returned"

End If

Else

Call setlock(True)

Call setbutton(True)

Exit Sub

End If

Call setlock(True)

Call setbutton(True)

End If 'validity check condition over

Exit Sub

errlable:

If (Err.Number = 6) Then

amount = 0

GoTo ignoreoverflow

ElseIf (Err.Number <> 0) Then

MsgBox Err.Number & Err.Description

End If

End Sub

Private Sub Form_Load()

On Error GoTo errlable

If (view = 1) Then

Me.Top = 50

Me.Left = 50

ElseIf (view = 2) Then

Me.Top = 700

Me.Left = (Screen.Width - Me.Width) / 2

End If

Image1.Picture = mdi_start.ImageList1.ListImages(6).Picture

Set Returnconnection = New ADODB.Connection

Returnconnection.CursorLocation = adUseClient

Returnconnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

Set temp = New ADODB.Recordset

txt_fine.Locked = True

Call setlock(True)

Call setbutton(True)

Exit Sub

errlable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub Label1_Click()

End Sub

Private Sub txt_fine_GotFocus()

MsgBox "Fine amount will be decided by itself.", vbInformation, "Self field propery"

End Sub

Code of Search Form:-

Dim fnd As String

Dim mflag As Boolean

Dim bflag As Boolean

Dim conn As ADODB.Connection

Dim MR As ADODB.Recordset

Dim BR As ADODB.Recordset

Private Sub cmb_bfield_click()

If (cmb_bfield.Text = "All") Then

txt_bvalue.Enabled = False

Else

txt_bvalue.Enabled = True

End If

lbl_status.Caption = " Search for book's Record field."

txt_bvalue.Text = ""

End Sub

Private Sub cmb_mfield_click()

If (cmb_mfield.Text = "All") Then

txt_mvalue.Enabled = False

Else

txt_mvalue.Enabled = True

End If

txt_mvalue.Text = ""

lbl_status.Caption = " Search for Member's Record field."

End Sub

Private Sub cmd_bsearch_Click()

On eror GoTo errlable:

'write code for validity

again:

bpbar.Value = 0

If (cmb_bfield.Text = "All" Or txt_bvalue.Text = "") Then

fnd = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book order by Bookid"

lbl_status.Caption = " Search for Book's Record field Alldata."

bpbar.Value = 30

ElseIf (cmb_bfield.Text = "Author") Then

fnd = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book where Author1 like'" & Trim(txt_bvalue.Text) & "%' or Author2 like'" & Trim(txt_bvalue.Text) & "%' or Author3 like'" & Trim(txt_bvalue.Text) & "%'"

lbl_status.Caption = " Search for Book's Record field Author."

bpbar.Value = 30

ElseIf (cmb_bfield.Text = "Price" Or cmb_bfield.Text = "Pages" Or cmb_bfield.Text = "Bookid") Then

If IsNumeric(txt_bvalue.Text) Then

fnd = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book where " & Trim(cmb_bfield) & " = " & Trim(txt_bvalue)

lbl_status.Caption = " Search for Book's Record field " & Trim(cmb_bfield.Text) & " of book."

bpbar.Value = 30

Else

txt_bvalue.Text = ""

Exit Sub

End If

Else

fnd = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book where " & Trim(cmb_bfield) & " like '" & Trim(txt_bvalue) & "%'"

lbl_status.Caption = " Search for Book's Record field " & Trim(cmb_bfield.Text) & " of book."

bpbar.Value = 30

End If

If (bflag = False) Then

BR.Open fnd, conn, adOpenStatic, adLockOptimistic

bpbar.Value = 50

bdatagrid.Visible = True

Set bdatagrid.DataSource = BR

bpbar.Value = 70

bdatagrid.ReBind

bflag = True

bpbar.Value = 85

Else

bflag = False

BR.Close

GoTo again

bpbar.Value = 90

End If

bpbar.Value = 100

bpbar.Value = 0

Exit Sub

errlable:

bpbar.Value = 0

MsgBox Err.Description

End Sub

Private Sub Form_Load()

On Error GoTo errlable

If (view = 1) Then

Me.Top = 50

Me.Left = 50

ElseIf (view = 2) Then

Me.Top = 700

Me.Left = (Screen.Width - Me.Width) / 2

End If

Image1.Picture = mdi_start.ImageList1.ListImages(2).Picture

Set conn = New ADODB.Connection

conn.CursorLocation = adUseClient

conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

Set BR = New ADODB.Recordset

Set MR = New ADODB.Recordset

lbl_status.Caption = " Choose the options for Datamember,Field and values for search."

Exit Sub

errlable:

MsgBox Err.Number & " " & Err.Description

End Sub

Private Sub Form_Unload(Cancel As Integer)

Unload Me

End Sub

Private Sub SSTab1_DblClick()

End Sub

Private Sub txt_msearch_Click()

'write a code validity

On Error GoTo errlable

again:

mpbar.Value = 0

lbl_status.Caption = " Search for Member's Record field " & Trim(cmb_mfield.Text) & " of Member."

If (cmb_mfield.Text = "All" Or txt_mvalue.Text = "") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member Order by Memid"

lbl_status.Caption = " Search for Member's Record field Alldata."

mpbar.Value = 40

ElseIf (cmb_mfield.Text = "First name") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Fname like '" & Trim(txt_mvalue.Text) & "%'"

mpbar.Value = 40

ElseIf (cmb_mfield.Text = "Last name") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Lname like '" & Trim(txt_mvalue.Text) & "%'"

mpbar.Value = 40

ElseIf (cmb_mfield.Text = "Member id") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Memid=" & Trim(txt_mvalue.Text)

mpbar.Value = 40

ElseIf (cmb_mfield.Text = "Address") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Address like '" & Trim(txt_mvalue.Text) & "%'"

mpbar.Value = 40

ElseIf (cmb_mfield.Text = "Phone") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Phone like'" & Trim(txt_mvalue.Text) & "%'"

mpbar.Value = 40

ElseIf (cmb_mfield.Text = "Email") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Email like'" & Trim(txt_mvalue.Text) & "%'"

mpbar.Value = 40

ElseIf (cmb_mfield.Text = "Birth date") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Birthdate like'" & Trim(txt_mvalue.Text) & "%'"

mpbar.Value = 40

ElseIf (cmb_mfield.Text = "Date of join") Then

fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Dojoin like'" & Trim(txt_mvalue.Text) & "%'"

mpbar.Value = 40

End If

If (mflag = False) Then

MR.Open fnd, conn, adOpenStatic, adLockOptimistic

mpbar.Value = 65

mdatagrid.Visible = True

Set mdatagrid.DataSource = MR

mpbar.Value = 80

mdatagrid.ReBind

mflag = True

mpbar.Value = 90

Else

mflag = False

MR.Close

GoTo again

End If

mpbar.Value = 100

mpbar.Value = 0

Exit Sub

errlable:

mpbar.Value = 0

MsgBox Err.Number & " " & Err.Description

End Sub

Code of Global Form:-

Dim bookr As ADODB.Recordset

Dim empr As ADODB.Recordset

Dim memr As ADODB.Recordset

Dim finer As ADODB.Recordset

Dim Database As ADODB.Connection

Dim str As String

Private Sub Form_Load()

On Error GoTo errlable

If (view = 1) Then

Me.Top = 50

Me.Left = 50

ElseIf (view = 2) Then

Me.Top = 700

Me.Left = (Screen.Width - Me.Width) / 2

End If

Image1.Picture = mdi_start.ImageList1.ListImages(3).Picture

Set Database = New ADODB.Connection

Database.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

Call updatedata

Call showdata

Exit Sub

errlable:

MsgBox Err.Number & Err.Description

End Sub

Private Sub showdata()

If memr.Fields(0) <> 0 Then

txt_tnmem.Text = memr.Fields(0)

txt_deposite.Text = memr.Fields(1)

Else

txt_tnmem.Text = 0

txt_deposite.Text = 0

End If

If empr.Fields(0) <> 0 Then

txt_tnemp.Text = empr.Fields(0)

txt_salary.Text = empr.Fields(1)

Else

txt_tnemp.Text = 0

txt_salary.Text = 0

End If

If bookr.Fields(4) <> 0 Then

txt_tnbooks.Text = bookr.Fields(0)

txt_avabooks.Text = bookr.Fields(1)

txt_issbooks.Text = bookr.Fields(2)

txt_investment.Text = bookr.Fields(3)

txt_typebook.Text = bookr.Fields(4)

Else

txt_tnbooks.Text = 0

txt_avabooks.Text = 0

txt_issbooks.Text = 0

txt_investment.Text = 0

txt_typebook.Text = 0

End If

If (finer.Fields(0) <> 0) Then

txt_finem.Text = finer.Fields(0)

txt_amount.Text = finer.Fields(1)

Else

txt_finem.Text = 0

txt_amount.Text = 0

End If

End Sub

Private Sub updatedata()

Set bookr = New ADODB.Recordset

str = "select sum(Totalno),sum(Avano),sum(Issno),sum(Price*Totalno),count(*) from Book"

bookr.Open str, Database, adOpenStatic, adLockOptimistic

Set memr = New ADODB.Recordset

str = "select count(*),sum(Deposite) from member"

memr.Open str, Database, adOpenStatic, adLockOptimistic

Set empr = New ADODB.Recordset

str = "select count(*),sum(Salary) from Emptab"

empr.Open str, Database, adOpenStatic, adLockOptimistic

Set finer = New ADODB.Recordset

str = "Select count(*),sum(Fine) from Fine"

finer.Open str, Database, adOpenStatic, adLockOptimistic

End Sub

Private Sub Frame1_DragDrop(Source As Control, X As Single, Y As Single)

End Sub

Code of Keyboard Form:-

Private Sub Form_Load()

If (view = 1) Then

Me.Top = 50

Me.Left = 50

ElseIf (view = 2) Then

Me.Top = 700

Me.Left = (Screen.Width - Me.Width) / 2

End If

Image1.Picture = mdi_start.ImageList1.ListImages(12).Picture

End Sub

Private Sub Text1_Change()

End Sub

Code of Book Report Form:-

Dim rs As ADODB.Recordset

Dim db As ADODB.Connection

Dim status As Boolean

Dim str As String

Private Sub Combo1_Click()

If (Combo1.Text = "All") Then

Text1.Text = ""

Text1.Locked = True

ElseIf (Combo1.Text = "Book ID") Then

Text1.Text = ""

Text1.Locked = False

End If

End Sub

Private Sub Command1_Click()

If (Combo1.Text <> "All" And Combo1.Text <> "Book ID") Then

MsgBox "Please select proper Book specifications.", vbCritical, "Invalid Data"

Exit Sub

End If

If (Combo1.Text = "All") Then

str = "Select * from Book"

ElseIf (Combo1.Text = "Book ID") Then

If (Text1.Text <> "") Then

If IsNumeric(Text1.Text) Then

str = "Select * from Book where Bookid=" & Text1.Text

Else

MsgBox ("Please enter Book ID Numeric value."), vbExclamation, "Invalid value"

Exit Sub

End If

Else

MsgBox ("Please enter Book ID."), vbExclamation, "Invalid value"

Exit Sub

End If

End If

again:

If (status = False) Then

rs.Open str, db, adOpenStatic, adLockOptimistic

status = True

Else

rs.Close

status = False

GoTo again

End If

Set Rpt_book.DataSource = rs

Rpt_book.Show vbModal

End Sub

Private Sub Form_Load()

Set db = New ADODB.Connection

Set rs = New ADODB.Recordset

db.CursorLocation = adUseClient

db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

status = False

End Sub

Code of Member Report Form:-

Dim rs As ADODB.Recordset

Dim db As ADODB.Connection

Dim status As Boolean

Dim str As String

Private Sub Combo1_Click()

If (Combo1.Text = "All") Then

Text1.Text = ""

Text1.Locked = True

ElseIf (Combo1.Text = "Member ID") Then

Text1.Text = ""

Text1.Locked = False

End If

End Sub

Private Sub Command1_Click()

If (Combo1.Text <> "All" And Combo1.Text <> "Member ID") Then

MsgBox "Please select proper Member specifications.", vbCritical, "Invalid Data"

Exit Sub

End If

If (Combo1.Text = "All") Then

str = "Select * from member"

ElseIf (Combo1.Text = "Member ID") Then

If (Text1.Text <> "") Then

If IsNumeric(Text1.Text) Then

str = "Select * from member where Memid=" & Text1.Text

Else

MsgBox ("Please enter member ID Numeric value."), vbExclamation, "Invalid value"

Exit Sub

End If

Else

MsgBox ("Please enter member ID."), vbExclamation, "Invalid value"

Exit Sub

End If

End If

again:

If (status = False) Then

rs.Open str, db, adOpenStatic, adLockOptimistic

status = True

Else

rs.Close

status = False

GoTo again

End If

Set Rpt_member.DataSource = rs

Rpt_member.Show vbModal

End Sub

Private Sub Form_Load()

Set db = New ADODB.Connection

Set rs = New ADODB.Recordset

db.CursorLocation = adUseClient

db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

status = False

End Sub

Private Sub Frame1_DragDrop(Source As Control, X As Single, Y As Single)

End Sub

Code of Issue Report Form:-

Dim rs As ADODB.Recordset

Dim db As ADODB.Connection

Dim status As Boolean

Dim str As String

Private Sub Combo1_Click()

If (Combo1.Text = "Specific Member") Then

txt_memid.Locked = False

ElseIf (Combo1.Text = "All") Then

txt_memid.Locked = True

End If

txt_memid.Text = ""

End Sub

Private Sub Command1_Click()

If (Combo1.Text <> "Specific Member" And Combo1.Text <> "All") Then

MsgBox "Please select proper Member specifications.", vbCritical, "Invalid Data"

Exit Sub

End If

If (Combo1.Text = "Specific Member") Then

If (txt_memid.Text <> "") Then

If IsNumeric(txt_memid.Text) Then

str = "select * from Issue where Memid=" & txt_memid.Text

Else

MsgBox "Please enter Member ID Numeric.", vbCritical, "Data missing"

Exit Sub

End If

Else

MsgBox "Please enter Member ID.", vbCritical, "Invalid Data"

Exit Sub

End If

Else

str = "select * from Issue"

End If

again:

If (status = False) Then

rs.Open str, db, adOpenStatic, adLockOptimistic

status = True

Else

rs.Close

status = False

GoTo again

End If

Set Rpt_Issret.DataSource = rs

Rpt_Issret.Show vbModal

End Sub

Private Sub Form_Load()

status = False

Set rs = New ADODB.Recordset

Set rs1 = New ADODB.Recordset

Set db = New ADODB.Connection

db.CursorLocation = adUseClient

db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

End Sub

Private Sub Frame1_DragDrop(Source As Control, X As Single, Y As Single)

End Sub

Code of About Form:-

Option Explicit

' Reg Key Security Options...

Const READ_CONTROL = &H20000

Const KEY_QUERY_VALUE = &H1

Const KEY_SET_VALUE = &H2

Const KEY_CREATE_SUB_KEY = &H4

Const KEY_ENUMERATE_SUB_KEYS = &H8

Const KEY_NOTIFY = &H10

Const KEY_CREATE_LINK = &H20

Const KEY_ALL_ACCESS = KEY_QUERY_VALUE + KEY_SET_VALUE + _

KEY_CREATE_SUB_KEY + KEY_ENUMERATE_SUB_KEYS + _

KEY_NOTIFY + KEY_CREATE_LINK + READ_CONTROL

' Reg Key ROOT Types...

Const HKEY_LOCAL_MACHINE = &H80000002

Const ERROR_SUCCESS = 0

Const REG_SZ = 1 ' Unicode nul terminated string

Const REG_DWORD = 4 ' 32-bit number

Const gREGKEYSYSINFOLOC = "SOFTWARE\Microsoft\Shared Tools Location"

Const gREGVALSYSINFOLOC = "MSINFO"

Const gREGKEYSYSINFO = "SOFTWARE\Microsoft\Shared Tools\MSINFO"

Const gREGVALSYSINFO = "PATH"

Private Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, ByRef phkResult As Long) As Long

Private Declare Function RegQueryValueEx Lib "advapi32" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, ByRef lpType As Long, ByVal lpData As String, ByRef lpcbData As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long

Private Sub cmdSysInfo_Click()

Call StartSysInfo

End Sub

Private Sub cmdOK_Click()

Unload Me

End Sub

Public Sub StartSysInfo()

On Error GoTo SysInfoErr

Dim rc As Long

Dim SysInfoPath As String

' Try To Get System Info Program Path\Name From Registry...

If GetKeyValue(HKEY_LOCAL_MACHINE, gREGKEYSYSINFO, gREGVALSYSINFO, SysInfoPath) Then

' Try To Get System Info Program Path Only From Registry...

ElseIf GetKeyValue(HKEY_LOCAL_MACHINE, gREGKEYSYSINFOLOC, gREGVALSYSINFOLOC, SysInfoPath) Then

' Validate Existance Of Known 32 Bit File Version

If (Dir(SysInfoPath & "\MSINFO32.EXE") <> "") Then

SysInfoPath = SysInfoPath & "\MSINFO32.EXE"

' Error - File Can Not Be Found...

Else

GoTo SysInfoErr

End If

' Error - Registry Entry Can Not Be Found...

Else

GoTo SysInfoErr

End If

Call Shell(SysInfoPath, vbNormalFocus)

Exit Sub

SysInfoErr:

MsgBox "System Information Is Unavailable At This Time", vbOKOnly

End Sub

Public Function GetKeyValue(KeyRoot As Long, KeyName As String, SubKeyRef As String, ByRef KeyVal As String) As Boolean

Dim i As Long ' Loop Counter

Dim rc As Long ' Return Code

Dim hKey As Long ' Handle To An Open Registry Key

Dim hDepth As Long '

Dim KeyValType As Long ' Data Type Of A Registry Key

Dim tmpVal As String ' Tempory Storage For A Registry Key Value

Dim KeyValSize As Long ' Size Of Registry Key Variable

'------------------------------------------------------------

' Open RegKey Under KeyRoot {HKEY_LOCAL_MACHINE...}

'------------------------------------------------------------

rc = RegOpenKeyEx(KeyRoot, KeyName, 0, KEY_ALL_ACCESS, hKey) ' Open Registry Key

If (rc <> ERROR_SUCCESS) Then GoTo GetKeyError ' Handle Error...

tmpVal = String$(1024, 0) ' Allocate Variable Space

KeyValSize = 1024 ' Mark Variable Size

'------------------------------------------------------------

' Retrieve Registry Key Value...

'------------------------------------------------------------

rc = RegQueryValueEx(hKey, SubKeyRef, 0, _

KeyValType, tmpVal, KeyValSize) ' Get/Create Key Value

If (rc <> ERROR_SUCCESS) Then GoTo GetKeyError ' Handle Errors

If (Asc(Mid(tmpVal, KeyValSize, 1)) = 0) Then ' Win95 Adds Null Terminated String...

tmpVal = Left(tmpVal, KeyValSize - 1) ' Null Found, Extract From String

Else ' WinNT Does NOT Null Terminate String...

tmpVal = Left(tmpVal, KeyValSize) ' Null Not Found, Extract String Only

End If

'------------------------------------------------------------

' Determine Key Value Type For Conversion...

'------------------------------------------------------------

Select Case KeyValType ' Search Data Types...

Case REG_SZ ' String Registry Key Data Type

KeyVal = tmpVal ' Copy String Value

Case REG_DWORD ' Double Word Registry Key Data Type

For i = Len(tmpVal) To 1 Step -1 ' Convert Each Bit

KeyVal = KeyVal + Hex(Asc(Mid(tmpVal, i, 1))) ' Build Value Char. By Char.

Next

KeyVal = Format$("&h" + KeyVal) ' Convert Double Word To String

End Select

GetKeyValue = True ' Return Success

rc = RegCloseKey(hKey) ' Close Registry Key

Exit Function ' Exit

GetKeyError: ' Cleanup After An Error Has Occured...

KeyVal = "" ' Set Return Val To Empty String

GetKeyValue = False ' Return Failure

rc = RegCloseKey(hKey) ' Close Registry Key

End Function

Private Sub Form_Load()

Me.Top = 3000

Me.Left = (Screen.Width - Me.Width) / 2

End Sub

Coding of Module:-

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public view As Integer

Public fratepday As Integer

Public dayslimit As Integer

Public refcopy As Integer

Public maxhold As Integer

Public salnew As Integer

Public saltemp As Integer

Public salper As Integer

Public splashtime As Integer

Public welcometime As Integer

Public uname As String

Public Welcome As Boolean

Dim str As String

Dim modulers As ADODB.Recordset

Dim moduleconn As ADODB.Connection

Sub main()

Set moduleconn = New ADODB.Connection

moduleconn.CursorLocation = adUseClient

moduleconn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

Set modulers = New ADODB.Recordset

modulers.Open "select Dayslimit,Fratepday,Maxhold,Pass,Refcopy,Salnew,Salper,Saltemp,Spl

ashtime,Viewe,Welcometime,Welcome from Custom", moduleconn, adOpenStatic, adLockOptimistic

If modulers(3) = "administerpass" Then

Load Frm_admin

Frm_admin.Show

Else

Call globalload

End If

End Sub

Public Sub globalload()

view = modulers.Fields(9)

fratepday = modulers.Fields(1)

dayslimit = modulers.Fields(0)

refcopy = modulers.Fields(4)

maxhold = modulers.Fields(2)

salnew = modulers.Fields(5)

saltemp = modulers.Fields(7)

salper = modulers.Fields(6)

splashtime = modulers.Fields(8)

welcometime = modulers.Fields(10)

Welcome = modulers.Fields(11)

Adpass = modulers.Fields(3)

modulers.Close

Load frmSplash

frmSplash.Show

DoEvents

Sleep splashtime

Unload frmSplash

DoEvents

Load frmLogin

Load mdi_start

mdi_start.Show

frmLogin.Show

End Sub

Public Sub logoff()

Unload frmLogin

DoEvents

Set moduleconn = New ADODB.Connection

moduleconn.CursorLocation = adUseClient

moduleconn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"

Set modulers = New ADODB.Recordset

modulers.Open "select Dayslimit,Fratepday,Maxhold,Pass,Refcopy,Salnew,Salper,Saltemp,Splashtime,Viewe,Welcometime,Welcome from Custom", moduleconn, adOpenStatic, adLockOptimistic

view = modulers.Fields(9)

fratepday = modulers.Fields(1)

dayslimit = modulers.Fields(0)

refcopy = modulers.Fields(4)

maxhold = modulers.Fields(2)

salnew = modulers.Fields(5)

saltemp = modulers.Fields(7)

salper = modulers.Fields(6)

splashtime = modulers.Fields(8)

welcometime = modulers.Fields(10)

Welcome = modulers.Fields(11)

modulers.Close

Load frmLogin

Load mdi_start

DoEvents

mdi_start.Show

frmLogin.Show vbModal

End Sub

Books Form Individual Views:-

This form contains information about books. New book can be added here. Any book which is now not in this library can be deleted using this form. This form also has a detail view tab. If the user clicks this tab a list of all books available is shown. Constraints on some fields are provided so as to ensure data integrity.

Books Form Detail Views:-

Members Form Individual Views:-

This form contains information about members. New members can be addes here.Any person who wants to widraw the services provided can be deleted from records here.It contains fields like personal information, amount deposited, library information etc.Detaild view of this form shows the list of all members of the library.This form also has detail view tab.if the users clicks this tab a list of all the members is shown.Constraints on some fields are provided so as to ensure data integrity.

Members Form Details Views:-

IssueForm:-

Return Form:-

Fine Information Form:-

This all form help the user to enter information about the books issued, books returned &fine on any member.Issue foem &return form data can be added but fine information form showe the list of the members along with the fine.

Search Form:-

This form is used to search any information regarding any book or member according to given criteria.

Global Form:-

This form contains global information about books in the library.

Keybord Form:-

About Form:-

MDI Form:-

Reports:-

Data Report of Library Book List:

Data Report of Member List:

Data Report of Library Issue/Return Book List:

Splash Form:-

CONCLUSION

Computerization of the Library system will help the institution “GGNIVS” to maintain the database of Library easily. Because the Library system is fully computerized so there is no conflict. This will help the user to check net salary of any employee and their details anytime. There is full security of database. No unauthorized person can access the database.

So, the overall performance will be helpful to the institute.

BIBLIOGRAPHY

Books referred to make this software package:-

LEARN VISUAL BASIC 6.0 IN 21 DAYS

SOFTWARE ENGINEERING BY ROGER S PRESSMAN

SYSTEM ANALYSIS AND DESIGN BY ELIAS M AWAD

Submitted By:Name: Shaveta RaniRoll No: 62445061Course: B.SC [IT]LC Name: ABM INFOSYS

PROJECT REPORT OF

Submitted in partial fulfillment of the requirement ofThe degree

Bachelors of Science in Information TechnologyOf

PUNJAB TECHNICAL UNIVERSITYIN VISUAL BASIC (As Front End)

&MS-ACCESS (AS Back End)

Under the Guidance ofMr. Jayant Ahuja

Submitted by:SHAVETA RANISAPNA SHARMA

DEPARTMENT OF COMPUTER SCIENCEPUNJAB TECHNICAL UNIVERSITY

KITCHLU NAGARLUDHIANA