| Groups | Blog | Home |
dotnet ado.net : Stored Proc results in DataGridView
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
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. ------------------------------------------------------------- [quoted text, click to view] "BillyRogers" <BillyRogers@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
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) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "BillyRogers" <BillyRogers@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
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. ---------------------- [quoted text, click to view] "William (Bill) Vaughn" <billvaRemoveThis@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" <BillyRogers@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 > >
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 [quoted text, click to view] "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" <BillyRogers@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 > >
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 [quoted text, click to view] "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" <BillyRogers@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 > > > >
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. ---------------------------------------- [quoted text, click to view] "BillyRogers" <BillyRogers@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" <BillyRogers@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 >> > >> > >> >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |