fill datagrid from other table.

[jaYPee]

I have 1 dataset called "dataset1" that contains 2 tables called
"course" and "courseload".

in my form i have a datagrid. the datasource of this datagrid is
"dataset1" and the datamember is "courseload".

here's the fields of every table in my dataset.

"Course" table
CourseID
CourseCode
CourseDescription
Program
Year

"Courseload" table
CourseLoadID
CourseID
Grades

CourseID in course table is a primary key and CourseID in CourseLoad
table is a foreign key.

now my question is on how can i fill this datagrid w/ records from
course table based on the criteria like Program and Year.

example:
Course table
CourseID CourseCode Program Year
1 IT 1 BSIT 1
2 IT 2 BSIM 1
3 MATH 1 BSIT 2

so for example in my form i want to fill the datagrid w/ records from
course table that has Program = BSIT and Year = 1

so the datagrid now will have
CourseID Grades CourseLoadID
1
2

Note: I have already done this using MS Access but I ported my apps to
VB.NET
i use the Docmd.RunSQL command then the insert statement like this
one:

DoCmd.RunSQL "INSERT INTO CourseLoad ( CourseID) " _
& "SELECT Course.CourseID " _
& "FROM Course " _
& "WHERE Course.Program = " &
Forms![Students]![Program] & " AND Course.Year= " & Me!Year;

this is pretty simple w/ MS Access.

thanks in advance for any help.

[jaYPee]

sorry i have some error on my sample data. it should be:

example:
Course table
CourseID CourseCode Program Year
1 IT 1 BSIT 1
2 IT 2 BSIM 1
3 MATH 1 BSIT 2

so for example in my form i want to fill the datagrid w/ records from
course table that has Program = BSIT and Year = 1

so the datagrid now will have
CourseID Grades CourseLoadID
1

note: the record in datagrid will have only 1 record based on the
criteria.

On Tue, 16 Nov 2004 09:24:32 +0800, jaYPee <hi******@yahoo.com> wrote:

I have 1 dataset called "dataset1" that contains 2 tables called
"course" and "courseload".

in my form i have a datagrid. the datasource of this datagrid is
"dataset1" and the datamember is "courseload".

here's the fields of every table in my dataset.

"Course" table
CourseID
CourseCode
CourseDescription
Program
Year

"Courseload" table
CourseLoadID
CourseID
Grades

CourseID in course table is a primary key and CourseID in CourseLoad
table is a foreign key.

now my question is on how can i fill this datagrid w/ records from
course table based on the criteria like Program and Year.

example:
Course table
CourseID CourseCode Program Year
1 IT 1 BSIT 1
2 IT 2 BSIM 1
3 MATH 1 BSIT 2

so for example in my form i want to fill the datagrid w/ records from
course table that has Program = BSIT and Year = 1

so the datagrid now will have
CourseID Grades CourseLoadID
1
2

Note: I have already done this using MS Access but I ported my apps to
VB.NET
i use the Docmd.RunSQL command then the insert statement like this
one:

DoCmd.RunSQL "INSERT INTO CourseLoad ( CourseID) " _
& "SELECT Course.CourseID " _
& "FROM Course " _
& "WHERE Course.Program = " &
Forms![Students]![Program] & " AND Course.Year= " & Me!Year;

this is pretty simple w/ MS Access.

thanks in advance for any help.

[Cor Ligthert]

jaYpee,

You can do it by making an extra datatable using a select with a where
clause as you already showed.

"SELECT CourseID FROM Course WHERE Program = " _
@Program"

And then use the parameters something as
XXXdataadapter.Selectcommand.Parameters.Add("@Prog ram", MyProgram)

fill it and use that datatable as datasource
xxxDataadapter.fill(ds, "myextratable")
datagrid1.datasource = dataset.tables("myextratable")

Or you can do it with a dataview.

dim dvExtra as new dataview(mytable)
dvExtra.rowfilter = "Program = '" & myProgram & "'"

datagrid1.datasource = dvExtra

I prefer the first one.

Everything is typed in this message so watch typos or little errors.

I hope this helps?

Cor
"jaYPee" <hi******@yahoo.com>

sorry i have some error on my sample data. it should be:

example:
Course table
CourseID CourseCode Program Year
1 IT 1 BSIT 1
2 IT 2 BSIM 1
3 MATH 1 BSIT 2

so for example in my form i want to fill the datagrid w/ records from
course table that has Program = BSIT and Year = 1

so the datagrid now will have
CourseID Grades CourseLoadID
1

note: the record in datagrid will have only 1 record based on the
criteria.

On Tue, 16 Nov 2004 09:24:32 +0800, jaYPee <hi******@yahoo.com> wrote:

I have 1 dataset called "dataset1" that contains 2 tables called
"course" and "courseload".

