How To: Connect, Read, Write, Close A Database, VB.NET

Write your "How To" regarding anything you are comfortable with and you feel it will help the forum members.

NOTE :: All threads started here will appear only after the approval from Administrator
Post Reply
anish
Posts: 353
Joined: Fri Apr 27, 2007 12:34 pm
Contact:

How To: Connect, Read, Write, Close A Database, VB.NET

Post by anish »

Here is a **** or a novice programmer. This tutorial assumes you have a basic knoladge of databases and VB.NET. It also assumes that you have a MS access database named Testdb.mdb with a table called "Table1" that has at least one column called "Col1". You must start with a form with one label on it.

The first step is connecting to the database.

The first thing you must do is add the ADODB reference. To do this in the "Project" menu **** "Add Reference" then select ADODB from the list and **** "Select".

The next thing you need is at least two variables. One for the connection and one for the record set. A record set is used to query the database. To make these variable add the next two lines at the top of the form.

Code: Select all

Dim Conn as new ADODB.Connection
Dim Rec as new ADODB.Recordset

Now that we have our variables we need to set-up the connection. In order to set up the connection you need a connection string. The one I use works for me but in order to meet your needs you may need to make your own. To set-up the connection string in the "FormLoad" event add this code.

Code: Select all

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=Testdb.mdb"
Great...we are ready to connect to the database. Add this line under the connection string set-up line.

Code: Select all

Conn.Open()
We now have a connection to the database. Now we need to find out how to get data from it. In order to do this you need to open a table in the database. To do this you must add these lines to your code.

Code: Select all

Rec.Open("SELECT * FROM Table1", Conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
Now that we have a table open we need to actually get data from it. In order to do this add the following code. This code goes through all the rows in the table and does what you want with the data.

Code: Select all

While not Rec.EOF
Label1.text = Label1.text & vbcrlf & Rec.Fields("Col1").value
Rec.MoveNext
End While
All this code does is take all the data from the column named "Col1" From "Table1" and put it all in Label1. Now that we have our data we need to edit the record and add a new one. To edit the current record use this code.

Code: Select all

Rec.Fields("Col1").value = "SomeValue"
Rec.Update()
Now we need to add a new record. Use this code to do that.

Code: Select all

Rec.AddNew()
Rec.Fields("Col1").Value = "SomeValue"
Rec.Update()
The code to add a record is very simialer except you need on more line of code. The Rec.AddNew(). All this does is add a new record to the database then selects it. Now that we are done playing around with the database we need to close it. Use this code.

Code: Select all

Rec.Close
Conn.Close
Thats it! You opened a database, Got information from the database, Changed and added some records, and closed the database.

Thanks,


SHAdmin
Posts: 2089
Joined: Sat Dec 18, 2004 11:28 am
Contact:

Post by SHAdmin »

Good job done on writing that how to. Your account has been credited 30 points for sharing it.
Post Reply