How to get dynamic pivot from sql to vb

Question

I would like to get a resultset that is dynamic according to which elements i find. Here is a sample of my query:

declare @til DateTime = dateadd(MINUTE, -0, getdate())
declare @fra datetime = DATEADD(MINUTE, -350, @til)

declare @title nvarchar(max) = 'test title'
DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

create table errors (collection_id bigint, nr smallint, position smallint, stamp datetime)
create table t (collection_id bigint, collection_name nvarchar(max), nr smallint, [status] smallint, stamp datetime)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(errors.position)
                      from t t
                        left join errors on errors.collection_id = t.collection_id and errors.nr = t.nr
                        where t.Status = 4 and errors.Stamp > @fra and t.collection_name = ''' + @title + ''' and errors.collection_id is not null
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '');

select @cols

set @query = 'declare @til DateTime = dateadd(MINUTE, -0, getdate())
            declare @fra datetime = DATEADD(MINUTE, -350, @til)

            ;with cte as (select t.collection_name, errors.position, count(errors.Tryksag_Nedtagsfejl_Id) antal
            from t
            left join errors on errors.collection_id = s.collection_id and errors.nr = t.nr
            where t.Status = 4 and errors.Stamp > @fra and and t.collection_name = ''' + @title + '''
            group by t.collection_name, errors.position)

                SELECT collection_name, ' + @cols + ' from 
                cte
            pivot 
            (
                 sum(antal)
                for position in (' + @cols + ')
            ) p '

execute(@query)

So far i have made a query i can run on SSMS and gives the output i desire. Which would be like this:

How can i make this resultset available to me in vb.net? When i just run it all as a query it doesnt give me the results (assuming it doesn't see the resultset from execute)

Added VB Code

Dim var_til As Short = 0
            Dim var_fra As Short = -60
            Dim Linie As String = "Red"
            Dim tx = "Test title"
            Dim Stt2 = "declare @til DateTime = dateadd(MINUTE, " & var_til & ", getdate()) " _
                & "declare @fra datetime = DATEADD(MINUTE, " & var_fra & " , @til) " _
                & "DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX) " _
                & "declare @linie as nvarchar(max) = '" & Linie & "' " _
                & "declare @title as nvarchar(max) = '" & tx & "' " _
                & "select @cols = STUFF((SELECT distinct ',' + QUOTENAME(errors.position) " _
                & "from t " _
                & "left join errors on errors.collection_id = t.collection_id And errors.nr = t.nr " _
                & "where t.Status = 4 And errors.Stamp > @fra And t.collection_name = ''' + @title + ''' and errors.collection_id is not null " _
                & "FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '') " _
                & " " _
                & "set @query = ';with cte as (select t.collection_name, errors.position, count(errors.Tryksag_Nedtagsfejl_Id) antal " _
                & "from t " _
                & "left join errors on errors.collection_id = s.collection_id And errors.nr = t.nr " _
                & "where t.Status = 4 And errors.Stamp > @fra And And t.collection_name = ''' + @title + ''' " _
                & "group by t.collection_name, errors.position) " _
                & "SELECT collection_name, ' + @cols + ' from " _
                & "cte " _
                & "pivot " _
                & "( " _
                & "sum(antal) " _
                & "for position in (' + @cols + ') " _
                & ") p ' " _
                & "execute(@query) "

            Dim sqlConnection2 As New SqlConnection("Data Source=CONDOR-TI;Initial Catalog=Condor_db;Integrated Security=True")
            Dim cmd2 As New SqlCommand
            Dim reader2 As SqlDataReader
            cmd2.CommandText = Stt2
            cmd2.CommandType = CommandType.Text
            cmd2.Connection = sqlConnection2
            sqlConnection2.Open()
            reader2 = cmd2.ExecuteReader()
            While reader2.Read
                Console.Write(reader2(0))
            End While
            Console.WriteLine()
            sqlConnection2.Close()
            reader2.Close()

Answer 1

I think it is better to create a stored procedure to execute this SQL command, and execute it from VB.Net using the following code:

Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader

cmd.CommandText = "StoredProcedureName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()
' Data is accessible through the DataReader object here.

sqlConnection1.Close()

Reference

• How to: Execute a Stored Procedure that Returns Rows