Stored Proc results in DataGridView

31 Jan 2007 10:02 PM

BillyRogers

I'm trying to get the results of a stored proc (with no input parameters)
into a datagridview. I'm able to get the results into a messagebox but i'm
nor sure how to get them into a DataGridView

Using cn As New SqlConnection(strConn)
            Try
                cn.Open()
            Catch ex As Exception
                MessageBox.Show("Connect Attempt Failed")
                MessageBox.Show(ex.Message.ToString)
            End Try

            Dim cmd As New SqlCommand("sp_TestBilly", cn)
            cmd.CommandType = CommandType.StoredProcedure

            Dim rdr As SqlDataReader
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            'Do While rdr.Read()
            '    MessageBox.Show(rdr(0) & " " & rdr(1) & " " & rdr(2) & " "
& rdr(3) & " " & rdr(4) & " " & rdr(5) & " " & rdr(6), "sp_BillyTest")
            'Loop
            'rdr.Close()

            datagridview1.DataSource=***here's where I can't get the code
right****

            cn.Close()
        End Using
    End Sub

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

31 Jan 2007 10:59 PM

RobinS

Using cn As New SqlConnection(strConn)
    Try
        cn.Open()
    Catch ex As Exception
        MessageBox.Show("Connect Attempt Failed")
        MessageBox.Show(ex.Message.ToString)
    End Try

    Dim cmd As New SqlCommand("sp_TestBilly", cn)
    cmd.CommandType = CommandType.StoredProcedure
    Dim da as SqlDataAdapter = New SqlDataAdapter(cmd)
    dt = New DataTable()
    da.Fill(dt)

    myDataGridView.DataSource = dt
    cn.Close()
End Using

You can't use a DataReader; it reads the data one row at a time, it doesn't
keep the whole set of data in memory. You have to use a DataSet, DataTable,
List(Of T), Collection, Arraylist, or something like that.

Robin S.
-------------------------------------------------------------
Show quote

"BillyRogers" <BillyRog***@discussions.microsoft.com> wrote in message
news:EF626D0D-EB91-43B7-BE4F-72D3D924442C@microsoft.com...
> I'm trying to get the results of a stored proc (with no input parameters)
> into a datagridview. I'm able to get the results into a messagebox but
> i'm
> nor sure how to get them into a DataGridView
>
> Using cn As New SqlConnection(strConn)
>            Try
>                cn.Open()
>            Catch ex As Exception
>                MessageBox.Show("Connect Attempt Failed")
>                MessageBox.Show(ex.Message.ToString)
>            End Try
>
>            Dim cmd As New SqlCommand("sp_TestBilly", cn)
>            cmd.CommandType = CommandType.StoredProcedure
>
>            Dim rdr As SqlDataReader
>            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
>
>            'Do While rdr.Read()
>            '    MessageBox.Show(rdr(0) & " " & rdr(1) & " " & rdr(2) & "
> "
> & rdr(3) & " " & rdr(4) & " " & rdr(5) & " " & rdr(6), "sp_BillyTest")
>            'Loop
>            'rdr.Close()
>
>
>
>
>            datagridview1.DataSource=***here's where I can't get the code
> right****
>
>
>
>            cn.Close()
>        End Using
>    End Sub
>
> --
> Billy Rogers
>
> Dallas,TX
>
> Currently Using SQL Server 2000, Office 2000 and Office 2003

31 Jan 2007 11:13 PM

William (Bill) Vaughn

Add
Dim dt as new DataTable
dt.Load rdr

before binding dt to the DataSource.

This is explained in detail in my book...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Show quote

