VB.Net DataGridView


This article is written by Pon Saravanan  on 03-May-10 Last modified on :01-Jun-10





DataGridView Binding- VB.Net Tutorial

Binding DataGridView is very simple in .Net. Provided the knowledge of connecting to database, executing the SQL Select Queries and filling the dataset with the data fetched is necessary. While data reader also can be used for light weight read only purposes, I am explaining here with dataset. Since the coming articles in this website will refer back this article for DataBinding samples

Connect To DataBase to bind VB.Net DataGridView

We are going to use the following namespaces for using the database related objects in ADO.Net,
Import System.Data
Import System.Data.SqlClient

Next step is to get the connection to the database. Since we have already decided to use the SqlClient, we have to use SqlConnection to open a connection as follows.
1. Initialize the connection object
Dim ConnectionToFetch As New SqlConnection

2. Specify the connection string
ConnectionToFetch.ConnectionString = "Server= localhost\SQLEXPRESS;Database=Northwind;Trusted_Connection=true"

3. Open the connection
ConnectionToFetch.Open()

Note: Northwind database can be obtained from MSDN
http://blogs.msdn.com/smartclientdata/archive/2005/11/02/488258.aspx
Next step is to use the opened connection to fetch data. We have several options to fetch the data. But in this example I am going to use SqlDataAdapter and DataSet.

Read the Data

1. Initialize the SqlDataAdapter and DataSet
Dim SampleDataAdapter As New SqlDataAdapter
Dim SampleSource As New DataSet

2. Create a command object

3. Connect the command object with the opened active connection

4. Assign the command object to the SqlDataAdapter
Dim SelectQry = "SELECT * FROM Products " 
Dim SampleCommand As New SqlCommand()
Dim SampleDataAdapter = New SqlDataAdapter()

SampleCommand.CommandText = SelectQry
SampleCommand.Connection = Connection
SampleDataAdapter.SelectCommand = SampleCommand

5. Use SqlDataAdapter’s fill method to fill the dataset
ConnectionToFetch.Fill(SampleSource)

6. Close the connection
ConnectionToFetch.Close()

7. Assign the dataset to the GridView’s Datasource to bind VB.Net DataGridView
GridView1.DataSource = dsGrid.Tables(0)

8. Bind the GridView with Dataset
GridView1.DataBind()

Source Code for VB.Net DataGridView

Code Behind

Imports System.Data.SqlClient
Public Class Form1
    Private Const ConnectionString As String = "Server=.\SQLEXPRESS;" & _
                    "Database=NorthWind;Trusted_Connection=True"
    Private ReadOnly Property Connection() As SqlConnection
        Get
            Dim ConnectionToFetch As New SqlConnection(ConnectionString)
            ConnectionToFetch.Open()
            Return ConnectionToFetch
        End Get
    End Property
    Public Function GetData() As DataView
        Dim SelectQry = "SELECT * FROM Products "
        Dim SampleSource As New DataSet
        Dim TableView As DataView
        Try
            Dim SampleCommand As New SqlCommand()
            Dim SampleDataAdapter = New SqlDataAdapter()
            SampleCommand.CommandText = SelectQry
            SampleCommand.Connection = Connection
            SampleDataAdapter.SelectCommand = SampleCommand
            SampleDataAdapter.Fill(SampleSource)
            TableView = SampleSource.Tables(0).DefaultView
        Catch ex As Exception
            Throw ex
        End Try
        Return TableView
    End Function
   
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DataGridView1.DataSource = GetData()
    End Sub
End Class



« Previous -
VB DataTable Sort - Next »







