fill datagrid from other table.

J

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.
 
J

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.
 
C

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("@Program", 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 said:
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.

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.
Click to expand...
Click to expand...
 
J

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.

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("@Program", 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 said:
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.

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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

importrow to related table 2
how to fill datagrid faster 2
Query with Rows from one table and columns from another (???) 1
getting 'real' table datasource from grandchild datagrid that is based on dataview 3
datagrid filled from a list 12
Urgent! Get rowindex - datagrid navigation 4
can I write a select ..where.. against a table in a dataset? 4
Datagrid column width won't change 2

Top