"BillyRogers" <BillyRog***@discussions.microsoft.com> wrote in message
news:EF626D0D-EB91-43B7-BE4F-72D3D924442C@microsoft.com...
> I'm trying to get the results of a stored proc (with no input parameters)
> into a datagridview. I'm able to get the results into a messagebox but
> i'm
> nor sure how to get them into a DataGridView
>
> Using cn As New SqlConnection(strConn)
>            Try
>                cn.Open()
>            Catch ex As Exception
>                MessageBox.Show("Connect Attempt Failed")
>                MessageBox.Show(ex.Message.ToString)
>            End Try
>
>            Dim cmd As New SqlCommand("sp_TestBilly", cn)
>            cmd.CommandType = CommandType.StoredProcedure
>
>            Dim rdr As SqlDataReader
>            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
>
>            'Do While rdr.Read()
>            '    MessageBox.Show(rdr(0) & " " & rdr(1) & " " & rdr(2) & " "
> & rdr(3) & " " & rdr(4) & " " & rdr(5) & " " & rdr(6), "sp_BillyTest")
>            'Loop
>            'rdr.Close()
>
>
>
>
>            datagridview1.DataSource=***here's where I can't get the code
> right****
>
>
>
>            cn.Close()
>        End Using
>    End Sub
>
> --
> Billy Rogers
>
> Dallas,TX
>
> Currently Using SQL Server 2000, Office 2000 and Office 2003

31 Jan 2007 11:43 PM

RobinS

As usual, Bill is right. Sigh. I don't use DataReaders very much. I guess I
should buy his book and read it. ;-)

Robin S.
----------------------
Show quote

"William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
news:%23ajay1YRHHA.4448@TK2MSFTNGP04.phx.gbl...
> Add
>    Dim dt as new DataTable
>    dt.Load rdr
>
> before binding dt to the DataSource.
>
> This is explained in detail in my book...
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> -----------------------------------------------------------------------------------------------------------------------
>
> "BillyRogers" <BillyRog***@discussions.microsoft.com> wrote in message
> news:EF626D0D-EB91-43B7-BE4F-72D3D924442C@microsoft.com...
>> I'm trying to get the results of a stored proc (with no input
>> parameters)
>> into a datagridview. I'm able to get the results into a messagebox but
>> i'm
>> nor sure how to get them into a DataGridView
>>
>> Using cn As New SqlConnection(strConn)
>>            Try
>>                cn.Open()
>>            Catch ex As Exception
>>                MessageBox.Show("Connect Attempt Failed")
>>                MessageBox.Show(ex.Message.ToString)
>>            End Try
>>
>>            Dim cmd As New SqlCommand("sp_TestBilly", cn)
>>            cmd.CommandType = CommandType.StoredProcedure
>>
>>            Dim rdr As SqlDataReader
>>            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
>>
>>            'Do While rdr.Read()
>>            '    MessageBox.Show(rdr(0) & " " & rdr(1) & " " & rdr(2) & "
>> "
>> & rdr(3) & " " & rdr(4) & " " & rdr(5) & " " & rdr(6), "sp_BillyTest")
>>            'Loop
>>            'rdr.Close()
>>
>>
>>
>>
>>            datagridview1.DataSource=***here's where I can't get the code
>> right****
>>
>>
>>
>>            cn.Close()
>>        End Using
>>    End Sub
>>
>> --
>> Billy Rogers
>>
>> Dallas,TX
>>
>> Currently Using SQL Server 2000, Office 2000 and Office 2003
>
>

1 Feb 2007 1:16 PM

BillyRogers

Thanks that was perfect. I'll have to take a look at your book.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Show quote

"William (Bill) Vaughn" wrote:

