Solved

Passing values from a Stored Procedure to a label

Posted on 2009-04-03
8
246 Views
Last Modified: 2012-05-07
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
Comment
Question by:NorthArrow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 

Author Comment

by:NorthArrow
ID: 24061251
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
 

Author Comment

by:NorthArrow
ID: 24061279
I am using SQL SMS, running Win XP Pro using VB.Net in VS 2008.
0
 
LVL 2

Expert Comment

by:gnovakhs2n
ID: 24076050
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:NorthArrow
ID: 24076990
Thank you for your response, gnovakhs2n.

Why won't the stored procedure work?

0
 

Author Comment

by:NorthArrow
ID: 24076993
Are there any Experts -- Gurus, Genius, etc. on this forum that can help me out?
0
 
LVL 2

Accepted Solution

by:
gnovakhs2n earned 500 total points
ID: 24077050
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
 

Author Comment

by:NorthArrow
ID: 24077315
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
 

Author Closing Comment

by:NorthArrow
ID: 31582142
I never got an answer to my last question.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question