• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Passing values from a Stored Procedure to a label

Using the stored procedure code below, I need to pass the data retrieved from the stored procedure to display in several labels on my page.  A developer friend of mine suggested using a datareader to do this.  I need help understanding how this is done.

I've heard a few different opinions about datareader being "old" way of doing this.  However, if anyone has done this before whether using a datareader or not, I'd appreciate learning how this done.

I've already created the GUI for the data from the SP.  
USE [DATABASE_NAME]
GO
/****** Object:  StoredProcedure [dbo].[SearchOrg]    Script Date: 03/30/2009 16:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SearchOrg]
	@OrgN	varchar(50)
AS
BEGIN
	select n1.*, n2.*, n3.*, n4.* from
	(select 
		O.CC as CC_#, 
		O.name as ORGname, ISNULL(CONVERT(varchar(50),O.DOP), '**No DOP in datasource**') as DODAAC, 
		A.Line1 as StreetAddress, 
		A.City, 
		A.State, 
		A.Zip as ZipCode, 
		B.CostCenter, 
		B.CCNumber
	from 
		organization as O 
		left join building as B on O.Locationid = B.buildingid 
		left join addresses as A on B.mailingaddressid = A.addressid where 
		isnull(O.recordstatusid, 0) <> -1 
		--order by O.name
		) as n1
 
	left join
 
	(select 
		O.name as Organization_Name,
		P.FirstName,
		P.MiddleName,
		P.LastName,
		t.name as RoleName
	from
		prsnlrlt as T
		left join prsnlrls as R on T.prsnlrltid = R.prsnlrltid
		left join prsnlas P on R.prsnlid = P.prsnlid
		left join org as O on R.orgid = O.orgid where
		T.name like '%Director%') as n2
 
	on n1.ORGname = n2.Organization_Name
 
	left join
 
	(select 
		O.name as Organization_Name,
		P.FirstName,
		P.MiddleName,
		P.LastName,
		t.name as RoleName
	from
		prsnlrlt as T
		left join prsnlrls as R on T.prsnlrltid = R.prsnlrltid
		left join prsnlas P on R.prsnlid = P.prsnlid
		left join org as O on R.orgid = O.orgid where
		T.name like 'Asset%') as n3
 
	on n1.ORGname = n3.Organization_Name
 
	left join
 
	(select 
		O.name as Organization_Name,
		P.FirstName,
		P.MiddleName,
		P.LastName,
		t.name as RoleName
	from
		prsnlrlt as T
		left join prsnlrls as R on T.prsnlrlsid = R.prsnlrlsid
		left join prsnl as P on R.prsnlid = P.prsnlid
		left join org as O on R.orgid = O.orgid where
		T.name like '%Inventory%') as n4
 
	on n1.ORGname = n4.Organization_Name
	WHERE Org_Name = @OrgN	
 
	order by n1.ORGname 
End

Open in new window

0
NorthArrow
Asked:
NorthArrow
  • 6
  • 2
1 Solution
 
NorthArrowAuthor Commented:
I've already created the GUI for the data from the SP.  Code below:
<table width="760" border="0" cellpadding="0" cellspacing="0">
        <tr>
        <td class="labelTitle">Organization</td>
        <td class="labelTitle" colspan="3">DODAAC</td>
        </tr>
        
        <tr>
        <td><asp:Label ID="lbl_OrganizationName" runat="server" Text=""></asp:Label></td>
        <td colspan="3"><asp:Label ID="lbl_DOP" runat="server" Text=""></asp:Label></td>
        </tr>
        
        <tr>
        <td colspan="4">&nbsp;</td>
        </tr>
        
        <tr>
        <td class="labelTitle">Street Address</td>
        <td class="labelTitle">City</td>
        <td class="labelTitle">State</td>
        <td class="labelTitle">Zip Code</td>
        </tr>
        
        
        <tr>
        <td><asp:Label ID="lbl_StreetAddress" runat="server" Text=""></asp:Label></td>
        <td><asp:Label ID="lbl_City" runat="server" Text=""></asp:Label></td>
        <td><asp:Label ID="lbl_State" runat="server" Text=""></asp:Label></td>
        <td><asp:Label ID="lbl_ZipCode" runat="server" Text=""></asp:Label></td>
        </tr>
        
        <tr>
        <td colspan="4">&nbsp;</td>
        </tr>
        
        <tr>
        <td class="labelTitle">Cost Center</td>
        <td class="labelTitle">Inventory</td>
        <td class="labelTitle">Inventory Contact Name</td>
        <td class="labelTitle">Director</td>
        </tr>
        
        <tr>
        <td><asp:Label ID="lbl_CostCenter" runat="server" Text=""></asp:Label></td>
        <td><asp:Label ID="lbl_Inventory" runat="server" Text=""></asp:Label></td>
        <td><asp:Label ID="lbl_InventoryContact" runat="server" Text=""></asp:Label></td>
        <td><asp:Label ID="lbl_Director" runat="server" Text=""></asp:Label></td>
        </tr>       
        </table>

Open in new window

0
 
NorthArrowAuthor Commented:
I am using SQL SMS, running Win XP Pro using VB.Net in VS 2008.
0
 
gnovakhs2nCommented:
i do not think that the stored procedure approach will work with what you want to do here ... what you need here is in my opinion a view!
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
NorthArrowAuthor Commented:
Thank you for your response, gnovakhs2n.

Why won't the stored procedure work?

0
 
NorthArrowAuthor Commented:
Are there any Experts -- Gurus, Genius, etc. on this forum that can help me out?
0
 
gnovakhs2nCommented:
The stored procedure won't work here for several reasons because

you
a) want to select quite a lot of columns (n1.*, n2.*, n3.*, n4.* ) from the tables, but you do not return any values from the stored procedure
b) might have many result rows in your select in the stored procedure, a SP is not the perfect way to retrieve data from multiple rows

You should put the select in the stored procedure in a view and query this view from your GUI
0
 
NorthArrowAuthor Commented:
Thank you, for your response. Your suggestiong does sound simpler, and I'm not sure why the SQL developer suggested I use SP in the way I described.

Here's the thing....one sql developer wrote the queries, and another sql developer put the query/ies in a stored procedure. I'm not sure why it was done this way (SP), but this is the way I was given to access the data (via SP). I will try to find with the Sql developer who created the SP to see if he can do what you suggest here:
"You should put the select in the stored procedure in a view and query this view from your GUI"
Since I do not code SQL well, and I'm just revisiting my .Net skills from three years ago (using VS 2005) --  I'm out of practice and I'm approaching .Net as a brand new "student" so I appreciate all the help I can get.  :)
How would I query the view from the GUI?
 
0
 
NorthArrowAuthor Commented:
I never got an answer to my last question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now