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
Solved

Passing values from a Stored Procedure to a label

Posted on 2009-04-03
8
243 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
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…

808 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