Solved

Passing values from a Stored Procedure to a label

Posted on 2009-04-03
8
245 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

740 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