Quick Enquiry

Please type below

captcha

c
21 Apr 2016

Basic Concepts of ADO.NET in VB

In this post, we will discuss the basic VB Concepts,

Creating a new connection

Right what you need to do is first create a connection to the database, because your using Access you will use the OLEdb namespace. Your first line(s) should be your imports so type into the code editor for the start form…

Imports System.Data.OleDb

This will enable us to access all the functions for the OLEdb namespace in shorthand. To access the Access database you will first need to create a connection to it via the OLEdb provider this can be done with the following code which creates a connection based of you connection string.

Dim MyConnection As New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MYDB.mdb”)

MyConnection.Open

Of course your Connection string will differ depending on your Database location, UID and Password the above example only specify’s the database location via the ‘Data Source=’. The second line opens the connection to the database so we can insert, update, delete and read from the database.

Now depending on what you want to do with the database depends on the next set of commands.

Reading from a database using a SQL Command & a DataReader

To read from the database you have 2 options the Datareader and the Dataadapter. I’m not going into the Dataadapter here as for simple database applications or application that require speed you properly will not need it.

The DataReader is a Forward only Reader this means you can only go forward through returned data.

Commands

Anytime you wish to read, insert, update or delete information from the database you will need to ask the database before anything happens, this is an command.

Commands are just standard SQL (Standard Query Language) which enables you to do all the operations with the database as described above and guess what… I’m not going into any detail about SQL either sorry there are more than enough tutorials in other places, so search the net.

Now for inserting, Updating or Deleting.

Inserting

All the code is the same up until we create MyCommand. The Command will now hold the SQL for inserting a record.

When Updating, Inserting or Deleting the ONLY thing now needed to be changed is the SQL the syntax as you will see below.

As above in using the DataReader im just using the fields ‘Firstname’ & ‘Lastname’ in a table called MyTable. Now for the SQL

INSERT INTO MyTABLE (firstname, Lastname) VALUES (‘Joe’, ‘Bloggs’)

That’s it for the SQL, the first section in the indices are the fields I want to insert into, and the second section in indices are the values that I am inserting.

Note: ” in the value list of the SQL indicates that the values are Text if the values are numerical then ” would not apply in between each value.

Updating

UPDATE MyTable SET Firstname = ‘Bobby’ WHERE Lastname = ‘Bloggs’

Deleting

DELETE FROM MyTable WHERE Lastname = ‘Bloggs’

The SQL above will delete the record from MyTable where the lastname equals ‘Bloggs’. To do this just like the above examples all we need change is the SQL part of the command. Full code as below

Dim MyConnection As New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MYDB.mdb”)

MyConnection.Open

Dim MyCommand As New OleDbCommand(“DELETE FROM MyTable WHERE Lastname = ‘Duff'”, MyConnection)

MyCommand.ExecuteNonQuery()

MyConnection.Close

MyCommand.Dispose

Comments

Write a Comment

No comment posted.