If I have a Course table and a Student table that are related by Student PK such as (Simplified):



Course1 table which has fields

PK

Course#

StudentPK

TestScore

QuizScore

DailyScore



and Student table has fields

PK

Name



I have a stored procedure that gets the following information using a parameter to get students in Algebra1:

course#,Name and TestScore



I want to put the the above info in a devexpress grid. I can get this far from the help I already got from the forum (I use a dummy table with the correct field names) put the info into a BO and then I use a BBS to attach it to the devgrid. But how does the test scores get back to the course table?



I have simplified the layout of the tables but if we can make this work then I can apply it too the project. TIA

Hi Terry,



We do this all the time. The approach we take is:



1) Create a BO on the Course1 table.



2) Create a BO on the Student table.



3) Configure each BO with a proc that extracts all the data in each table.



4) Point 3) can, of course, get more complicated but lets assume this is good enough for now.



5) In BO.Student:

> Set ChileAutoFilterOption to MatchCurrentRow (this will ensure that onlt Students in the current Course are displayed.

> Set ParentRelationship so that Student is related to Course by it PK



6) Create a form and drop a grid on to it and set its datasource to BBS.Course1. This becomes the forms PrimaryBusinessObject.



7) Drop a second grid onto the from (we normally use a split container, one panel for each grid) and set its datasource to BBS.Student.



8) In BO.Student (once its been dropped on your form) go into Properties and set ParentBusinessObject to BO.Course which is already on the form.



And you're done. A nice parent child form. The same can be done for parent/child/grandchild etc.





Cheers, Peter

Oh wow. Your SP does not bring the data together it just makes sure you have the correct data from each table which then ends up in a BO and then the BBS for the grid? Correct?



1. So what do you do if some data from both tables are to be displayed(the first grid)?

2. What do you do if you have a link table(I think that is what they are called)? Like this.

studentpk, name <-----> studentpk,coursepk <---------->coursepk,coursename,testscore,quizscore,dailyscore?



I hope #2 makes sense. It is just 3 tables related thru the middle table.

Again, thank you Peter for such a timely response and a clear response.

Hi Terry,



In that case you will three tables:



Parent:    Course



Child:      StudentCourse



GrandChild:   Student





Grid1 would be populated with Course



Grid2 would be populated with StudentCourse



There would be no need for a Student to be included as a seperate grid. The data column Student in Grid2 would be a simple DevEx lookup edit populated from the Student table and the Course data column in StudentCouse table would not even be displayed in Grid2. Straraframe will take care of the relationships and keep the Course column (in StudentCourse) correctly populated with the Course PK.



Cheers, Peter

Hi Terry,



Oops, a bit early in the morning. I've just reread my post and your question and I see I'm off the track a little bit.



In your example "testscore, quizscore, dailyscore" would have to become part of the StudentCourse table because that's what the data refers to, i.e. it isn't Course specific its StudentCourse specific.



So one grid containing the expanded StudentCourse table with the data columns Student and Course being lookup edits referencing those two tables.



Cheers, Peter

Ok I thought about that but the teachers are so accustomed to putting their cursor in a column say test column and putting in a score and hitting enter which takes them to the next student. The grid is in the same order as their grade book. Don't think I can change that thought pattern.



How about this? Create a View that will get ALL Possible Data. Then I believe I can create a BO from that View and I then I think I could use a SP that uses the View to just get the data needed at any given time with parameters. Then I could fill the BO with the SP, then use a BBS for the gird.



If that would work to get the data, would it then update the testscores?????? Or is my thoughts completely off base?

TIA.

Hi Terry,



You can create a BO from a view but not a stored procedure (as you seem to know) however SQL Server doesn't support updatable views.



I guess you could use a view and have an unbound grid and handle all the database updates manually as you move from row to row. That being said your database does need to be structured in the way I outlined.



Cheers, Peter

oh views are not updateable. ug........ so much for my idea..... The data structure is in place, we are being allowed to do a few more things with it, I hope.

I am going to have to think about this multiple grid idea. If you scroll down the course grid to enter scores how will I be able to keep it in sink with the Student Table so the names will be correct?

TIA

Hi Terry,



Don't really understand the question but what I do need to know is what tables/columns will you be working with?



Cheers, Peter

The data structure is in place from a already purchased piece of software. The company is going to let us access that data and we want to update it outside of their program because of some issues.

I will get the outline of the files and fields together and get back to you. Thanks