I have created a phone book which you can use to store all your contacts and their numbers. I will modify it to make it store email addresses and other type of information.
The code is given here: Phone Book
To create this phone book, you will need MS Access. Follow the steps:
- Open Ms Access
- Create a new database and name the first field as “FirstName”, second as “Surname”, third as “PhoneNumber”
- Go to file menu and then click on save and publish.
- Click save database as Access 2002-03 and click save as
- Now create a new project in VS2010 and put this database in the bin folder of the project
- Now create a form which looks like this:
- Now, enter the below code in the load event of your form. For reference, here is the complete code which you can refer to, if I fail to explain something well. Phone Book
- Now, we have to connect to our database. We will need a connection object. We will use the connection provider “JET” to provide us with connections to the database. Declare the connection object as:
Dim con as New oleDb.OleDbConnection
Now we need to pass two things to our object, the technology which we will use to connect it and the address of the database.
So, add these two variables to your form, dbProvider and dbSource as Strings. Initialize them as follows:
dbProvider=”PROVIDER=Microsoft.Jet.OLEDB.4.0;” //Note the semicolon
dbSource=”DATA SOURCE=Phone Book.mdb” //enter the address of wherever you have stored the database
Now we need to pass this to the object so enter this sentence:
con.ConnectionString=dbProvider & dbSource
Now, we have to open the connection to the database and set up a query for its contents. For this, we use
and to close it, we use
- Now, there is a concept called data set and data adapter.
VB.NET uses something called Data Set and Data Adapter.
Data Set: It is hidden from you. It holds a copy of the database which you have connected to.
Data Adapter: The connection object and the data set cannot communicate directly with each other so they need a go-between. This is the Data Adapter.
The data set and data adapter are then objects which you declare like this(declare everything at the top of your form):
Dim ds as New DataSet Dim da as OleDb.OleDbDataAdapter da=New OleDb.OleDbDataAdapter("SELECT * FROM tblNumbers",con)
tblNumbers is the name of the table in the database. You should change it to whatever name you have given your table in the .mdb database.
Now, the first two lines are declarations of objects. What is the third line? It passes an SQL(Structured Query Language) query which gives you the whole database. I’ll explain the SQL part in a later tutorial. For now, just remember that this query gives you the whole database.
The connection object is also passed which then creates a go-between data adapter.
- Now, you have to fill the data set with the database. For this, you enter:
Add this line after the creation of the data adapter. That is, after: da=New OleDb.OleDbDataAdapter(“SELECT * FROM tblNumbers”,con)
This line fills the data set with the database in the data adapter and then names it as “Address Book”
- To refer to any item in the database, you can use:
ds.Tables (“Address Book”).Rows(0).Item(1)
Rows refer to row number.
Item refer to column number.
- This is the basics you need to know about Database Programming.
One more thing remains and that is, how to add a new record to your Database.
For this, you need another Command Builder.
dim cb as oleDb.OleDbCommandBuilder(da) Dim dsNewRow as Data Row dsNewRow=dsTables("Address Book").NewRow() dsNewRow.Item("FirstName")=txtFirstName.text ds.Tables("Address Book").Rows.Add(new dsNewRow) da.update(ds,"Address Book")
- The first line in the above code creates a new command builder
The next line is used to add a new row to your database.
The third line is used to create a new data row object.
And the actual values for the new row are taken from the text boxes.
And the final line Updates the database with the information that the user has given you.
So this is the tutorial. Once again the whole code is given here: Phone Book
I think you should be able to follow the whole code if you have understood what I have tried to explain.
If you have any doubts, feel free to comment.