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

Question

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 2012 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()

score 0 · Answer 1 · answered Jun 19 '14 at 16:00

0

The problem is you should pass the DataGridView1.DataSource a DataTable, yet you pass it the SqlDataReader from cmd.ExecuteReader(). Look at the documentation for DataGridView.DataSource to see what it accepts.

Anyways, what you need to do is set DataSource to table after adapter.Fill(table) has been invoked.

answered Jun 19 '14 at 16:00

j.i.h.

815
8
29

I can't do a Fill method or don't know how to do it. The tableadapter when generate don't create this method and don't let me create one. Sorry for my english. – Capanga Jun 19 '14 at 16:45
You already call it: 'Fill the datatable Try adapter.Fill(table) reader = cmd.ExecuteReader() Catch ex As System.Exception MessageBox.Show(ex.GetBaseException.Message) End Try – j.i.h. Jun 19 '14 at 16:59
I use the assistant to create a DataSource and select the sp, when I use the preview data I can see the table. but when I use the code I can't see anything. I don't know another way to see it. – Capanga Jun 19 '14 at 17:35

score 0 · Accepted Answer · answered Jun 19 '14 at 19:49

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

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

2 Answers2