Tips when connecting VB.NET Application to a Database

This article will require some programming knowledge and some database knowledge. It is intended for someone beginning to work with VB.NET and databases. It discusses a simple way to connect your application to a database and do some queries using Visual Basic.net. Visual Basic .NET (VB.NET), is and object-oriented computer programming language that is viewed as an progression of the vintage Visual Basic (VB), that is implemented within the .NET Framework. Microsoft currently supplies two main versions of IDEs intended for developing in Visual Basic: Microsoft Visual Studio , which is professional software and Visual Basic Express Edition, which is no cost. The command-line compiler, VBC.EXE, is installed as part of the free software application .NET Framework SDK.

In this tutorial we will also use Microsoft Access as the database that we will connect to. Each database has a connection-string which is needed to connect and read the information from that database.

We will show an example code on connecting to a database:

The first thing you will need to do is create a access database, lets call it student.mdb. In the database, you will have a table called students with 7 fields.

student.mdb
student_id - (numeric, auto-number)
last_name - (text, 20)
first_name - (text, 20)
age - (numeric)
city - (text, 20)
state - (text, 2)
zip - (text, 10)

Lets say you input some data:


the student_id field must be a primary-key field and auto-numbered. At least one field in each table should be auto-numbered to uniquely identify the record. If you do not do this, then you will have problems inserting, updating and deleting records in VB.net. You can also use a unique text field instead of an ID field but keep in mind each record must have a unique value. let say the database is on the root of you hard drive C:\student.mdb.

Now create a new VB.NET project and add a button and a datagrid (see image):


Double-Click the button and add the following code to button:


Here is the code, you can simply cut and paste:
Dim da As New OleDb.OleDbDataAdapter("", "") Dim dt As New DataTable Dim conn As String Dim sql As String conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\student.mdb" da.SelectCommand.Connection.ConnectionString = conn da.SelectCommand.CommandText = "select * from students" da.Fill(dt) Me.DataGrid1.DataSource = dt Me.DataGrid1.Refresh()

Now, once we are done, you can compile the application and run it, the output should look like this:


This example shows the data from the database and the code needed to connect the 2. If you are using a SQL server, you will use: SqlClient.SqlDataAdapter("", "") instead of OleDb.OleDbDataAdapter. Each database has its own adapter. You can also use Odbc.OdbcDataAdapter to connect from any database via ODBC. Also remember, each database will have its own connect string. More information about connection strings can be found here.

Just make sure each table as a unique ID and you should be good to go!