Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored Procedure

Posted on 2011-02-22
9
Medium Priority
?
477 Views
Last Modified: 2012-06-21
I am still pretty new at writing my own stored procedure.  I am creating and new program and I have to pull some data from different tables and I am not sure how of if this can be done.  The attached file shows a list of fields that are already populated in different tables.  Now what is being asked of me is to bring them together without having to combine these tables into one table.  I needs a store procedure that will let this happen, so that if I go to this tab these fields will already have the information in there and if it doesn't then we are able to update this information.  No sure if this make sense.
Store-Procedure.docx
0
Comment
Question by:TCHGirl
[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
9 Comments
 
LVL 3

Expert Comment

by:DFW_Ed
ID: 34955899
don't think i'm understanding the question fully.  Are you needing the procedure to just join the tables and produce the information you need?  like this:

select eslc.Edcm,
y.Ythcm,
cmjc.Cjcm,
nec.Betscm,
nec.Ritecm,
nec.Tagcm,
nec.Empcm,
nci.Bets1,
nci.Cj1,
nci.Youth1,
nci.Rite1,
nci.Tag1,
nci.Education1,
nci.Bridge1

from ESL_Client eslc
join Youth y on eslc.tchid = y.tchid
join Cmjclient cmjc on cmjc.tchid = y.tchid
join newEmployClient nec on nec.tchid = y.tchid
join NewClientInfo nci on nci.tchid = y.tchid

or do you need something based on parameters provided by a user?

could also make a view if this is something you're not going to use to update tables
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34955922
Exactly what are you trying to do.

You have to be specific about about your requirements
0
 
LVL 40

Expert Comment

by:lcohan
ID: 34956062
You should be able to do that with a data grid - just check "Database operations in DatagridView" at the link below and one advice - if you work with stored procedures do not prefix them "sp_...." like Microsoft system objects.

http://vb.net-informations.com/datagridview/vb.net_datagridview_tutorial.htm


Other than that you could easily create a SQL View instead to sqlqct data from all the tables/columns joined by common "tchid" and use that in a data grid.
0
The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

 

Author Comment

by:TCHGirl
ID: 34956435
dfw ed.  This is not based on parameters.

it based on what I hope to be a simple insert and update procedure.  See attached on how I would like to have this stored procedure work
Intake.docx
0
 
LVL 3

Expert Comment

by:DFW_Ed
ID: 34956562
so are you wanting the education case manager to appear regardless of the tab used and whether or not the check box for that additional tab is marked?  The attachment shows 7 check boxes from left-to-right "Bets" thru "Bridge" but only "Education" is marked.  Once marked with "Education" you want the case manager to show in the other tabs?  Does it currently show in the other tabes but not the "Bridge"?
0
 

Author Comment

by:TCHGirl
ID: 34956722
The two are not exclusive.  it shows on the first tab because that is where it is stored in the table.  So once I input the information onto the Intake tab, it has been entered there and in the table that holds the information.  Now when I open the bridge tab I want any data that shows on this screen to self populate with the information that  has already been entered somewhere else.  This is really hard to explain.

For example: I am in the education department and I enter all the education information in the intake tab. Somebody else entered other information into a youth program that has the same check boxes on their intake tab and has a different case manager field.  So when I open up my bridge tab any information that was entered on the youth  tab ie youth case manager, youth check box will show up on my bridge tab.  This way I don't have to go to the youth page to look this information up.

Does this help?
0
 
LVL 3

Expert Comment

by:DFW_Ed
ID: 34961505
so the bridge tab should show all info that has been input in the other forms?  
0
 

Author Comment

by:TCHGirl
ID: 34961517
that is correct
0
 
LVL 3

Accepted Solution

by:
DFW_Ed earned 2000 total points
ID: 34962672
you may want to consider using a view since you can modify information using a view, i would test results though to be sure the inserts and updates are happening as you expect.

as for a stored procedure, to display the info would be easy enough, to display and also perform inserts/updates to any changed fields is possible but i'd recommend having a separate proc for insert and one for update, but that's just a personal preference.  IFor a single proc use 'd look at using MERGE in the procedure for the insert/update portion:

http://msdn.microsoft.com/en-us/library/bb510625.aspx

the join structure below should be sufficient for collecting the data you need:

from ESL_Client eslc
left join Youth y on eslc.tchid = y.tchid
left join Cmjclient cmjc on cmjc.tchid = y.tchid
left join newEmployClient nec on nec.tchid = y.tchid
left join NewClientInfo nci on nci.tchid = y.tchid


0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Six Sigma Control Plans
Starting up a Project

704 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