Loading a datagrid/datalist from a Stored procedure that uses cursor

[Guest]

In my datagrid, I call my SP and if I only have a simple select statement, everything comes back fine and all rows get displayed in datagrid using datasource and databind

But if I use a cursor inside the SP (to manipulate data), the data that comes out of the SP can not be displayed in the datagrid. In this case only the first row shows up
Here is aportion of my page_load

System.Data.SqlClient.SqlCommand cm;
System.Data.SqlClient.SqlDataReader dr = null;
System.Data.SqlClient.SqlCommand cm;
//DataSet ds = new DataSet();

cm = new System.Data.SqlClient.SqlCommand();
cm.Connection = ConnectionString;
cm.Connection.Open();
cm.CommandType = System.Data.CommandType.StoredProcedure;
cm.CommandText = "[getTransList]";

dr = cm.ExecuteReader();
gridListData.DataSource=dr;
gridListData.DataBind();
dr.Close();

 

[Dmitriy Lapshin [C# / .NET MVP]]

Hi,

Binding the DataGrid to a DataReader is not a good idea. You should create
and populate a DataSet or a DataTable and then bind the grid to those.

Second, ensure you return a valid rowset from your SP. I have little
experience with cursors, but I suppose you should still execute a SELECT
statement to return the rowset after you have finished data manipulation.
You could probably introduce a temp table or a local variable of type TABLE
(given your DB is SQL Server 2000).

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

In my datagrid, I call my SP and if I only have a simple select statement,

everything comes back fine and all rows get displayed in datagrid using
datasource and databind.

But if I use a cursor inside the SP (to manipulate data), the data that

comes out of the SP can not be displayed in the datagrid. In this case only
the first row shows up!

Here is aportion of my page_load:

System.Data.SqlClient.SqlCommand cm;
System.Data.SqlClient.SqlDataReader dr = null;
System.Data.SqlClient.SqlCommand cm;
//DataSet ds = new DataSet();

cm = new System.Data.SqlClient.SqlCommand();
cm.Connection = ConnectionString;
cm.Connection.Open();
cm.CommandType = System.Data.CommandType.StoredProcedure;
cm.CommandText = "[getTransList]";

dr = cm.ExecuteReader();
gridListData.DataSource=dr;
gridListData.DataBind();
dr.Close();