Connecting Database

In Visual Basic 2015, we need to create a connection to a database before we can access its data. Before we begin, create a new database. Since this lesson uses SQL Server as the database engine, you can use Microsoft SQL Server Management Studio Express to create a database file with the mdf extension. In the original example, the database file is named Test.mdf. :contentReference[oaicite:1]{index=1}

After creating the database, build a table called Contacts and create two fields named ContactName and State. Enter a few records into the table and save the data. Now you are ready to connect to the new database.

ADO.NET offers a number of connection objects such as OleDbConnection, SqlConnection, and others. OleDbConnection is commonly used to access OLE DB data sources such as Microsoft Access, whereas SqlConnection is used to access data provided by Microsoft SQL Server. Since this lesson works with SQL Server, we will use the SqlConnection object.

To initialize the variable as a new SqlConnection object, use the following statement:

Private MyCn As New SqlConnection

After creating the instance of the SqlConnection object, the next step is to establish the connection to the data source using the ConnectionString property.

MyCn.ConnectionString = "Data Source=TOSHIBA-PC\SQL2012; " &
"AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test.mdf; " &
"User Instance=True;Integrated Security=SSPI"

You need to change the SQL Server reference and the path to the database file so that they match your own computer system.

After establishing the connection string, you can open the database using:

MyCn.Open()

Establishing a connection to a database in Visual Basic 2015 using SqlConnection alone will not present anything useful to the user until we add more relevant objects and write the appropriate code.

The next step is to create an instance of the SqlDataAdapter so that we can populate the DataTable with data from the data source. Besides that, you also need to create an instance of the DataTable. In addition, you should create an instance of the SqlCommandBuilder, which is used to manipulate data such as updating and deleting records in the DataTable and sending the changes back to the data source.

Private MyDatAdp As New SqlDataAdapter
Private MyCmdBld As New SqlCommandBuilder
Private MyDataTbl As New DataTable

Besides that, we need to declare a variable to keep track of the user’s current row position within the DataTable:

Private MyRowPosition As Integer = 0

After creating the above objects, include the following statements in the Form_Load event to start filling the DataTable with data from the data source:

MyDatAdp = New SqlDataAdapter("Select * from Contacts", MyCn)
MyCmdBld = New SqlCommandBuilder(MyDatAdp)
MyDatAdp.Fill(MyDataTbl)

After filling up the DataTable, we need to write code to access the data. Accessing data in the DataTable means accessing the rows in the table. We can achieve this by using the DataRow object. For example, the following code accesses the first row of the table and presents the data via two text boxes named TxtName and TxtState.

Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
Dim strName As String
Dim strState As String

strName = MyDataRow("ContactName")
strState = MyDataRow("State")

TxtName.Text = strName.ToString
TxtState.Text = strState.ToString

Here, the two fields being referenced are ContactName and State. The index 0 means the first row.

In the original lesson, a sub procedure named showRecords() is used to show data in the text boxes. The code is:

Private Sub showRecords()

    If MyDataTbl.Rows.Count = 0 Then
        TxtName.Text = ""
        TxtState.Text = ""
        Exit Sub
    End If

    TxtName.Text = MyDataTbl.Rows(MyRowPosition)("ContactName").ToString
    TxtState.Text = MyDataTbl.Rows(MyRowPosition)("State").ToString

End Sub

This procedure checks whether the table contains any rows. If there are no rows, the text boxes are cleared. Otherwise, the current record is displayed according to the value of MyRowPosition.

Below is the cleaned-up complete example based on the original lesson:

Public Class Form1

    Private MyDatAdp As New SqlDataAdapter
    Private MyCmdBld As New SqlCommandBuilder
    Private MyDataTbl As New DataTable
    Private MyCn As New SqlConnection
    Private MyRowPosition As Integer = 0

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        MyCn.ConnectionString = "Data Source=TOSHIBA-PC\SQL2012; " &
                                "AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test.mdf; " &
                                "User Instance=True;Integrated Security=SSPI"

        MyCn.Open()

        MyDatAdp = New SqlDataAdapter("Select * from Contacts", MyCn)
        MyCmdBld = New SqlCommandBuilder(MyDatAdp)
        MyDatAdp.Fill(MyDataTbl)

        Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
        Dim strName As String
        Dim strState As String

        strName = MyDataRow("ContactName")
        strState = MyDataRow("State")

        TxtName.Text = strName.ToString
        TxtState.Text = strState.ToString

        Me.showRecords()

    End Sub

    Private Sub showRecords()

        If MyDataTbl.Rows.Count = 0 Then
            TxtName.Text = ""
            TxtState.Text = ""
            Exit Sub
        End If

        TxtName.Text = MyDataTbl.Rows(MyRowPosition)("ContactName").ToString
        TxtState.Text = MyDataTbl.Rows(MyRowPosition)("State").ToString

    End Sub

End Class

This code establishes the connection, retrieves the data from the Contacts table, stores it in a DataTable, and then displays the first record in the text boxes.