Help with Code for Stored Procedure

[jamie_pattison]

Im trying to retrieve some data from a database using a stored procedure. Heres my code:

Dim connectionString As String = "server=test; database=testdb; integrated security=true;"
Dim sqlCon As New SqlConnection(connectionString)
Dim sqlComm As SqlCommand = New SqlCommand
Dim sdr As SqlDataReader

sqlComm.Connection = sqlCon
sqlComm.CommandText = "A_Stored_Procedure"
sqlComm.CommandType = CommandType.StoredProcedure

sqlCon.Open()
sdr = sqlComm.ExecuteReader
DataGridView1.DataSource = sdr.Read
DataGridView1.Show()
sqlCon.Close()

Unfortunately this doesnt return any results so i dont knwo if the Stored Procedure is at fault or this code? When i run the Procedure in SQL studio i does return the output i require. Heres the code for my stored procedure

USE [Testdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Test]
AS
SELECT * FROM
Table

Can anyone advise please?

Thanks

 

[jmcilhinney]

You assigning the result of your DataReader's Read method to your grid's DataSource. What does Read return? Have you actually checked? Raed does NOT actually return any data. It simply moves the reader to the next record in the result set, which will be the first row the first time you call it. It will return either True or False, depending on whether there is a row to read or not. So, you're setting your grid's DataSource to a single Boolean value.

You cannot bind a DataGridView to a DataReader at all. WinForms data-binding requires an IList or an IListSource object, which a DataReader is not. A DataReader can be bound in Web apps because in that case only an IEnumerable object is required, which a DataReader is.

What you need to do is load your data into a DataTable and bind that. You can either use a DataAdapter to populate a DataTable directly or else create a DataTable and call its Load method, passing your DataReader.