Solved

Stored Procedure

Posted on 2011-02-22
9
472 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
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!

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

630 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