Solved

Passing values from a Stored Procedure to a label

Posted on 2009-04-03
8
239 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
 

Author Comment

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

Why won't the stored procedure work?

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now