retrieve records from sqlserver w/ where clause

[jaYPee]

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.

[Cowboy (Gregory A. Beamer) - MVP]

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.

[jaYPee]

WOW!

Thank you thank you very much. I don't know how to thank you but I
really appreciate your help. It works!

I am also open to your suggestion on how can I normalize my table.
From now on I'm relying to my database design.

Hope to hear from you soon about your suggestion.

Thank you once again.
On Thu, 9 Dec 2004 11:18:55 -0800, "Cowboy (Gregory A. Beamer) - MVP"
<No************@comcast.netNoSpamM> wrote:

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.