datareader return with one result instead of two

[orencs]

Hi,

I am using Datareader and stored procedure in C# ADO.NET.
When I am running the stored procedure in the SQL Query Analyzer and
recieve two rows (as I hace expected)
col1 col2
0 1
0 2
4 2

but when I run it via code the datareader returns with only one result.

Hope someone overcome this and could help
-------------------------------------------------------------------------------------------------------------------------
The Stored procedure

CREATE PROCEDURE FetchData @columnlist char(255), @recordlist
char(255), @tablename char(255)

AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(8000)

SET @SQL =
' SELECT ' + @columnlist + ' FROM ' + @tablename + 'WHERE rd_id IN
(' + @recordlist + ')'

EXEC(@SQL)
END
GO

-------------------------------------------------------------------------------------------------------------------------
The code
string sqlCommand = "FetchData";

DBCommandWrapper dbCommandWrapper =
db.GetStoredProcCommandWrapper(sqlCommand);
dbCommandWrapper.AddInParameter("@columnlist", DbType.String,
sColumn);
dbCommandWrapper.AddInParameter("@recordlist", DbType.String, records);
dbCommandWrapper.AddInParameter("@tablename", DbType.String, table);
dataReader = db.ExecuteReader(dbCommandWrapper);
do {
while (dataReader.Read()) {
try{
o = dataReader["rms_v1_max"]; }catch{}
try{
o = dataReader["rms_v1_min"]; }catch{}

}
} while (dataReader.NextResult());
*****The innner while runs only one time because the NextResult return
false
Thanks in advance
Oren

[Nicholas Paldino [.NET/C# MVP]]

Well, the loop calling NextResult should only run once. You are only
returning one result set. From what I can tell, your code looks correct
(and from what you say, acting correct). Since you are returning one result
set, your call to NextResult runs once, and cycles through the rows in that
result set.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

<or****@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hi,

I am using Datareader and stored procedure in C# ADO.NET.
When I am running the stored procedure in the SQL Query Analyzer and
recieve two rows (as I hace expected)
col1 col2
0 1
0 2
4 2

but when I run it via code the datareader returns with only one result.

Hope someone overcome this and could help
-------------------------------------------------------------------------------------------------------------------------
The Stored procedure

CREATE PROCEDURE FetchData @columnlist char(255), @recordlist
char(255), @tablename char(255)

AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(8000)

SET @SQL =
' SELECT ' + @columnlist + ' FROM ' + @tablename + 'WHERE rd_id IN
(' + @recordlist + ')'

EXEC(@SQL)
END
GO

-------------------------------------------------------------------------------------------------------------------------
The code
string sqlCommand = "FetchData";

DBCommandWrapper dbCommandWrapper =
db.GetStoredProcCommandWrapper(sqlCommand);
dbCommandWrapper.AddInParameter("@columnlist", DbType.String,
sColumn);
dbCommandWrapper.AddInParameter("@recordlist", DbType.String, records);
dbCommandWrapper.AddInParameter("@tablename", DbType.String, table);
dataReader = db.ExecuteReader(dbCommandWrapper);
do {
while (dataReader.Read()) {
try{
o = dataReader["rms_v1_max"]; }catch{}
try{
o = dataReader["rms_v1_min"]; }catch{}

}
} while (dataReader.NextResult());
*****The innner while runs only one time because the NextResult return
false
Thanks in advance
Oren

[orencs]

Nicholas, thank you for your response,

Even if the NextResult should only run once I expect the datareader to
return first the col1 items:0,0,4 then col2 items: 1,2,2
In reality it only returns col1 items.
I have change the code in a way that I don't use stored procedure but
two select stetments and the datareader behave as It should, hence
returns the col1 items after the first iteration and then after the
NextResult, in the second inner loop iteration the values of col2.

Is it ring a bell to you or to anyone?