how to read multiple rows from Stored procedure with cursors

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to read multiple rows from an SP into a datalist.

I can easily read and display multiple rows if I use a select statement but when I call an SP to send me a few rows, I only get back the first row. I can also read multiple rows from SP if I have a single select statement in the S

Here is the code:
System.Data.SqlClient.SqlCommand cm;
System.Data.SqlClient.SqlDataReader dr = null;

cm = new System.Data.SqlClient.SqlCommand();
cm.Connection = new System.Data.SqlClient.SqlConnection();
connect......
DataSet ds = new DataSet(); // I don't really need/use this!!!
cm.CommandType = System.Data.CommandType.StoredProcedure;
cm.CommandText = "[getAllTransactions]";

add parameters for SP.....

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

Could someone help?

Thanks
Merdaad
 
Hi Merdaad,

There are always things that belongs to each other,
A datareader is special for reading streaming data from a database.

To get a database you can use the dataadapter(also with stored procedures).

I have changed your code a little here in this message, if it gets errors
try to correct or message back.
using System.Data.SqlClient;
SqlCommand cm;
com = new SqlCommand();
Connection conn = new SqlConnection("connectionString");
connect......
DataSet ds = new DataSet(); // I don't really need/use this!!!
com.Connection = conn;
com.CommandType = System.Data.CommandType.StoredProcedure;
com.CommandText = "[getAllTransactions]";
add parameters for SP.....
SqlClient.SqlDataAdapter da = new SqlClient.SqlDataAdapter(com);
da.fill(ds);
transListData.DataSource=ds;
transListData.DataBind();
///

I changed cm in com because it in the else is to confusing with the currency
manager, you will see you use that soon.

Give it a try?

Cor
 
Back
Top