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();

from what i see and i think i know what you want, i notice you are using your stored procedure as if it were to return the new value for each row but you have to remember sql returns sets,  so youre code would work if you ran it like a loop but what you want is you get all your values at once, i thinik you just have to change your stored procedure but because i dont have your db i cant really test it, anyway try something like this

(first of all run your sql with a static @id and see what comes back if its one value then thats what youre going to get on the asp side too) i was going to try to give you an example but it looks liike your procedure is a little specific i dont know what dbo.[text] is  and how each column has a specific where clause to specify which part of the name it is, my point is you have to end up with a result set not 1 by 1

I understand, and you are correct.. when I run it with a statc ID.. I only get the one name still...   So I guess that is what I am confused about - actually getting it to spit out the set...

When I run that query in query analyzer, I do get the full set, so I guess I need a ForEach setup so for each id that pops up, it does the next step?

Running

right, but then after that you start doing a query on that code, which returns only one name

Hi

I guess I know what you mean now.  Actually Select S.SectyCode From tblMain2 as M LEFT JOIN tblSectyData as S ON M.Code = S.Code WHERE M.Code = @id would return a talbe,  but in your stored procedure, you have assigned [SectyCode] 's value to @Code,

   Select @Code = [SectyCode] From tblMain2 as M LEFT JOIN tblSectyData as S ON M.Code = S.Code WHERE M.Code = @id 

To fix this, you need to re-write your stord procedure.  As far as I can see, You need to join [tblMain2] ,[tblSectyData ], and [text] to accomplish this job.  Another solution is to use cursor (as you have mentioned," I guess I need a ForEach setup so for each id that pops up, it does the next step"). However, using cursor would involve some tricky steps...

Hope my suggestion helps

You could use a cursor I guess.