"jaYPee" wrote:

> I have no problem setting the selectcommand in sqldataadapter to fetch
> record from sqlserver w/ where clause in parent table. however, my
> problem is on how can i fetch the child table which is related from
> the parent table.
>
> I have ask this before and may be have not explained it well that's
> why i can't still get what i need.
>
> i have three tables that is related from each other.
> students table
> schyrsem table
> schyrsemcourse table
>
> i'll give some of the field for each table to give a thorough example
>
> students table consist of:
> idno
> lastname
> firstname
>
> schyrsem table
> schyrsemid
> idno
> schyr
>
> schyrsemcourse table
> schyrsemcourseid
> schyrsemid
> course
>
> as you can see STUDENTS and SCHYRSEM table is related using IDNO and
> SCHYRSEM and SCHYRSEMCOURSE table is also related using SCHYRSEMID.
>
> now in my sqldataadapter for students table i have setup the
> selectcommand using this sql "select idno, lastname, firstname from
> students where lastname = @lastname"
>
> i don't have problem retrieving record in students and schyrsem table
> but the big problem is on the third table (schyrsemcourse)
>
> students table is displayed using textboxes and schyrsem is displayed
> using a datagrid.
>
> so my sqldataadapter selectcommand statement is something like this.
> "select schyrsemid, idno, schyr from schyrsem where idno = @idno"
> and the parameter is this
> daSchYrSem.SelectCommand.Parameters("@IDNo").Value = IDNo.Text
>
> this is seems very easy w/ the 1st and 2nd table but the problem is in
> the 3rd table because i don't know how to get the value of 2nd table
> (based on a datagrid)  in order to fetch the record in 3rd table.
>
> i would be very glad if you can give me some clue or article to learn
> on.
>
> thanks in  advance.
>

>If you are running from a student last name (not the wisest, as it is not
>guaranteed unique, but good enough for our example), link everything back to
>that name.
>
>select idno, lastname, firstname from
>students where lastname = @lastname
>
>The second table:
>
>select s.schyrsemid, s.idno, s.schyr from schyrsem s
>join students st on s.idno = st.idno
>students where st.lastname = @lastname
>
>Third table:
>
>select course from schyrsemcourse c
>join schyrsem s
>on c.schyrsemid = s.schyrsemid
>join students st on s.idno = st.idno
>students where st.lastname = @lastname
>
>You now have data related to a single student. Personally, I do not like the
>database design, as it is not as normal as it should be (based on what you
>have given me).
>
>You can link the three in a single sproc and return three tables. Use
>TableMappings on the DataAdapter to give them friendly names.
>
>---
>
>Gregory A. Beamer
>MVP; MCP: +I, SE, SD, DBA
>
>***************************
>Think Outside the Box!
>***************************
>
>"jaYPee" wrote:
>
>> I have no problem setting the selectcommand in sqldataadapter to fetch
>> record from sqlserver w/ where clause in parent table. however, my
>> problem is on how can i fetch the child table which is related from
>> the parent table.
>>
>> I have ask this before and may be have not explained it well that's
>> why i can't still get what i need.
>>
>> i have three tables that is related from each other.
>> students table
>> schyrsem table
>> schyrsemcourse table
>>
>> i'll give some of the field for each table to give a thorough example
>>
>> students table consist of:
>> idno
>> lastname
>> firstname
>>
>> schyrsem table
>> schyrsemid
>> idno
>> schyr
>>
>> schyrsemcourse table
>> schyrsemcourseid
>> schyrsemid
>> course
>>
>> as you can see STUDENTS and SCHYRSEM table is related using IDNO and
>> SCHYRSEM and SCHYRSEMCOURSE table is also related using SCHYRSEMID.
>>
>> now in my sqldataadapter for students table i have setup the
>> selectcommand using this sql "select idno, lastname, firstname from
>> students where lastname = @lastname"
>>
>> i don't have problem retrieving record in students and schyrsem table
>> but the big problem is on the third table (schyrsemcourse)
>>
>> students table is displayed using textboxes and schyrsem is displayed
>> using a datagrid.
>>
>> so my sqldataadapter selectcommand statement is something like this.
>> "select schyrsemid, idno, schyr from schyrsem where idno = @idno"
>> and the parameter is this
>> daSchYrSem.SelectCommand.Parameters("@IDNo").Value = IDNo.Text
>>
>> this is seems very easy w/ the 1st and 2nd table but the problem is in
>> the 3rd table because i don't know how to get the value of 2nd table
>> (based on a datagrid)  in order to fetch the record in 3rd table.
>>
>> i would be very glad if you can give me some clue or article to learn
>> on.
>>
>> thanks in  advance.
>>