> Add
>     Dim dt as new DataTable
>     dt.Load rdr
>
> before binding dt to the DataSource.
>
> This is explained in detail in my book...
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> -----------------------------------------------------------------------------------------------------------------------
>
> "BillyRogers" <BillyRog***@discussions.microsoft.com> wrote in message
> news:EF626D0D-EB91-43B7-BE4F-72D3D924442C@microsoft.com...
> > I'm trying to get the results of a stored proc (with no input parameters)
> > into a datagridview. I'm able to get the results into a messagebox but
> > i'm
> > nor sure how to get them into a DataGridView
> >
> > Using cn As New SqlConnection(strConn)
> >            Try
> >                cn.Open()
> >            Catch ex As Exception
> >                MessageBox.Show("Connect Attempt Failed")
> >                MessageBox.Show(ex.Message.ToString)
> >            End Try
> >
> >            Dim cmd As New SqlCommand("sp_TestBilly", cn)
> >            cmd.CommandType = CommandType.StoredProcedure
> >
> >            Dim rdr As SqlDataReader
> >            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
> >
> >            'Do While rdr.Read()
> >            '    MessageBox.Show(rdr(0) & " " & rdr(1) & " " & rdr(2) & " "
> > & rdr(3) & " " & rdr(4) & " " & rdr(5) & " " & rdr(6), "sp_BillyTest")
> >            'Loop
> >            'rdr.Close()
> >
> >
> >
> >
> >            datagridview1.DataSource=***here's where I can't get the code
> > right****
> >
> >
> >
> >            cn.Close()
> >        End Using
> >    End Sub
> >
> > --
> > Billy Rogers
> >
> > Dallas,TX
> >
> > Currently Using SQL Server 2000, Office 2000 and Office 2003
>
>
>

1 Feb 2007 1:58 PM

BillyRogers

Is there someway to put the data into excel. I know how how to open an
instance of excel but can't seem to figure out how to past the data without
cycling through each cell.

Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet

        oXL = CreateObject("excel.application")

        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet

       oSheet.Cells(11, 1) = myDataGridView.DataSource **this doesn't work**

        oXL.Visible = True
        oXL.UserControl = True

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Show quote

"BillyRogers" wrote:

> Thanks that was perfect. I'll have to take a look at your book.
> --
> Billy Rogers
>
> Dallas,TX
>
> Currently Using SQL Server 2000, Office 2000 and Office 2003
>
>
> "William (Bill) Vaughn" wrote:
>
> > Add
> >     Dim dt as new DataTable
> >     dt.Load rdr
> >
> > before binding dt to the DataSource.
> >
> > This is explained in detail in my book...
> >
> > --
> > ____________________________________
> > William (Bill) Vaughn
> > Author, Mentor, Consultant
> > Microsoft MVP
> > INETA Speaker
> > www.betav.com/blog/billva
> > www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > __________________________________
> > Visit www.hitchhikerguides.net to get more information on my latest book:
> > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> > -----------------------------------------------------------------------------------------------------------------------
> >
> > "BillyRogers" <BillyRog***@discussions.microsoft.com> wrote in message
> > news:EF626D0D-EB91-43B7-BE4F-72D3D924442C@microsoft.com...
> > > I'm trying to get the results of a stored proc (with no input parameters)
> > > into a datagridview. I'm able to get the results into a messagebox but
> > > i'm
> > > nor sure how to get them into a DataGridView
> > >
> > > Using cn As New SqlConnection(strConn)
> > >            Try
> > >                cn.Open()
> > >            Catch ex As Exception
> > >                MessageBox.Show("Connect Attempt Failed")
> > >                MessageBox.Show(ex.Message.ToString)
> > >            End Try
> > >
> > >            Dim cmd As New SqlCommand("sp_TestBilly", cn)
> > >            cmd.CommandType = CommandType.StoredProcedure
> > >
> > >            Dim rdr As SqlDataReader
> > >            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
> > >
> > >            'Do While rdr.Read()
> > >            '    MessageBox.Show(rdr(0) & " " & rdr(1) & " " & rdr(2) & " "
> > > & rdr(3) & " " & rdr(4) & " " & rdr(5) & " " & rdr(6), "sp_BillyTest")
> > >            'Loop
> > >            'rdr.Close()
> > >
> > >
> > >
> > >
> > >            datagridview1.DataSource=***here's where I can't get the code
> > > right****
> > >
> > >
> > >
> > >            cn.Close()
> > >        End Using
> > >    End Sub
> > >
> > > --
> > > Billy Rogers
> > >
> > > Dallas,TX
> > >
> > > Currently Using SQL Server 2000, Office 2000 and Office 2003
> >
> >
> >

1 Feb 2007 5:39 PM

RobinS

