how to populate a grid view using a stored procedure in VB.net?

2012-07-17

Question

_{Tuesday, July 17, 2012 1:14 PM}

how to populate a grid view using a stored procedure in VB.net?

All replies (13)

_{Friday, July 20, 2012 6:24 AM ✅Answered}

Hi AD_mm,

Welcome to the MSDN forum.

At first, the gridview is a web UI, so if you use this control with a Asp.net application, your issue is out of scope here. Microsoft provides a special website to deal with Asp.Net issue, please post this issue to ASP.Net forums: http://forums.asp.net/

If you use a Winform VB.Net application, the root issue is how to invoke stored procedure in VB.Net. I have no idea about what database you used, I assume you use SQL query. You can check the SqlCommand.CommandType Property in SqlCommand class: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtype.aspx

When you set the CommandType property to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when you call one of the Execute methods.

Here are some samples for you:

SQL Stored Procedure with VB.NET code, no dataview: http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/a1ae0099-b81e-447d-ae82-2c23d22eed87

VB.Net Display Stored Procedure Print Results in DataGridView: http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/a38f82e3-d1f8-4dbd-95c1-8ba6311fc1ff

Hope this helps.

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

_{Tuesday, July 17, 2012 1:35 PM}

Hard to answer without mroe details, but you basically want to load the results of the stored procedure into a type of collection class and then you can set the gridview datasource to the collection.

Lawrence L Schaeffer

_{Tuesday, July 17, 2012 1:38 PM}

I have made a simple select stored procedure in SQL server now i want to populate a datagridview (in VB.net) using the Stored procedure(filling the grid view)

_{Tuesday, July 17, 2012 1:53 PM}

Then my solution will work.

Lawrence L Schaeffer

_{Tuesday, July 17, 2012 3:03 PM}

Could you change the type of your post from a comment to a question please. Thanks.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Wednesday, July 18, 2012 6:02 AM}

wht is it?

_{Wednesday, July 18, 2012 6:04 AM}

the type is question only

_{Wednesday, July 18, 2012 1:29 PM}

As I stated in a previous response:

1. Load the result of the Stored Procedure into a Collection type class

2. Set the Collection class variable as the datasource to the grid.

3. Databind the grid

Lawrence L Schaeffer

_{Friday, July 20, 2012 7:15 AM}

u should make a module :

Imports System.Data.SqlClient
Module Koneksi
   
    Dim ds As String
    Dim db As String

    Public Function connect() As SqlConnection
        ds = My.Settings.ServerName
        db = My.Settings.DatabaseName
        Dim conn As SqlConnection
        Try
            conn = New SqlConnection("Integrated Security=True;Persist Security Info=False;Initial Catalog=" & db & ";Data Source=" & ds)
            conn.Open()
            Return conn
        Catch ex As Exception
            If MessageBox.Show("Koneksi Gagal, Pilih yes untuk mengubah setting koneksi", "Gagal", MessageBoxButtons.YesNo, MessageBoxIcon.Error) = Windows.Forms.DialogResult.Yes Then
                FormKonfigurasiKoneksi.Show()
            End If
        End Try
    End Function


    Public Function GetData(ByRef gridview As DataGridView, ByVal query As String)
        Dim Adapter As New SqlDataAdapter(query, connect)
        Dim data As New DataTable()

        Try
            Adapter.Fill(data)
            If data.Rows.Count <> 0 Then
                gridview.DataSource = data
            End If
        Catch ex As Exception
            Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        connect.Close()
    End Function

    Public Function CommandSql(ByVal sql As String, ByRef conn As SqlConnection, ByRef adapter As SqlDataAdapter, ByRef data As DataTable)
        Try
            adapter.SelectCommand = New SqlCommand(sql, conn)
            data.Clear()
            adapter.Fill(data)

        Catch ex As Exception
            Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        connect.Close()
    End Function
End Module

so when u will fill a datagrid just write :

Imports System.Data.SqlClient
Public Class FormBarang
    Dim conn As SqlConnection = Koneksi.connect()
    Dim adapter As SqlDataAdapter = New SqlDataAdapter
    Dim data As DataTable = New DataTable

    Private Sub DataGridRefresh()                                    
        Koneksi.CommandSql("SELECT * FROM tabelBarang", conn, adapter, data)
        dgvData.DataSource = data
        dgvData.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dgvData.EditMode = DataGridViewEditMode.EditProgrammatically
End Sub

dgvData is name of DataGridView control.

_{Saturday, July 21, 2012 2:39 PM | 1 vote}

I've tried this but it's not compilable. It seems you have Option Strict Off. Function must have a return type. If you don't want to return a value, write a Sub.

For Sub CommandSql, is there a reason why you pass some values ByRef? You don't pass back other/new values via these parameters.

Armin

_{Thursday, July 26, 2012 9:26 AM}

Hi AD_mm,

We haven’t heard from you for several days. I’d like to mark my reply as answer firstly. If you have any additional questions, you also can unmark the replay and post your question here.

Sorry for any inconvenience and have a nice day.

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

_{Monday, September 8, 2014 4:43 AM}

Dim cmd As New SqlCommand()
cmd.Connection = Conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "Your Store Procedure Name"
cmd.Connection.Open()
cmd.ExecuteReader()
cmd.Connection.Close()
Dim adp As New SqlDataAdapter(cmd)
Dim tb As New DataTable
adp.Fill(tb)
Datagridview.DataSource = tb

_{Monday, September 8, 2014 9:53 AM}

Dim cmd As New SqlCommand()
cmd.Connection = Conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "Your Store Procedure Name"
cmd.Connection.Open()
cmd.ExecuteReader()
cmd.Connection.Close()
Dim adp As New SqlDataAdapter(cmd)
Dim tb As New DataTable
adp.Fill(tb)
Datagridview.DataSource = tb

Atif,

Beside that this question is 2 years old and there is probably nobody watching it anymore, is this question is probably about a GridView not about a DataGridView a complete other control which is meant for ASP.Net

There are hundreds of Grid types from which is the DataGridView only one.

Success
Cor