in my form i have a datagrid. the datasource of this datagrid is
"dataset1" and the datamember is "courseload".

here's the fields of every table in my dataset.

"Course" table
CourseID
CourseCode
CourseDescription
Program
Year

"Courseload" table
CourseLoadID
CourseID
Grades

CourseID in course table is a primary key and CourseID in CourseLoad
table is a foreign key.

now my question is on how can i fill this datagrid w/ records from
course table based on the criteria like Program and Year.

example:
Course table
CourseID CourseCode Program Year
1 IT 1 BSIT 1
2 IT 2 BSIM 1
3 MATH 1 BSIT 2

so for example in my form i want to fill the datagrid w/ records from
course table that has Program = BSIT and Year = 1

so the datagrid now will have
CourseID Grades CourseLoadID
1
2

Note: I have already done this using MS Access but I ported my apps to
VB.NET
i use the Docmd.RunSQL command then the insert statement like this
one:

DoCmd.RunSQL "INSERT INTO CourseLoad ( CourseID) " _
& "SELECT Course.CourseID " _
& "FROM Course " _
& "WHERE Course.Program = " &
Forms![Students]![Program] & " AND Course.Year= " & Me!Year;

this is pretty simple w/ MS Access.

thanks in advance for any help.

[jaYPee]

Thank you very much for the reply. I just want to know how this code
works. since i'm not new to VB.NET and i'm not also an expert i don't
know if this code needs to pull the data from the sql server. and if
so how can i update then the datagrid since in my app the datasource
is dataset1 and the datamember is courseload.

On Tue, 16 Nov 2004 09:41:25 +0100, "Cor Ligthert"
<no************@planet.nl> wrote:

jaYpee,

You can do it by making an extra datatable using a select with a where
clause as you already showed.

"SELECT CourseID FROM Course WHERE Program = " _
@Program"

And then use the parameters something as
XXXdataadapter.Selectcommand.Parameters.Add("@Pro gram", MyProgram)

fill it and use that datatable as datasource
xxxDataadapter.fill(ds, "myextratable")
datagrid1.datasource = dataset.tables("myextratable")

Or you can do it with a dataview.

dim dvExtra as new dataview(mytable)
dvExtra.rowfilter = "Program = '" & myProgram & "'"

datagrid1.datasource = dvExtra

I prefer the first one.

Everything is typed in this message so watch typos or little errors.

I hope this helps?

Cor
"jaYPee" <hi******@yahoo.com>

sorry i have some error on my sample data. it should be:

example:
Course table
CourseID CourseCode Program Year
1 IT 1 BSIT 1
2 IT 2 BSIM 1
3 MATH 1 BSIT 2

so for example in my form i want to fill the datagrid w/ records from
course table that has Program = BSIT and Year = 1

so the datagrid now will have
CourseID Grades CourseLoadID
1

note: the record in datagrid will have only 1 record based on the
criteria.

On Tue, 16 Nov 2004 09:24:32 +0800, jaYPee <hi******@yahoo.com> wrote:

I have 1 dataset called "dataset1" that contains 2 tables called
"course" and "courseload".

in my form i have a datagrid. the datasource of this datagrid is
"dataset1" and the datamember is "courseload".

here's the fields of every table in my dataset.

"Course" table
CourseID
CourseCode
CourseDescription
Program
Year

"Courseload" table
CourseLoadID
CourseID
Grades

CourseID in course table is a primary key and CourseID in CourseLoad
table is a foreign key.

now my question is on how can i fill this datagrid w/ records from
course table based on the criteria like Program and Year.

example:
Course table
CourseID CourseCode Program Year
1 IT 1 BSIT 1
2 IT 2 BSIM 1
3 MATH 1 BSIT 2

so for example in my form i want to fill the datagrid w/ records from
course table that has Program = BSIT and Year = 1

so the datagrid now will have
CourseID Grades CourseLoadID
1
2

Note: I have already done this using MS Access but I ported my apps to
VB.NET
i use the Docmd.RunSQL command then the insert statement like this
one:

DoCmd.RunSQL "INSERT INTO CourseLoad ( CourseID) " _
& "SELECT Course.CourseID " _
& "FROM Course " _
& "WHERE Course.Program = " &
Forms![Students]![Program] & " AND Course.Year= " & Me!Year;

this is pretty simple w/ MS Access.

thanks in advance for any help.

[Rulin Hong]

Forget it. Your code never works if you didn't change datasource and
datamember of DataGrid.

"jaYPee" wrote:

Thank you very much for the reply. I just want to know how this code
works. since i'm not new to VB.NET and i'm not also an expert i don't
know if this code needs to pull the data from the sql server. and if
so how can i update then the datagrid since in my app the datasource
is dataset1 and the datamember is courseload.