Capanga Capanga - 11 days ago 6
Vb.net Question

Don't know how to read a sp from vb.net 2013

I have a stored procedure which returns variable columns as a table. I need to put this output in a

DataGridView
. Can anyone help me?

I'm using SQL Server 2013 Express and Visual basic 2013 on windows 8.1

ALTER PROCEDURE [dbo].[sp_Pivot]
@Dias varchar(4),
@Rubro varchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Cols AS NVARCHAR(MAX) = '';
DECLARE @Query AS NVARCHAR(MAX);

SELECT @Cols += STUFF((SELECT DISTINCT ',' + QUOTENAME(Linea) FROM TablaByModel WHERE
Rubro = @Rubro FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SELECT @Query = 'SELECT * FROM (SELECT [AAAAMM] AS [AAAAMM], [Linea] AS [Linea],
' + @Dias + ' as [Dias] FROM [TablaByModel] o ) AS t
PIVOT
(AVG(Dias) FOR Linea IN( ' + @cols + ' )' + ' ) AS p ; ';

EXECUTE(@Query);
END


The output is like this:

AAAAMM A78 A79P Plus TC TC2
--------------------------------------------
201010 7
201104 90
201105 94
201106 43 46 41 38
201107 34 35 35 32
201108 14 11 11 16
201109 43 10 12 119
201110 155 10 12 9 6
201111 23 37 15 10
201112 34 26 13 23


Name columns may vary.

The VB code I'm trying to use is:

' Dim con As SqlConnection = New SqlConnection("Data Source=server\sqlexpress;Initial Catalog=MyDataBase;Integrated Security=True")
' Dim p As SqlClient.SqlParameter
' Dim cmd As New SqlClient.SqlCommand("SP_Pivot", con, Nothing)
' Dim adapter As New SqlClient.SqlDataAdapter
' Dim table As New DataTable
' Dim reader As SqlDataReader
' cmd.CommandType = CommandType.StoredProcedure

' con.Open()
' 'Fill the parameters
' p = cmd.Parameters.Add("@Dias", SqlDbType.VarChar, 4)
' p.Value = "Dias"
' p = cmd.Parameters.Add("@Rubro", SqlDbType.VarChar, 10)
' p.Value = "H"

' 'Assign the select command
' adapter.Selectcommand = cmd

' 'Fill the datatable
' Try
' ' adapter.Fill(table)
' reader = cmd.ExecuteReader()
' Catch ex As System.Exception
' MessageBox.Show(ex.GetBaseException.Message)
' End Try


' 'DataGridView1.DataSource = reader

' con.Close()

Answer

I solve the problem this way: Dim bla bla Parameters ...

     Try
        Conn.Open()
        Dim myReader As SqlDataReader = cmd.ExecuteReader()
        Dim NroReg As Integer = myReader.FieldCount - 1
        Dim dt As New DataTable
        dt.Load(myReader)

        DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = dt
        DataGridView1.Refresh()
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error")
    Finally
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
        End If
    End Try