Sql – Populate DataGridView from a Stored Procedure

datasetnetsqlsql servervb.net

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

Best Answer

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

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Best Answer

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Related Topic