stored procedure query question – looping

I have a stored procedure that I want to basically pull in a code/codes which correspond to people. I then convert the code to the persons actual name that I then want to send back and add to a listbox.

Right now with the code I have, it returns just one name. I know I am doing something wrong, but I am not sure if what I want is possible with a stored procedure or if I am on the right track.

my code is as follows.

 

 

The proceedure I am running is as follows...


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[GetAttySectyData] (@id as int) 
AS 
BEGIN

	DECLARE @First as varchar(200)
	DECLARE @Middle as varchar(50)
	DECLARE @Last as varchar(200)
	DECLARE @Code as varchar(200)
    	
   Select @Code = [SectyCode] From tblMain2 as M LEFT JOIN tblSectyData as S ON M.Code = S.Code WHERE M.Code = @id
        
     
   SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = @Code
   SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = @Code
   SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = @Code
   
   Select ISNULL(@First, '') + ' ' + ISNULL(@Middle, '') + ' ' + ISNULL(@Last,'')  AS FullName

END


////which is called from SqlCommand ShowTeam = new SqlCommand("Exec dbo.GetAttySectyData '" + selectedEmployee + "'  ", IntranetConnection);





To populate the listbox, I was running this. As I mentioned, I get one name.

 IntranetConnection.Close();

        IntranetConnection.Open();
        IntranetReader = ShowTeam.ExecuteReader();  

        while (IntranetReader.Read())
        {
            TeamList2.Items.Add(IntranetReader["FullName"].ToString());
        }

        IntranetConnection.Close();

 
Read More

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s