How do I show the results of a stored procedure in a datagrid?

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Can someone, please, show me how to display the results of
a stored procedure in a VB.NET datagrid on a winform? I'm
a newbie.

Here's my SQL 2000 stored precedure that returns the
status of SQL Jobs as a table.

==================================================
CREATE PROCEDURE [dbo].[GetRunningJobs] AS

EXEC msdb.dbo.sp_help_job @execution_status = 0

GO
==================================================


Thanks,

Jim
 
Create a dataadapter and assign the select query to this stored proc in your
database. Then, you could create a strongly typed dataset from this, and
assign your datagrid's datasource to your new dataset.

Peace,
cege
 
It's not a select query. I can do select queries. I can
start a stored procedure and get the single integer result
that says the procedure completed. I just can't seem to
get the returned table into a datagrid.

Jim
-----Original Message-----
Create a dataadapter and assign the select query to this stored proc in your
database. Then, you could create a strongly typed dataset from this, and
assign your datagrid's datasource to your new dataset.

Peace,
cege
Can someone, please, show me how to display the results of
a stored procedure in a VB.NET datagrid on a winform? I'm
a newbie.

Here's my SQL 2000 stored precedure that returns the
status of SQL Jobs as a table.

==================================================
CREATE PROCEDURE [dbo].[GetRunningJobs] AS

EXEC msdb.dbo.sp_help_job @execution_status = 0

GO
==================================================


Thanks,

Jim
Click to expand...


.
Click to expand...
 
Dude...

it has to be a select query... thats how data grids work... if you want to
return a single result from a stored proc, don't use a datagrid. Thats like
using a backhoe to plant a daisy...

and if its a "returned table" then yes, thats a select query.

Jim said:
It's not a select query. I can do select queries. I can
start a stored procedure and get the single integer result
that says the procedure completed. I just can't seem to
get the returned table into a datagrid.

Jim
-----Original Message-----
Create a dataadapter and assign the select query to this stored proc in your
database. Then, you could create a strongly typed dataset from this, and
assign your datagrid's datasource to your new dataset.

Peace,
cege
Can someone, please, show me how to display the results of
a stored procedure in a VB.NET datagrid on a winform? I'm
a newbie.

Here's my SQL 2000 stored precedure that returns the
status of SQL Jobs as a table.

==================================================
CREATE PROCEDURE [dbo].[GetRunningJobs] AS

EXEC msdb.dbo.sp_help_job @execution_status = 0

GO
==================================================


Thanks,

Jim
Click to expand...


.
Click to expand...
Click to expand...
 
Maybe I'm confused? Are you saying that "GetRunningJobs"
is my SELECT statment? This returns a table.

Here's where I'm getting stuck:

Dim myConn As SqlConnection = New _
SqlConnection
("server=abc;database=Northwind;integrated security=SSPI")
Dim myDA As New SqlDataAdapter

myDA.SelectCommand = New SqlCommand
myDA.SelectCommand.Connection = myConn

'-------What do I do here?
myDA.SelectCommand.CommandText = "GetRunningJobs"
myDA.SelectCommand.CommandType = _
CommandType.StoredProcedure
'-------
' OR
'-------
myDA.SelectCommand.CommandText = "EXEC GetRunningJobs"
myDA.SelectCommand.CommandType = _
CommandType.Text
'--------

myConn.Open()

' Then which execute command do I use and how?
myDA.SelectCommand.????????????
.ExecuteNonQuery
.ExecuteReader
myConn.Close()


Jim
 
Thanks for your help. The light bulb just lit up in my
head! Here's how I got it to work:

=========
myDA.SelectCommand = New SqlCommand
myDA.SelectCommand.Connection = myConn

'Note I'm running the stored procedure through t-sql.
myDA.SelectCommand.CommandText = "Exec GetRunningJobs"

myDA.SelectCommand.CommandType = CommandType.Text

myConn.Open()
myDA.SelectCommand.ExecuteNonQuery()
myDA.Fill(myDS, "Jobs")
myConn.Close()

DataGrid1.DataSource = myDS
DataGrid1.DataMember = "Jobs"
=========

Jim
 
Back
Top