CICS 314: Advanced Visual Basic
.NET Programming
Data Access with ADO.NET
- Tutorial 3 -
Create Connections and Commands Using a Wizard for OLE DB and
SQL Data Sources
Ghana Communication Technology University
Lecturer – Dr. Forgor Lempogo
2021
Objectives
Applied
Use a data source to get the data that an application
requires.
Use a DataGridView control to present the data that’s
retrieved by a data source.
Use other controls like text boxes to present the data
that’s retrieved by a data source.
Write the Visual Basic code for handling any data errors
that result from the use of the data source or the controls
that are bound to it.
Use the Dataset Designer to
(1) view the schema for the dataset of a data source
(2) preview the data for a query
(3) review the SQL statements that are generated for a data source.
2
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Objectives (continued)
Knowledge
Describe the use of a connection string in an app.config file.
Describe the use of the Fill method of the TableAdapter object.
Describe the use of the UpdateAll method of the
TableAdapterManager object.
Describe the use of the EndEdit method of the BindingSource
object.
Describe the two categories of data errors that can occur when
you run an application that uses a data source.
In general terms, describe the way the SQL statements that are
generated for a data source
(1) prevent concurrency errors
(2) refresh a dataset when the database generates the keys for new
rows.
3
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Getting Started
Open Last weeks Windows application in
Visual Studio
Using the same three tables in the database
Add the windows forms
Design the user interfaces
Write the code
4
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
The User Interface
Add a new form to
your project and
give it an
appropriate name
(mine is
newForm5)
Add a button to
form1 to open the
newForm5 in the
click event
5 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Creating a Data Source (1/2)
After adding the form, the
next thing to do is to create
a connection to the
database using the
Connection wizard
The connection wizard can
be lunched from the Data
Sources Window
Do not use the Server
Explorer Window for this
purpose
To open the Data Source
Window, look on the top left
corner of visual studio
design window and click on
Data Sources.
6 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Creating a Data Source (2/2)
If you cannot find the
Data Sources Window
using the above process,
then do the following
Go to Menu and select
View.
From the drop-down,
select Other Windows.
From the list select Data
Sources
Menu->View->Other
Windows-> Data
Sources
7 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
The Data Sources Window
On the Data Sources
Window select “Add
New Data Source” to
lunch the Connection
Wizard
8 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Choose a Data Source Type Window
The first step of the
connection wizard is the
“Choose a Data Source
Type” Window.
Here we need to choose
which type of data source
we want to connect to.
Since we are trying to
connect to a database, we
will select Database from
the list.
Clicking on the Next button
will Lunch the “Choose
database model” Window.
9 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Choose a Database Model Window
The next step of the connection
wizard is the “Choose a
Database Model” Window.
Here we need to choose which
type of model we want to work
with.
In most of the versions the only
option available is Dataset, which
is what we need for our project.
Thus, After connecting to the
database, the data will be
extracted and stored in a dataset
on your computer.
Select Dataset and Click on the
Next button to open the Choose
your Data Connection Window.
10 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Choose your Data Connection Window
The next step of the
connection wizard is the
“Choose your Data
Connection” Window.
This is where the
Connection String is
actually created.
To get started, select
“New Connection” to
lunch the “Add
Connection” Window.
11 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
New Connection Window
The first step of setting
the connection string is
the “New Connection”
Window.
Here the database type
needs to be selected and
configured
To select the type of
database you want to
connect to, click on the
“Change” button to lunch
the “Change Data Source
Window
12 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Change Data Source Window
The Next step of setting the
connection string is the “Change Data
Source ” Window.
Here the database type needs to be
selected and configured
This is where there will be a difference
between OLEDB (MS Access) and MS
SQL Server Databases
Users of MS Access Database must
select “Microsoft Access Database
File” and Click on OK to proceed
Users of MS SQL Server Databases
must select “Microsoft SQL Server”
and select OK to proceed.
13 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Add Connection Window – OLEDB
For OLEDB Clicking of OK on the Previous
“Change Data Source ” Window will take
you back to the same “Add Connection”
Window in slide 12
On the “Add Connection” window click on
the Browse Button and browse to where
your Access database file is saved on you
computer
Select your MS Access Database file and
click on Open to return to the Add
Connection Window
If your access database file has a user
name and password, you will need to enter
them into the user name and password
fields in the Add Connection Window.
Next select Test Connection to be sure
everything is correctly set up.
If the connection is correctly setup a
success message will be shown, else an
error message will be shown.
14 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Add Connection Window – OLEDB
Next select Test Connection
to be sure everything is
correctly set up.
If the connection is correctly
setup a success message
will be shown, else an error
message will be shown.
If an error message is
shown, then you need to
repeat the process and be
sure the user name and
passwords, if any, are
correct
15 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Change Data Source - MS SQL SERVER
This is where there will be a
difference between OLEDB
(MS Access) and MS SQL
Server Databases
Users of MS Access
Database must select
“Microsoft Access
Database File” and Click
on OK to proceed
Users of MS SQL Server
Databases must select
“Microsoft SQL Server”
and select OK to proceed.
16 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Add Connection – MS SQL SERVER
For MS SQL Server Database Clicking on OK on the
Previous “Change Data Source ” Window will take you to
the following “Add Connection” Window
On the “Add Connection” window click on “Refresh” button
by the Server Name Combobox to load all SQL Servers
running on your system.
Next Select the appropriate Server name from the
Combobox.
If after refreshing the server name Combobox is still empty,
then you will have to manually enter your server name in
that Combobox.
Next is to indicate the user name and passwords.
Depending on what you indicated during setup of your
server. But in most cases students use Windows
authentication to login
Entering the server name and clicking on refresh will load all
available databases residing on the server into the “Select
or Enter Database name” Combobox.
If the database name Combobox is still empty after the
refresh, then you will have to manually enter your database
name into there dropdown.
Next select Test Connection to be sure everything is
correctly set up.
If the connection is correctly setup a success message will
be shown, else an error message will be shown.
17 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Add Connection – MS SQL SERVER
Next select Test Connection to be
sure everything is correctly set up.
If the connection is correctly setup
a success message will be
shown, else an error message will
be shown.
If an error message is shown,
then you need to repeat the
process and be sure the user
name and passwords, if any, are
correct
Click ok on the success message
to return to the Add Connection
Window
Click Ok on the Add Connection
window to return to the “Choose
your Data Connection” Window
18 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
View your Connection String
The connection string is
ready.
To view the connection
string, click on the plus
sign on the mid-left side
of the “Choose your
Data Connection”
Window
Click on the Next button
on the “Choose your
Data Connection”
Window to proceed.
19 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
View your Connection String
Clicking the Next button
on the “Choose your
Data Connection”
Window will generate the
following dialog.
Asking whether or not
you wish to copy your
access database file into Yes No Cancel
the debug folder.
Select Yes and proceed
to the “Save Connection
String” Window
20 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Save Connection String Window
Selecting Yes from the
dialog will open the “Save
Connection String” Window
Visual studio has already
suggested a name for the
connection string. You may
change it to any valid
identifier or leave it as it is
Clicking on next will save
the connection string with
the above identifier into the
Application Configuration
file (App.Config) and Open
the “Choose your Data
Objects” Window
21 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Choose your Data Objects Window
On the “Choose your Data
Objects” window you are asked to
select which tables and views you
want to include in the dataset.
Here you can select specific
tables, or specific columns of
specific tables or specific views
etc.
Make sure all the three tables in
your database are selected before
clicking on Finish
This is the last step of the
connection setup.
After selecting the tables and
columns you wish to add to your
dataset, Click on Finish to Exit the
Connection Wizard.
22 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
The Data Source Window
After exiting the
Connection wizard, the
Data Sources Window
will now contain your
database and the
tables you selected
Note that the tables will
only contain the
columns you have
selected.
.
23 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
The Solution Explorer
After exiting the
Connection wizard, The database
some new files will be file / data
source
added to the Project The
and will show up on the Application
configuration
solutions explorer, file
Namely:
The dataset file
The data source file
The app.config file The dataset
24 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
The App. Configuration File – SQL Server
The information that’s stored in the app.config
file
<connectionStrings>
<add name=
" AdvanceVBNET_Totorial.My.MySettings.Contact_DBConnectionString"
connectionString="Data Source=localhost\sqlexpress;
Initial Catalog=Contact_DB;
Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
25
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
The App. Configuration File – OLEDB
The information that’s stored in the app.config
file
<connectionStrings>
<add
name="AdvanceVBNET_Totorial.My.MySettings.Contact_DBConnectionSt
ring"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=|DataDirectory|\Contact_DB.mdb"
providerName="System.Data.OleDb" />
</connectionStrings>
26
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Handling Columns With Default Values
If a column in a database has a default value,
that value is not included in the column
definition in the dataset. Eg. Auto number fields
You can omit columns with default values
unless they are needed by the application.
If you include a column that is defined with a
default value, you must provide a value for that
column whenever a row is added to the
dataset.
27
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Creating the User Interface
Open newForm5
From the Data Sources window drag-and-drop the
Person_TB table onto newForm5
Expand the form and DataGridView to get a good design
28
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Auto-Added Controls
After drag-and-droping the table on the form different
controls were automatically added to the form
The controls that are created when you drag a data source
onto a form are:
Control Description
DataGridView control Displays the data from the data
source in a grid.
BindingNavigator Defines the toolbar that can be used
control to navigate, add, update, and delete
rows in the DataGridView control.
29
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Auto-Added Controls
The objects that are created when you drag a data source
to a form
Object Description
BindingSource Identifies the data source that the controls on
the form are bound to and provides
functionality for working with the data source.
DataSet Provides access to all of the tables, views,
stored procedures, and functions that are
available to the project.
TableAdapter Provides the commands that read and write
data to and from a table.
TableAdapter Maintains referential integrity.
Manager
30
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Testing the Application
31
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
BindingNavigator - The ToolStrip
To First Total To Last
Delete
Record To records Record
Record
Previous
Record
To Next Add Save
Record Record
32
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Possible OLEDB Issues
Data NOT Saved Problem
Select your DataBase File from
the solutions explorer
Go to the properties window
and Select COPY TO OUTPUT
DIRECTORY
Set the Value to COPY IF
NEWER
33
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Data NOT Saved Problem
The Copy To Output Directory has three possible values
Value Description
Do Not Copy Never Copies the Database file to
the project directory (Copying can be
done manually)
Copy Always Copies the database file to the
project directory every time the
program is run
Copy If Newer Copies the database file to the
project directory only if the database
Schema has changed
34
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Auto-Generated Code
The code that is generated by Visual Studio
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the
''PayablesDataSet.Terms' table. You can move,
'or remove it, as needed.
Me.TermsTableAdapter.Fill(Me.PayablesDataSet.Terms)
End Sub
Private Sub TermsBindingNavigatorSaveItem_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles TermsBindingNavigatorSaveItem.Click
Me.Validate()
Me.TermsBindingSource.EndEdit()
Me.TermsTableAdapter.Update(Me.PayablesDataSet.Terms)
End Sub
35
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Auto-Generated Codes
The syntax of the Fill method
TableAdapter.Fill(DataSet.TableName)
The syntax of the Update method
TableAdapter.Update(DataSet.TableName)
36
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Changing the Default Control (1/2)
By default when tables are dragged onto a
form the DataGridView Control is added to
display the data.
But this can be changed to make other
controls such as textboxes, labels,
comboboxes, etc. display various fields of
our tables.
To change the default controls of a table do
the following:
Open the Data sources window and select
the Table whose controls you wish to
change
Click on the arrow to the right of the table
and select Details
This will change the controls from
Datagridview to the appropriate default
controls for the specific data types.
Eg. For a column with a text data type, a textbox
will be used,
for a column with Date datatype Datetimepicker
will be used, etc.
38
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Changing the Default Control (2/2)
To change the default controls of each
field/Column do the following:
Open the Data sources window and
select the Table whose controls you wish
to change
Click on the arrow to the right of the table
and select Details
This will change the controls from
Datagridview to the appropriate default
controls for the specific data types.
Eg. For a column with a text data type, a
textbox will be used,
for a column with Date datatype
Datetimepicker will be used, etc.
Go through the attributes of the table and
define a control for each of them by
clicking on the arrow to the right of the
attribute and selecting the appropriate
control
39
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Creating the User Interface
Add a new form to
your project and
give it an
appropriate name
(mine is
newForm6)
Add a button to
form1 to open the
newForm6 in the
click event
40 CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Creating the User Interface
Open newForm6
Open the Data sources window and change the default view of Person_TB from
DataGridView to Detail
Go through the attributes of the Person_TB table and change them to appropriate
controls
From the Data Sources window drag-and-drop the Person_TB table onto newForm6
Expand the form and reorganize the controls to get a good design
41
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Test The Application
42
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Code for the Application
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the
''PayablesDataSet.Vendors' table.
'You can move, or remove it, as needed.
Me.VendorsTableAdapter.Fill(
Me.PayablesDataSet.Vendors)
End Sub
Private Sub VendorsBindingNavigatorSaveItem_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles VendorsBindingNavigatorSaveItem.Click
Me.Validate()
Me.VendorsBindingSource.EndEdit()
Me.VendorsTableAdapter.Update( _
Me.PayablesDataSet.Vendors)
End Sub
43End Class
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Code for the Application
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the
''PayablesDataSet.Vendors' table.
'You can move, or remove it, as needed.
Me.VendorsTableAdapter.Fill(
Me.PayablesDataSet.Vendors)
End Sub
Private Sub VendorsBindingNavigatorSaveItem_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles VendorsBindingNavigatorSaveItem.Click
Me.Validate()
Me.VendorsBindingSource.EndEdit()
Me.VendorsTableAdapter.Update( _
Me.PayablesDataSet.Vendors)
End Sub
44End Class
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
Questions?
45
CICS 314: Advanced Visual Basic .NET Programming - GTUC 2021 Delivery
