Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Passing values from a Stored Procedure to a label

Posted on 2009-04-03
8
Medium Priority
?
253 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 1500 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

581 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