You can do that with ADO.Net, I believe, without automating Excel. I
haven't done it personally, but if you google this newsgroup and
microsoft.public.dotnet.languages.vb, you'll find it's been asked and
answered multiple times.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
----------------------------------------
Show quote

"BillyRogers" <BillyRog***@discussions.microsoft.com> wrote in message
news:F8E33EF5-9197-473A-AA4B-67FAE6854FBA@microsoft.com...
> Is there someway to put the data into excel. I know how how to open an
> instance of excel but can't seem to figure out how to past the data
> without
> cycling through each cell.
>
>
> Dim oXL As Excel.Application
>        Dim oWB As Excel.Workbook
>        Dim oSheet As Excel.Worksheet
>
>        oXL = CreateObject("excel.application")
>
>        oWB = oXL.Workbooks.Add
>        oSheet = oWB.ActiveSheet
>
>
>       oSheet.Cells(11, 1) = myDataGridView.DataSource **this doesn't
> work**
>
>        oXL.Visible = True
>        oXL.UserControl = True
> --
> Billy Rogers
>
> Dallas,TX
>
> Currently Using SQL Server 2000, Office 2000 and Office 2003
>
>
> "BillyRogers" wrote:
>
>> Thanks that was perfect. I'll have to take a look at your book.
>> --
>> Billy Rogers
>>
>> Dallas,TX
>>
>> Currently Using SQL Server 2000, Office 2000 and Office 2003
>>
>>
>> "William (Bill) Vaughn" wrote:
>>
>> > Add
>> >     Dim dt as new DataTable
>> >     dt.Load rdr
>> >
>> > before binding dt to the DataSource.
>> >
>> > This is explained in detail in my book...
>> >
>> > --
>> > ____________________________________
>> > William (Bill) Vaughn
>> > Author, Mentor, Consultant
>> > Microsoft MVP
>> > INETA Speaker
>> > www.betav.com/blog/billva
>> > www.betav.com
>> > Please reply only to the newsgroup so that others can benefit.
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> > __________________________________
>> > Visit www.hitchhikerguides.net to get more information on my latest
>> > book:
>> > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
>> > -----------------------------------------------------------------------------------------------------------------------
>> >
>> > "BillyRogers" <BillyRog***@discussions.microsoft.com> wrote in message
>> > news:EF626D0D-EB91-43B7-BE4F-72D3D924442C@microsoft.com...
>> > > I'm trying to get the results of a stored proc (with no input
>> > > parameters)
>> > > into a datagridview. I'm able to get the results into a messagebox
>> > > but
>> > > i'm
>> > > nor sure how to get them into a DataGridView
>> > >
>> > > Using cn As New SqlConnection(strConn)
>> > >            Try
>> > >                cn.Open()
>> > >            Catch ex As Exception
>> > >                MessageBox.Show("Connect Attempt Failed")
>> > >                MessageBox.Show(ex.Message.ToString)
>> > >            End Try
>> > >
>> > >            Dim cmd As New SqlCommand("sp_TestBilly", cn)
>> > >            cmd.CommandType = CommandType.StoredProcedure
>> > >
>> > >            Dim rdr As SqlDataReader
>> > >            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
>> > >
>> > >            'Do While rdr.Read()
>> > >            '    MessageBox.Show(rdr(0) & " " & rdr(1) & " " & rdr(2)
>> > > & " "
>> > > & rdr(3) & " " & rdr(4) & " " & rdr(5) & " " & rdr(6),
>> > > "sp_BillyTest")
>> > >            'Loop
>> > >            'rdr.Close()
>> > >
>> > >
>> > >
>> > >
>> > >            datagridview1.DataSource=***here's where I can't get the
>> > > code
>> > > right****
>> > >
>> > >
>> > >
>> > >            cn.Close()
>> > >        End Using
>> > >    End Sub
>> > >
>> > > --
>> > > Billy Rogers
>> > >
>> > > Dallas,TX
>> > >
>> > > Currently Using SQL Server 2000, Office 2000 and Office 2003
>> >
>> >
>> >