Displaying data in a DataGridView control using Dataset and DataAdapter in Visual Basic.Net

This article explains how to display data in a DataGridView control using DataSet. In this sample program, we will use a SQL Server database, FinAccounting and we will access Accounts table of FinAccounting database and display records in the datagridview control.

dataset in a database application

Datasets are part of the DataAccess Layer. How do you use Dataset object in a DataAccess Layer of a database application? Preparation:

Create a Windows form and add a DataGridView control to the form. Tasks:
  • Declare and setup the Connection string
  • Declare and build a query string
  • Create SqlDataAdapter Object
  • Create a DataSet Object and Fill it with data
  • Bind DataSet to the DataGridView control

Declare and setup the Connection string

This task declares, instantiates and sets up the connection. We need a connection when we want to access a data source. We will use a connection string to set up the connection with the database. We have used the SqlConnection class which is only to be used with MS SQL Server database as it has been specially optimized for connecting to the SQL Server database.

Declare and build a query string

All query statements used in the program are declared in this section and assigned to string variables.

Create SqlDataAdapter Object

The DataAdapter is used for retrieving data from the database and populating the dataset. The DataAdapter is the connector class that sits between the disconnected and the connected parts of ADO.Net. The DataAdapter class is instantiated using the New constructor, which is overloaded. If we use the default constructor for the DataAdapter, we need to specify the Command object for the actions performed. This means that if we want to retrieve rows from the Data Source, we will have to set the Select command property.

Create a DataSet Object and Fill it with Accounts data

Unlike the managed provider objects, the DataSet object do not diverge between the OleDb and SqlClient .net namespaces. We declare a DataSet object the same way regardless of which .NET data provider we are using:

myDataSet = New DataSet()

Once we have set up the DataAdapter and the DataSet, we need to populate the DataSet. The Fill method of the DataAdapter class is used for populating and refreshing the DataSet object.

Attach DataSet to DataGrid

Now we use DataSource method of DataGridView control to attach the DataSet data to the datagridview control.

Program Code


This program demonstrates how to access data using a DataAdapter and store it in a dataset. This DataSet is attached to the datagridview control and displayed on the form.

Imports System.Data.SqlClient

Public Class Form1
'Declare the string variable 'connectionString' to hold the ConnectionString        
Dim connectionString As String = "server=SYS2;" + "integrated security=SSPI;" +
'Declare the string variable 'str_Account_Select' to hold the SQL statement
Dim str_Account_Select As String = "SELECT * FROM AccountsTable "

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myAdapter As SqlDataAdapter
'Declare the DataSet object
Dim myDataSet As DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles MyBase.Load
    'Instantiate the Connection object
    myConnection = New SqlConnection(connectionString)

    'Instantiate the Command object
    myCommand = New SqlCommand(str_Account_Select, myConnection)


    'Instantiate  DataSet object
    myDataSet = New DataSet()

    'Instantiate  DataAdapter object
    myAdapter = New SqlDataAdapter()

    'Set DataAdapter command properties
    myAdapter.SelectCommand = myCommand

    'Populate the Dataset
    myAdapter.Fill(myDataSet, "AccountsTable")

    If (myDataSet.Tables("AccountsTable").Rows.Count = 0) Then
       MessageBox.Show("There are currently no registries in the database.")
       DataGridView1.DataSource = myDataSet.Tables("AccountsTable")
    End If
    End Sub
End Class

cover image of azure cloud book

Develop Azure Applications

Azure Cloud is gaining popularity and is competing with Amazon Elastic Cloud and Google Cloud for a spot in the billion dollar Cloud computing space.

With this book learn how to use - Table (an Azure Table is different from a SQL Server Table), Queue, Blob storage services, use your local machine to create an Application and a Service, how Azure splits a Table to achieve Load Balancing, Scalability based on the Partition Key, AppFabric Service Bus, Access Control Service, role of Fabric Controller, features of Azure SQL Database and Content Delivery Network.

Many have mastered Cloud Programming and are enjoying the benefits. Why not you?

View Highlights » View Contents »
cover image of foundation database programming book

Create your Own Accounting Software

A foundation book to Master Accounting Software Development, VB.Net, C# and OOPs programming. Detailed explanation of the Accounting domain for programmers is included. SQL and ADO.Net is also covered in detail.

This book gives you a strong foundation in Application Development.

A thorough understanding of the contents will enable you to grasp the topics covered in the accompanying books quite easily. This is because, the accounting domain is dealt with great detail in this book.

The ideal book to get started with application development in the .Net world.

View Highlights » View Contents »

cover image of asp.net book

Create your Own WEB ERP Software using ASP.Net

This book opens you to the Web. Using ASP.Net, C# and SQL Server as the tools, you will learn to build a Multi Tier Web Application as recommended by Microsoft. You will learn implementing inline GridView control, Transaction Management for a layered Web Application, build Data Access component and a Business Layer component.

Opens you to a world of opportunities. You will be able to develop a Web ERP application including all the five modules and learn how to use the TreeView control and GridView control to implement transactions such as Purchase Invoice, Sales Order and Sales Invoice.

View Highlights » View Contents »
cover image of entity framework book

ASP.Net MVC 5 Application Development Using Entity Framework 6 and VS 2015

Entity frameworks is the latest buzz in .Net programming and is the data access technology for Microsoft OS based hand held devices and web applications. Learn to integrate EF into an ASP.Net Application and MVC application. Create, explore and query the Entity Data Model. Learn how to use ObjectDataSource control and GridView control in an ASP.Net Application.

Also, learn to build Three-Tier ASP.Net application, create a business-class, implement methods in the class to perform CRUD operations and bind a GridView to the ObjectDataSource control. Learn how to handle concurrency and managing Transactions.

View Contents »