Stored Proc results in DataGridView

[Guest]

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

 

[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.

 

[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)

 

[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.
----------------------

 

[Guest]

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

 

[Guest]

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

 

[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.
----------------------------------------