To introduce the connection string to communicate between your
application and the database (Microsoft Access, MySQL, MSSQL, Oracle), you need
to know more details what is ADO.NET
and its functions to understand well on its uses. Now, it’s time to work with
Visual Basic.Net to build our simple application to communicate with the
database that enables performs queries to retrieve, insert, update, and delete data
from database. In this tutorial, we’ll stick to work with Microsoft Access Database but if you want to work with a large
amount data to store in your database it’s better to use SQL Server, Oracle, MySQL,
or other DBMS that supports large amount of storage because Database Access has
a limited features over the others. Let’s work with OLE DB connection object, OLE stands for Object Linking and
Embedding, and it’s basically a lot of objects (COM objects) bundled together
that allow you to connect to data sources in general. There are a number of
different OLE DB objects (called data providers), but the one we'll use is
called "Jet".
Imports System.Data.OleDb namespace needs to be import first just
above the Public Class
Form1 to use all of this components such
as Connection,
Command, DataAdapter, and DataReader.
We need to pass two things to our new Connection Object:
the technology we want to use to do the connecting to our database; and where
the database is. (If your database was password and user name protected, you
would add these two parameters as well. Ours isn't, so we only need the two.)
The technology is called the Provider; and you use Data
Source to specify where your database is. So, this is the code:
connString = "Provider=Microsoft.JET.OLEDB.4.0;
Data Source = D:\\OledbCon\DbInfo.mdb"
The first part specifies which provider technology we want to use
to do the connecting (JET). The
second part, typed after a semi-colon, points to where the database is. In the
above code, the database is on the D Drive,
in the root folder OledbCon. The
name of the Access file we want to connect to is called DbInfo.mdb. Remember "Data Source" is two words, and not
one. Note, make sure your file extension is .mdb not .accdb because it doesn’t work for it.
connString is a String variable that holds your
Connection Object
conn.ConnectionString = connString
conn.Open()
ConnectionString
is a property of the con variable. The con variable holds our Connection
Object. We're passing the Connection String the name of a data Provider, and a
path to the database.
Open method of
the Connection Object, once open the connection has to be closed again, just
use Close method conn.Close()
Your code
looks like the following:
#Region "Method name for
connection string"
Public Sub myConnectionString()
connString = "Provider=Microsoft.JET.OLEDB.4.0;
Data Source = D:\\OledbCon\DbInfo.mdb"
conn.ConnectionString = connString
conn.Open()
End Sub
#End Region
We’ll use
Region to specify the name of my method myConnectionString,
so that it easy to connect every time to open my database to performs
queries. However, you can do a lot more of this approach, you may also use
module or class to enable access globally. (The purpose is you don’t need to
type again every time to connect from your database.)
Dim connString
As String
Dim conn As New OleDbConnection
Dim da As OleDbDataAdapter
Dim cmd As New OleDbCommand
Dim ds As New DataSet
Dim sql As String
The above
variables are needed to the whole of this program. Declare this variable as
public under Public Class Form1 to enabled
access other controls within the class.
To ensure
you’re successfully connected to your database, in your button connect, the code
looks like the following.
Call myConnectionString()
MessageBox.Show("Successfully
connected to the database", "Connected",
MessageBoxButtons.OK, MessageBoxIcon.Information)
conn.Close()
MessageBox.Show("Database
closed", "Closed", MessageBoxButtons.OK, MessageBoxIcon.Information)
To connect from your database, you
need to Call myConnectionString method followed by
message box result if it is connected successfully or not.
Understanding
what is SQL Statement
SQL
(pronounced
es-kyoo-el) is
not an acronym. It’s a common misconception that
SQL stands for structured query language; it stands for
S–Q–L and nothing else. Why? Because ANSI says, the official name is Database
Language SQL, isn’t structured (because it can’t be broken down into blocks or
procedures), isn’t for only queries (because it has more than just the SELECT statement),
and isn’t a language (because it’s not Turing complete, which you’ll study
should you take Theory of Computation.) SQL it is not case sensitive. reference ~Third Edition Chris
Fehily~
SQL statement are divided into
Three Categories
Data manipulation language (DML) statements retrieve, reckon, insert, edit, and delete data stored in a
database.
Data definition language (DDL) statements create, modify, and destroy database objects such as
tables, indexes, and views.
Data control language (DCL) statements authorize certain users to view, change, or delete
data and database objects.
For
more information about SQL queries and its uses, I’ve suggest to read more in
details. Unfortunately, I only stated important keyword what is SQL stands for
in this article. However, we need only used SELECT, INSERT, DELETE, and UPDATE
statement that meet our needs for our application in Visual Basic.Net. SQL
queries and other components both were apply to communicate between your
application and the database. To do so:
SELECT
* FROM Table_Name
Asterisk
(*) means all fields or columns are
displays in your query result using your SELECT Statement. If you are going to
specify the name of each columns, instead of asterisk (*), write the column
name you want to display on your result separated by a comma.
SELECT
col1, col2, coln…
FROM Table_Name
FROM
refers from which table name in your database to display the result, and
followed by the name of your Table.
Here’s your code looks like:
Private Sub retrieveData()
'I've created this method so that you don't need to type
'this query every time you retrieve your data from
database,
'instead call it
if needed.
Call myConnectionString()
sql =
"SELECT * FROM tblInfo"
da = New OleDbDataAdapter(sql,
conn)
da.Fill(ds, "DbInfo")
DataGridView1.DataSource = ds.Tables("DbInfo")
conn.Close()
End Sub
tblInfo is the name of the Table and DbInfo
is the name of your database where your table is already belong.
sql is a
variable name that holds your query statement, da stands for DataAdapter also a variable and conn is your connection variable for an instance object to
communicate between your database, and the DataAdapter can Fill a DataSet (ds) with records from a
Table. Lastly, it will close the connection between the application and your
database.
To display the result, you need DataGridView to fill with records from your table. So, place the
code in your Form Load event so that once the program
is run it will automatically retrieve the data from your database.
Private Sub Form1_Load(sender
As System.Object,
e As System.EventArgs)
Handles MyBase.Load
'call method name retrieveData to fill the DataGridView
Call retrieveData()
End Sub
We already did to retrieve data from database. Now, it’s time to
add/insert new entry to your
database. Your code looks like the following:
Private Sub
btnSave_Click(sender As System.Object, e As
System.EventArgs) Handles
btnSave.Click
Call myConnectionString() 'Enabled
communicate to database
sql =
"INSERT INTO
tblInfo(Firstname,Lastname,Middlename) VALUES ('" &
txtLastname.Text & _
"','" & txtFirstname.Text & "','" & cbMiddlename.Text & "')"
cmd =
New OleDbCommand(sql,
conn)
cmd.ExecuteNonQuery()
MessageBox.Show("Successfully
Added to your Database", "New
Record Added", MessageBoxButtons.OK,
MessageBoxIcon.Information)
conn.Close()
Me.Close()
End Sub
The
above code runs smoothly to insert record directly to your database. You used INSERT INTO statement to execute insert command.
INSERT INTO
Table_Name (col1, col2, coln…) VALUES (value1, value2, valuen…)
ExecuteNotQuery
allows you to execute a SQL statement or command object with CommandText property having a SQL
statement without using a DataSet.
Here are other options how to
INSERT record.
#Region "CommandText
Insert Command"
'You may also used this approach to insert record.
'If you want to try to run this code, call this procedure
'and comment the code you already made.
Private Sub
CmdTextInsert()
cmd =
New OleDbCommand("tblInfo", conn)
cmd.CommandText = "INSERT INTO
tblInfo(Firstname,Lastname,Middlename) VALUES ('" &
txtlastname.Text & _
"','" & txtfirstname.Text & "','" & txtmiddlename.Text & "')"
cmd.ExecuteNonQuery()
MsgBox("Successfully Added to your Database", "New Record(s) Added", MsgBoxStyle.Information)
conn.Close()
End Sub
#End Region
#Region "Other Insert
Command"
Private Sub
OtherInsert()
sql =
"INSERT INTO tblInfo(Firstname,Lastname,Middlename)
VALUES ('" & txtlastname.Text & _
"','" & txtfirstname.Text & "','" & txtmiddlename.Text & "')"
Dim da As New OleDbDataAdapter(sql,
conn)
da.Fill(ds)
End Sub
#End Region
You
can choose which INSERT command you’re really compatible to use.
Next,
need to modify your record if in case there’s record inserted mistaken? To do
so, to modify the entry in DataGridView, the users can double click which entry in row they are going to modify. I used
this code in my previous tutorial Barcode product entry system used by a cashier, so this click event
include DOUBLE click in order to
make changes the entry.
Private Sub
DataGridView1_CellMouseDoubleClick(sender As Object, e As
System.Windows.Forms.DataGridViewCellMouseEventArgs)
Handles DataGridView1.CellMouseDoubleClick
Dim x As Integer
If Not
DataGridView1.CurrentRow.IsNewRow Then
x
= DataGridView1.Rows.IndexOf(DataGridView1.CurrentRow)
txtFirstname.Text = DataGridView1.Rows(x).Cells(1).Value.ToString()
txtLastname.Text = DataGridView1.Rows(x).Cells(2).Value.ToString()
cbMiddlename.Text = DataGridView1.Rows(x).Cells(3).Value.ToString()
End If
End Sub
Once
the user double click the cell, the current data selected in DataGridView
displays in TextBox. That’s the time
you can make changes the entry. Here’s the code:
Private Sub
btnUpdate_Click(sender As System.Object, e As
System.EventArgs) Handles
btnUpdate.Click
Call myConnectionString()
Dim x As Integer
Dim id As Integer
If Not
DataGridView1.CurrentRow.IsNewRow Then
x
= DataGridView1.Rows.IndexOf(DataGridView1.CurrentRow)
id = DataGridView1.Rows(x).Cells(0).Value.ToString()
sql = "UPDATE tblInfo SET Firstname =
'" & txtFirstname.Text & "',Lastname
= '" &
txtLastname.Text & "',Middlename =
'" & cbMiddlename.Text & "'
WHERE ID = " & id & ""
Dim da As New OleDbDataAdapter(sql,
conn)
da.Fill(ds)
MessageBox.Show("Data
successfully updated", "Update",
MessageBoxButtons.OK, MessageBoxIcon.Information)
conn.Close()
Me.Close()
End If
End Sub
In
update, you used UPDATE statement to
enable modification of entry.
UPDATE
Table_Name SET col1, col2, coln… WHERE col = col
UPDATE,
SET, and WHERE Keywords in SQL that has a special meaning. WHERE clause is every important to
emphasize which column match unique ID
so that once it update, only one entry in row will affected because once you
didn’t specify the WHERE clause properly there’s some tendency all records in
table will change in one updates. SET
simply set which column(s) in table to modify.
Lastly,
it enables to DELETE if you don’t
satisfy what entry was inserted. Here’s the code:
Private Sub
btndelete_Click(sender As System.Object, e As
System.EventArgs) Handles
btndelete.Click
Call myConnectionString() 'Connect
to database first
Dim x As Integer
Dim id As Integer
Dim firstname As String
Dim lastname As String
If Not
DataGridView1.CurrentRow.IsNewRow Then
x
= DataGridView1.Rows.IndexOf(DataGridView1.CurrentRow)
id = DataGridView1.Rows(x).Cells(0).Value.ToString()
firstname = DataGridView1.Rows(x).Cells(1).Value.ToString()
lastname = DataGridView1.Rows(x).Cells(2).Value.ToString()
If MessageBox.Show("Are you sure you want to delete " &
firstname & " " & lastname
& " entry", "Delete Entry" _
, MessageBoxButtons.YesNo, MessageBoxIcon.Question)
= Windows.Forms.DialogResult.Yes Then
sql = "DELETE FROM tblInfo WHERE ID =
" & id
Dim da As
New OleDbDataAdapter(sql,
conn)
da.Fill(ds)
MessageBox.Show("Record successfully deleted", "Confirmed", MessageBoxButtons.OK)
conn.Close()
Me.Close()
Else
MessageBox.Show("Cancelled Operation", "Cancelled", MessageBoxButtons.OK)
conn.Close()
End If
End If
End Sub
To
DELETE entry, also the same as UPDATE statement because you’re specifying a
WHERE clause statement which ID to be deleted. Why ID? Because that’s the only
fields provide the uniqueness of entry in a table. If you are going to put
unique ID in your table it must be a PRIMARY
Key and Data Type AutoNumber for Access, so it will
automatically generated every time new entry is added, and ID is your identity
to identify which entry to be update or delete in your table.
