Scribd
Upload
31 views4 pages

VB.NET Database Connection Guide

The document provides instructions for connecting to and manipulating data in a Microsoft Access database from Visual Basic .NET 2010. It explains how to create a connection, declare variables, add/update/delete records, search the database, and display records in a datagrid or listview. Key aspects covered include using OleDb classes like OleDbConnection and OleDbCommand, opening a connection, defining SQL queries, and executing queries to manipulate data.

Uploaded by

Mythes Jica
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
31 views4 pages

VB.NET Database Connection Guide

The document provides instructions for connecting to and manipulating data in a Microsoft Access database from Visual Basic .NET 2010. It explains how to create a connection, declare variables, add/update/delete records, search the database, and display records in a datagrid or listview. Key aspects covered include using OleDb classes like OleDbConnection and OleDbCommand, opening a connection, defining SQL queries, and executing queries to manipulate data.

Uploaded by

Mythes Jica
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

VISUAL BASIC .

NET 2010

CREATING A CONNECTION
1. Create your DATABASE first with the needed TABLES.
2. Create or Add a MODULE in Visual Basic .net.
3. Type this line of code to the upper most part of the code window of your Module.
Imports [Link]
Note: You also need to type this line of code in ALL OF YOUR FORMS.
4. Declare a variable that will store the database path for your connection.
Public con As OledbConnection = New OledbConnection("Connection String")

Note: CONNECTION STRING should be replaced by the actual connection string of your database.
Sumpay daw sa code: CONNECTION STRING: Provider = [Link].4.0;Data Source =
5. You might also need to declare the following variables for future use.
Public ds As New DataSet
Public cmd As OledbCommand
Public dr As OledbDataReader
Public da As OledbDataAdapter = New OledbDataAdapter
Public sqlSearch, sqlDelete, sqlInsert, sqlUpdate As String

DEFINITION OF TERMS:

o OledbConnection
The classes you can use to connect your application to a Microsoft Access database are stored in the
[Link] namespace.

To access the database, the first action you take consists of establishing a connection with the database. To
support this, the [Link] namespace provides the OleDbConnection class.

o ConnectionString - is used to specify how the connection would be performed. It is a string made of
different sections. Everything in this string is case-insensitive.

o DataSet
The DataSet can then be treated like a database in your program code.

A DataSet can contain all the basic elements of a database: tables, keys, indexes, and even relations
between tables. So by creating a DataSet, you'll be discovering the structure of a database at the same
time. The fundamental differences between a DataSet and a database are that a database generally resides
on a hard drive in one or more files and is usually larger. A DataSet usually holds a subset of the data in a
full database.

o OledbCommand
After establishing a connection to a database, you can perform actions or operations on it. To make this
possible and probably easier, the [Link] namespace provides the OleDbCommand class.

As its name suggests, this class is used to create commands or actions to be performed on the database.

o OledbDataReader
DataReader Object in [Link] is a stream-based, forward-only, read-only retrieval of query results from
the Data Source, which do not update the data. The DataReader cannot be created directly from code; they
created only by calling the ExecuteReader method of a Command Object.

DataReader Object provides a connection oriented data access to the data Sources.
o OledbDataAdapter
OleDbDataAdapter is a part of the [Link] Data Provider and it resides in the [Link]
namespace. OleDbDataAdapter provides the communication between the Dataset and the OleDb Data
Sources. We can use OleDbDataAdapter Object in combination with Dataset Object.

 Try Catch
The Try word means "Try to execute this code". The Catch word means "Catch any errors here". The ex is a
variable, and the type of variable it is is an Exception object.
For more details, check this site: [Link]

Sources: [Link]
[Link]

6. Create a function that will identify whether the connection is open or not.
Public Sub open_con()
If Not [Link] = [Link] Then
Try
[Link]()
Catch ex As Exception
MsgBox("Cannot find Server")
End Try
End If
End Sub
Note: All DECLARATIONS (variables and etc) and FUNCTIONS can be written in the MODULE.

ADDING A NEW RECORD TO THE DATABASE


1. On your ADD/SAVE button, type the following line of codes:
Sample Code:
open_con()
sqlInsert = "Insert into tblCategory Values('" & [Link] & "','" & [Link] &
"')"
cmd = New OledbCommand(sqlInsert, con)

Try
[Link]()
MsgBox("Successfully Saved!", [Link], "MESSAGE")
Catch ex As Exception
MsgBox([Link], [Link], "ERROR")
End Try

cmd = Nothing
sqlInsert = Nothing

[Link]()

2. Don’t forget to Type this line of code to the upper most part of the code window of your FORM.
Imports [Link]
UPDATING EXISTING RECORD IN THE DATABASE

1. On your EDIT/UPDATE button, type the following line of codes:


SAMPLE CODE:
open_con()
sqlUpdate = "Update tblcustomer set custname = '" & [Link] & "' where
customerid = " & [Link] & ""

cmd = New OledbCommand(sqlUpdate, con)

Try
[Link]()
MsgBox("Record successfully updated!", [Link], "MESSAGE")
Catch ex As Exception
MsgBox([Link], [Link], "ERROR")
End Try
cmd = Nothing
[Link]()