Comments
  • GUEST
    thank u very so much its vary useful fopr me & for job 5/4/2010 9:03:12 PM

  • GUEST
    Thanks! now i got how to use grid in project 6/26/2010 12:57:53 AM

  • GUEST
    Awesome Post!

    Clear explanation on database connection, read the data, and databinding the Datagridview.
    8/18/2010 8:15:15 PM

  • GUEST
    Thanks sir,great code 3/24/2011 8:24:20 AM

  • GUEST
    youuuuuuuuuuuuuuuu saved my life man for real

    thanks alot god bless u
    4/8/2011 12:05:02 PM

  • GUEST
    Excellent :) 4/27/2011 3:33:11 PM

  • GUEST
    i want to insert the Added row in SqlDatabase from Gridview. Pls help me. 4/30/2011 5:26:00 AM

  • GUEST
    thenks man thes cod is very importain me thainks for ever 5/3/2011 1:37:09 AM

  • GUEST
    Please give me the how i can fetch data from sql server 2008 database using stored procedure which contains optional parameters in vb.net pls help me...... 5/13/2011 5:37:07 AM

  • pons
    Try this.

    http://www.vbknowledgebase.com/?Id=151&Desc=Asp.Net-Stored-Procedure-Output-Parameter

    For optional parameters you dont need to send the values from asp.net if you dont have the data


    while defining the parameters in stored procedure assign null as shown below

    @Description varchar(100) = null


    5/13/2011 5:48:46 AM

  • GUEST
    Thanks for the post!Damn it helped me in my project......keep up the good work! 5/20/2011 5:43:54 AM

  • GUEST
    Thanks for the post!Damn it helped me in my project......keep up the good work! 5/20/2011 5:46:01 AM

  • GUEST
    Thank you. You guys are amazing. Its very help full of you. Thank you again. 6/25/2011 2:48:14 AM

  • GUEST
    thank's 6/29/2011 3:30:00 AM

  • GUEST
    This is ok.
    if you can connect search result to a griw view.
    6/29/2011 10:11:01 PM

  • GUEST
    Finally a short tutrorial that beginner's can follow yet has enough substance to be useful!!! Thanks. Wish there were more of these on the net. 7/4/2011 6:19:55 PM

  • GUEST
    7/16/2011 4:47:42 AM

  • GUEST
    Now, how to use code to update the grid to redisplay the contents of the datasource
    after an update to the data has been performed through another part of the code. Refresh doesn't update the contents of the grid.
    7/21/2011 2:01:24 PM

  • GUEST
    Now, how to use code to update the grid to redisplay the contents of the datasource
    after an update to the data has been performed through another part of the code. Refresh doesn't update the contents of the grid.
    7/21/2011 2:01:25 PM

  • GUEST
    dfgfhghj 8/5/2011 1:55:39 AM

  • GUEST
    thank you so much. it was very useful 8/5/2011 11:34:02 PM

  • GUEST

    thanks this code very useful but can u tell how i can update and delete the record in datagridview itself simultaneously should update into the database about changes.


    8/15/2011 12:27:41 AM

  • GUEST
    there is update functionality in grid itself find it in option 8/22/2011 3:00:20 AM

  • GUEST
    super :) 8/26/2011 5:30:02 AM

  • GUEST
    clearly explained...tnx 8/30/2011 11:16:04 PM

  • GUEST
    not bad! 9/26/2011 2:47:49 AM

  • GUEST
    10/19/2011 4:00:13 PM

  • GUEST
    question (sql2005 connect to vb.net 2008)
    i want to update data in database using datagridview
    actually i am displaying the data in datagridview and after that i want to update some rows data directly modifying the displayed data into the datagridview
    question 2
    i want to insert one data from a textbox i was design in vb net in two tables of same database, for me i am able to insert only that data on one table, using insert button i was create. can you help me is it necessary to create to store procedures?

    i am waiting your codes, thanks
    my email is : clauden300@yahoo.fr

    10/23/2011 3:33:28 AM

  • pons
    No it is not necessary to create a stored procedure. However, You should understand how create and update works. It is hard for me to explain in the comments. But you can search for a tutorial online for now. I will update this code in the future to add the insert and update functionalities 10/23/2011 10:33:50 AM

  • GUEST
    Thanks so much. This was extremely helpful 11/2/2011 7:47:26 PM

  • GUEST
    i need what are all types available for ado.net 11/9/2011 4:27:32 AM

  • GUEST
    very cool :) 11/9/2011 5:43:57 AM

  • GUEST
    thanks








    varun
    11/21/2011 10:33:45 PM

  • GUEST
    hmmmm...gud job dude 12/2/2011 1:31:07 AM

  • GUEST
    this code work but data not display.
    Only row count
    So plz help me...
    1/15/2012 8:05:23 PM

  • GUEST
    Gr8 Job 1/31/2012 2:27:03 PM

  • GUEST
    bullshit 2/7/2012 3:04:19 AM

  • GUEST
    when I load from I want to have dataGridView cleard. I tried datagrid.table.clear & datagrid.datasource=nothing , datagrid.refresh. and it didn't work and when I load the form I find old rows in datagridview. also I tried to clear table in data set and nothing happend. what can i do ?? :( 2/12/2012 6:10:13 PM

  • GUEST
    what if i have 2 columns in datagridview, 1 pick up the data from database and another one for user input. how can i do that? 2/21/2012 10:21:27 AM

  • GUEST
    Thanks for providing us so much valuable information on database connection and data binding the data grid view.http://dapfor.com/en/net-suite/net-grid/features 4/2/2012 11:08:06 PM

  • GUEST
    good....thanks.. 4/13/2012 10:31:22 AM

  • GUEST
    good....thanks.. 4/13/2012 10:31:29 AM

  • GUEST
    Really helpful,,thanks a lot!! 4/19/2012 5:28:58 AM

  • GUEST
    thaaaaaaaaaaaaaaaaaaaaaaaaaaaanks a lot
    5/20/2012 5:52:54 AM

  • GUEST
    nice job
    2/8/2013 7:07:23 AM

  • GUEST
    nice job
    2/8/2013 7:07:31 AM

  • GUEST
    is a fast growing start-up which is having their ERP Web solutions for Education, Finance & Hospitality domain. Recently they have started with training modules for Dot Net / JAVA / PHP technologies & Android / Windows 8 Framework application development. Good opportunity for 6 Week / Internship program for all Engineering & MCA.
    They are Industry professionals to train & get you prepared for IT Job Interviews. The Good part is they have seperately added a interview preparatory technical programs to get you qualify in any IT interview.

    Add-Ones for training profile: -

    --> Technical Leaders & Managers from Industry to train
    --> Live project development Exposure
    --> Valid Pvt ltd company experience
    --> 100% Job Assistance
    --> Personality Development Sessions + Resume Writing Tips
    --> Free Mock Tests for written exams for Interview Cracking
    12/22/2013 2:38:31 AM

  • GUEST
    Acceleration is a fast growing start-up which is having their ERP Web solutions for Education, Finance & Hospitality domain. Recently they have started with training modules for Dot Net / JAVA / PHP technologies & Android / Windows 8 Framework application development. Good opportunity for 6 Week / Internship program for all Engineering & MCA.
    They are Industry professionals to train & get you prepared for IT Job Interviews. The Good part is they have seperately added a interview preparatory technical programs to get you qualify in any IT interview.

    Add-Ones for training profile: -

    --> Technical Leaders & Managers from Industry to train
    --> Live project development Exposure
    --> Valid Pvt ltd company experience
    --> 100% Job Assistance
    --> Personality Development Sessions + Resume Writing Tips
    --> Free Mock Tests for written exams for Interview Cracking
    12/23/2013 5:44:09 AM


Comments
   
Captcha Image
For you specially:  
Captcha Text Enter the text in the image.(Not Case sensitive)    



Spam Bot Trap



   



Select Theme
White
Gray
Blue
Brown
GraySimple