Transcript Interacting with a Database Using VB: 2006 Training

Developing Visual Basic Applications
to Interact with an Access Database
Training Session
Brian R. Kovar
Kansas State University
8th AIS Educator Annual Meeting
June 23, 2006
Concepts Covered in this session
• Programming
–
–
–
–
–
–
–
–
–
How to link a VB.NET program to a database
Basic SQL
How to bind controls/objects to a dataset
How to create message boxes
IF….Then….Else Selection Statements
Variable Declaration
Object-Oriented Programming
How to create message boxes
Visual Basic programming environment
• The idea of “program-data independence”.
• Many other programming and information systems concepts will
be covered as well
Programming helps build accounting competencies
• Programming helps develop and improve problem solving
and critical thinking skills.
– Increased ability to handle more complex accounting issues and
problems (Beard and Smith 2002)
• AICPA core competency: ability to leverage technology
(2004)
• The lack of programming skills may handicap accountants
in working effectively as IS auditors (Calderon, Cheh, Chatham
2002)
• The Information Systems Audit and Control Association
(ISACA) has developed curriculum models stressing the
need and importance of computer programming skills.
Tutorials Developed for Classroom Use
• Creating and Coding an Expert System Using Visual Basic.NET
– Published in the Winter 2005 issue of The Review of Business Information
Systems. Received a Best Paper Award at the 2004 AIS Educator Conference.
• Interacting with a Database Using Visual Basic.NET
– Presented at the 2005 AIS Educator Conference
– Recently accepted for publication in the 2006 C3 project (A Compendium of
Classroom Cases and Tools) sponsored by the Information Systems section of
the American Accounting Association.
• Designed to give students an introductory look into how computer
programs are created using a computer programming language
(VB.NET), as well as give students some basic exposure to the
concepts and skills needed to create computer programs.
• Step-by-step tutorials where students:
– Create an expert system (Loan Evaluation ES)
– Create programs capable of interacting with an Access database (simple data
retrieval and display, as well as SQL queries involving one table and multiple
tables)
Start Button
1
2
4
View Code:
click to view the
code on a form
The Toolbox.
The hammer and
wrench button (that you
might see somewhere on your
screen) is the Toolbox
button, and clicking it
causes the Toolbox to
appear and/or disappear.
View Designer:
click to view
GUI of the form
3
Interacting with a Database Using
Visual Basic.NET
• VB.NET uses ADO.NET (ActiveX Data Objects.NET)
to read and write information to a database.
– A copy of the records and fields that are needed (called
the dataset) is stored in the computer’s memory.
– The connection to the database is closed, while the
dataset (in memory) is manipulated.
– The application (VB.NET program) reconnects to the
original database when changes to the dataset need to be
saved.
The Data
Source
(Database)
Data Adapter
DataSet
(acts as an
intermediary
between a dataset
and the database)
(stored in
computer’s
memory)
VB.NET Application
(user interface and
program code)
Making a VB.NET application interact
with a database
1. Create a connection to a data source (a specific
database file and its tables)
2. A Data Adapter must be created to facilitate
information transfer back and forth between the
data source and the computer program.
3. A Data Set (internal memory data holding area) must be
created to hold the data as the application is
working with it.
• Supports the idea of program/data independence
The Data
Source
(Database)
Data Adapter
DataSet
(acts as an
intermediary
between a dataset
and the database)
(stored in
computer’s
memory)
VB.NET Application
(user interface and
program code)
SQL (Structured Query Language)
• Pronounced as both SQL and sequel
• A set of commands used to access and manipulate
the data stored in a DBMS. Can be used to store,
retrieve, update, delete & sort data.
• SELECT fields FROM table [WHERE condition] [ORDER BY field]
• SELECT specifies the fields that should be displayed.
• FROM specifies the table or tables the selected fields are
taken from.
• WHERE is used to limit the records to only those meeting
specified criteria.
• ORDER BY is used to sort the displayed records.
Database Methods (Actions)
• Dataset = an object representing the data you want
to access.
–
–
–
–
Clear removes all rows in all tables in a dataset.
Fill refreshes/adds data from the source (data source)
Count = the number of items in something (the dataset)
Position = current location in something (the dataset)
• Binding context is an “object” used to keep track of all
datasets. Each dataset can be identified and manipulated
while the program is running using its binding context and
its parameters (name and names of tables being accessed)
• SelectCommand.Parameters(“State”).Value=State
is used to modify the filter (subset of records from
the original set) established by the WHERE SQL
command.
– WHERE (State = ?)
Using my Interacting with a Database Using
VB.NET tutorial in the classroom
• Can be used as part of an in-class unit, or it can be used as a
stand-alone, self-paced unit.
• Has been used at both the undergraduate and graduate
levels.
– Students typically report that the tutorial takes 5-6 hours to
complete
• Must be completed in a computer lab or on a computer that
already has Visual Studio/Visual Basic.NET installed.
– Software can be obtained for a yearly fee from the Microsoft
Software Developer Network Academic Alliance (your college or
department may already be a member)
Concepts Covered in this session
• Programming
–
–
–
–
–
–
–
–
–
How to link a VB.NET program to a database
Basic SQL
How to bind controls/objects to a dataset
How to create message boxes
IF….Then….Else Selection Statements
Variable Declaration
Object-Oriented Programming
How to create message boxes
Visual Basic programming environment
• The idea of “program-data independence”.
• Many other programming and information systems concepts
were covered as well