CODEWITHSUNDEEP
c#asp.netreturnexecutereader
HGomez
HGomez

Reputation: 1534

ExecuteReader is returning only 1 value

I am using an ExecuteReader to return the values of QuestionText when QuestionnaireID matches my query. (See Database Design)

However my problem is that when I run the project my ExecuteReader only returns the last value that was input. For example, if I was to create 3 questions (A,B,C) under the questionnaireID of 1. The ExecuteReader would return Question C only.

How can I return all values in the QuestionText collumn that have the same questionnaireID?

Database Design

enter image description here

****.cs**

public string GetQuestionName(int QuestionnaireID)
        {
            string returnvalue = string.Empty;
            SqlCommand myCommand = new SqlCommand("GetQuestion", _productConn);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(new SqlParameter("@QUEST_ID", SqlDbType.Int));
            myCommand.Parameters[0].Value = QuestionnaireID;   
            _productConn.Open();
            SqlDataReader test = myCommand.ExecuteReader();
            while (test.Read())
            { 
               returnvalue = test.GetString(0);  
            }
            _productConn.Close();
            return returnvalue;        

        }

Stored Procedure

   USE [devworks_oscar]
    GO
    /****** Object:  StoredProcedure [hbo].[GetQuestion]    Script Date: 11/12/2011 13:12:36 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [hgomez].[GetQuestion]  
        (
        @QUEST_ID int
        )   
    AS
        /*SET NOCOUNT ON;*/
        SELECT QuestionText FROM [Questions] WHERE QuestionnaireID = @QUEST_ID
        RETURN

Thanks in advance.

Upvotes: 2

Views: 6013

Answers (3)

BrokenGlass
BrokenGlass

Reputation: 160962

Using the data reader you pick up one question name only and override its value on each read - then you return that last item. Instead just use a List<string> to hold all your values and return that:

 List<string> questionNames = new List<string>();
 while (test.Read())
 { 
    questionNames.Add(GetString(0));
 }

Upvotes: 3

Haedrian
Haedrian

Reputation: 4328

while (test.Read())
        { 
           returnvalue = test.GetString(0);  
        }

There's your problem. You're looping that multiple times, each time overwriting it with the last value, then returning once.

You want a list and .add() instead.

Upvotes: 2

ipr101
ipr101

Reputation: 24236

Your reader is returning all the values but the code -

returnvalue = test.GetString(0);

will keep overwriting the returnvalue variable with the next value returned from the datareader so you will only be left with the last value.

You could create a list of strings -

List<string> list = new List<string>();

then add each value returned to the list - 

 while (test.Read())
 { 
     list.Add(test.GetString(0));  
 }

then return the list from your function instead of a string.

Upvotes: 7

Related Questions