Populate DataGridView from a Stored Procedure

Question

Using SQL Server 2008 I created a Stored Procedure called MyStoreProc and it runs fine from the Management Tools.

In VB.Net 2008 I created a new dataset and a new TableAdaptor. In this table adapter I created a new Query called FillByGrid and selected the Stored Procedure. Previewed data and it previewed correctly.

On a form I created DataGridView and selected the Table Adapter from the dataset.

I ran the app and no data is shown. Visual Studio autocreated the code below and I changed it to select the the Query I just created:

Me.MyTableAdapter.FillByGrid(Me.MyDataset.MyTableAdaptor)

No data is shown on the grid so I tried the manual approach:

' Create the dataset
Dim da As New SqlDataAdapter, ds As New DataSet
Dim conn As New SqlConnection

conn.ConnectionString = opsData.DBConn.ConnectionString
da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.SelectCommand.CommandText = "dbo.MyStoreProc"
da.Fill(ds, "tbl1")
DataGridView2.DataSource = ds.Tables("tbl1")

Still no data shown. However stepping through the I can see that the connection is open, and "da.Fill(ds, "tbl1")" takes a little bit of time as it is running the Stored Procedure and ds table has the correct number of rows and columns. Its just not being shown on the datagrid.

Creating another table adapter in the dataset and returning data from a database table using a standard select * from table command display in the datagridview fine.

Does anyone have any suggestions?

Thank you

Solution 1

There seem to be a wealth of resources available on how to do this. The best source would be: http://www.asp.net/learn/data-access/ -

Failing that a good old fashioned search yields lots too.

Solution 2

I managed to get this going now. Below is what I did. Thank you everyone for your help. It pushed me in the right direction.

Dim strCon As String = myConnectionString
Dim strSQL As String = "dbo.MyStoreProc"
Dim dataAdapter As New SqlClient.SqlDataAdapter(strSQL, strCon)
Dim table As New DataTable
dataAdapter.Fill(table)
DataGridView1.DataSource = table