2. Don’t forget to Type this line of code to the upper most part of the code window of your FORM.
Imports [Link]

DELETING A RECORD IN THE DATABASE

1. On your DELETE button, type the following line of codes:


SAMPLE CODE:
open_con()
sqlDelete = "Delete from tblCustomer where customerid = " & [Link] & " "
cmd = New OledbCommand(sqlDelete, con)
Try
[Link]()
MsgBox("Successfully Deleted a record!", [Link], "MESSAGE")
Catch ex As Exception
MsgBox([Link], [Link], "ERROR")
End Try
cmd = Nothing
[Link]()

2. Don’t forget to Type this line of code to the upper most part of the code window of your FORM.
Imports [Link]

SEARCHING RECORDS IN THE DATABASE

1. On your SEARCH button, type the following line of codes:


SAMPLE CODE:
open_con ()
sqlsearch = "Select * from tbldestination where destinationID = "& [Link] &
""
cmd = New OledbCommand(sqlsearch, con)
dr= [Link]()
While [Link]
[Link] = dr("place")
[Link] = dr("destinationid")
End While

[Link]()
dr = Nothing
cmd = Nothing
sqlsearch = Nothing
[Link]()

2. Don’t forget to Type this line of code to the upper most part of the code window of your FORM.
Imports [Link]

DISPLAYING RECORDS IN A DATAGRID

SAMPLE CODE:
open_con()
da = New OledbDataAdapter("Select * FROM tblcustomer where customerid Like '" &
[Link] & "%'", con)
ds = New DataSet
[Link](ds, "tblcustomer")
[Link] = [Link]("tblcustomer")
ds = Nothing
da = Nothing
[Link]()

DISPLAYING ALL RECORDS IN A LISTVIEW


open_con ()
sqlsearch = "Select * from tblcustomers"
cmd = New OledbCommand(sqlsearch, con)
dr= [Link]()

While [Link]
[Link](dr("CustomerID"))
[Link]([Link] - 1).[Link](dr("Firstname"))
[Link]([Link] - 1).[Link](dr("MiddleName"))
[Link]([Link] - 1).[Link](dr("LastName"))
[Link]([Link] - 1).[Link](dr("Landline"))
[Link]([Link] - 1).[Link](dr("MobileNo"))
End While
[Link]()
dr = Nothing
cmd = Nothing
[Link]()

Common questions

Powered by AI

Closing database connections after operations is essential to release resources, prevent memory leaks, and avoid potential locking issues or connection pool exhaustion in VB .NET. If neglected, it could lead to degraded application performance and even system crashes due to resource exhaustion or deadlocks if the application has multiple open connections .

The OleDbDataAdapter acts as a bridge between a DataSet and a data source by facilitating the retrieval and manipulation of data. It populates a DataSet with data from a database and can also update the database with changes made in the DataSet. It is part of the ADO.NET Data Provider within the System.Data.OleDb namespace .

To search and retrieve records, the OleDbCommand executes an SQL Select query, and the OleDbDataReader, obtained via the ExecuteReader method of the command, reads the resulting data in a stream-based, forward-only manner. This allows for efficient retrieval of query results, which can then be used to populate UI elements such as text boxes or list views .

The OleDbConnection class, which resides in the System.Data.OleDb namespace, is essential for connecting an application to a Microsoft Access database. It requires the establishment of a connection using a connection string that specifies how the connection should be performed .

Writing 'Imports System.Data.Oledb' at the beginning of a form is necessary to gain access to the classes within the System.Data.OleDb namespace, such as OleDbConnection, OleDbCommand, and OleDbDataAdapter, which are required for database operations like establishing connections and executing commands .

To update a record safely using an OleDbCommand in VB .NET, first open the connection with open_con(), then construct the SQL update statement. Next, create an OleDbCommand with this statement and the connection. Use a Try-Catch block to execute cmd.ExecuteNonQuery(), which updates the record, and handle any exceptions. Finally, set cmd to Nothing and close the connection with con.Close().

To display records in a DataGrid, first open the connection with open_con(). Use an OleDbDataAdapter to specify an SQL Select command to fetch data. Fill the DataSet with data from the adapter, then bind the DataGrid's DataSource to the DataSet's table. This approach allows for a simplistic and effective display of data in UI components. Close the connection afterward to free resources .

The Try-Catch block in VB .NET is crucial for handling exceptions during database operations. The Try block attempts to execute code that might fail, such as opening a connection or executing a command. If an error occurs, the Catch block catches the exception, allowing for graceful error handling, such as displaying an error message and preventing the application from crashing .

To delete a record using VB .NET, first open the database connection with open_con(). Then, construct an SQL Delete command that specifies the criteria for the record to be deleted. Create an OleDbCommand with this command and execute it using cmd.ExecuteNonQuery() within a Try-Catch block to handle errors. Finally, set cmd to Nothing and close the connection with con.Close().

To display data in a ListView, open the connection with open_con(). Execute an SQL Select command using OleDbCommand and read data using OleDbDataReader obtained via ExecuteReader. Use the DataReader to iterate over data and add ListView items, filling sub-items with relevant database fields. This method allows for listing dataset records in a user-friendly format .

You might also like