select * from tblStudent where classID = 8 ??

[Laura]

Hi,

I have a database with a Student table and a Class table. A number of
students are members of a particular class.

On my windows form I need to display student names in a listbox. I
then have a different form for each class. So for example (as per code
below) this form is for Class51. The code I have so far is reading in
all student names into the listbox. I need it to read in the names of
those students in Class51.

Each class has a classID. I know in SQL I would simply say SELECT *
FROM STUDENT WHERE CLASSID = 8, but what do I need to add to my code
here.

Any help would be really appreciated.

Private Sub frmAttendace51_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load

'clear data set of any existing data
objDataSet.Clear()
'fill schema
objStudentDA.FillSchema(objDataSet, SchemaType.Source,
"tblStudent")
'fill data set with info from data adapter
objStudentDA.Fill(objDataSet, "tblStudent")

'fill the data set with info from tblClass
objClassDA.FillSchema(objDataSet, SchemaType.Source,
"tblClass")
objClassDA.Fill(objDataSet, "tblClass")

'fill the data set with info from tblClass
objAttendanceDA.FillSchema(objDataSet, SchemaType.Source,
"tblAttendance")
objAttendanceDA.Fill(objDataSet, "tblAttendance")

'setup the relationship
objDataSet.Relations.Clear()
objDataSet.Relations.Add("Class2Student", _
objDataSet.Tables("tblClass").Columns
("ClassID"), _
objDataSet.Tables
("tblStudent").Columns("ClassID"))
objDataSet.Relations.Add("Student2Attendance", _
objDataSet.Tables
("tblStudent").Columns("StudentID"), _
objDataSet.Tables
("tblAttendance").Columns("StudentID"))

lstStudents51.Items.Clear()



Dim i As Integer
Dim strSurname As String
Dim strFirstName As String
For i = 1 To objDataSet.Tables("tblStudent").Rows.Count
strSurname = objDataSet.Tables("tblStudent").Rows(i -
1).Item("SName")
strFirstName = objDataSet.Tables("tblStudent").Rows(i -
1).Item("FName")
lstStudents51.Items.Add(strSurname + ", " + strFirstName)
Next

lstStudents51.SelectedIndex = 0

[The_Fruitman]

Please tell me you aren't building a separate form for each class.
This is a bad design and should be avoided at all costs because of
several issues (these are the ones that quickly come to mind):
1. What happens when a new class is added? Will you have to build
another form?
2. What happens if a class has been removed? Do you need to remove or
hide the form?
3. Having each class as a separate form means that you have duplicate
code all over the place, this is also something that should be
avoided. For one reason, if you have to make modifications to the
code, you have to change it in multiple areas.

If you must display the listing of students for a selected class in
another form there are other ways to do it. For example you may pass
the selected class id to the attendance form. This would allow you to
use the same form for all your classes (a much better design). On the
form load of the attendance form you would grab the passed in class id
and use it as a parameter in your sql statement to populate your
dataset. From there you can then do your adding of information from
the dataset to the listbox or whatever other controls you wish to
populate witih the information.

[Cerebrus]

Laura,

I agree with "The_Fruitman" on this... Your design appears to be so
deeply flawed (it violates the very tenets of programming) that it
seems that your actual problem at hand is insignificant in comparison.
I seriously hope we have misunderstood you and you are not using a
separate form for each class... sounds like there are atleast 51 of
these classes.

> >         lstStudents51.SelectedIndex = 0- Hide quoted text -
>
> - Show quoted text -

[Laura]

I have now made changes to my project. Now I have one form where you
select a class from a combobox and then the listbox needs to be
populated with the students from the selected class. However, I keep
getting an error and cannot figure out what's up ( my coding abilities
are not the strongest - as you may have noticed).

Public Sub FillStudentList()
Dim j As Integer

Dim strSurname As String
Dim strFirstName As String

For j = 1 To objDataSet.Tables("tblStudent").Rows.Count
objRow = objDataSet.Tables("tblStudent").Rows.Find
(cboClassSelect.SelectedValue.ToString)
strSurname = objDataSet.Tables("tblStudent").Rows(j -
1).Item("SName")
strFirstName = objDataSet.Tables("tblStudent").Rows(j -
1).Item("FName")
lstStudents51.Items.Add(objRow.Item(strSurname + ", " +
strFirstName))
Next

lstStudents51.SelectedIndex = 0

End Sub

The error is : "Column 'Clancy, Martina' does not belong to the table
tblStudent"
The column the error refers to is the first column in tblStudent. My
database is called tblStudent and its not a spelling mistake or
anything.
Can you help with this little error please?

[Laura]

Thanks for that. I WAS actually planning on creating a new form for
each class. I can see now that that was madness.

[Cerebrus]

No, it certainly isn't a spelling mistake. And the error is telling
you exactly where the problem lies... ;-)

The DataRow class exposes its Columns in the Row by means of the
Indexer, i.e., the default Item property. This means that you can
retrieve the data within any column in a given DataRow by the
following syntax (in addition to various overloads) :
---
Dim myData as Object = myDataRow.Item("MyColumnName")
---

And the relevant part of your code is :
---
objRow.Item(strSurname + ", " + strFirstName)
---

What your code is trying to do is to find the data in the column named
"Clancy, Martina" because that parameter is meant for the column name.
It appears to me that you should simply write the following statement
because you already have the firstName and lastName values in
variables:
---

lstStudents51.Items.Add(strSurname + ", " + strFirstName)

---

P.S.: Since you are at the learning stage, I won't tell you that the
first line in your For-loop should probably be outside the loop (if
not removed altogether) because it serves no purpose within. Or maybe
I will ! ;-)

HTH.

> > > - Show quoted text -- Hide quoted text -

[Cerebrus]

Ritesh,

Since you do not have the basic decency to post in your own thread,
you cannot get help here. Please treat this as your final warning !

Post removed because of thread hijacking attempt.

--
Cerebrus.
Group Moderator.

On Jan 19, 8:34 pm, ritesh sharma <ritesh.281...@gmail.com> wrote:
> Hello all,
>
> I need help from you all and want to know why this error comes..

[Laura]

That first line in the For-Loop was there because on the form the user
makes a selection from a combobox (cboClassSelect) and then once that
selection has been made, the listbox will fill up with all the names
of students who are in that selected class.

I had been doing what you suggested previously but that simply
displays ALL student names whereas I need it to display student names
by class - by the class the user selects in the combobox.

How can I go about this? Was I anywhere close with that line of code :

objRow = objDataSet.Tables("tblStudent").Rows.Find

(cboClassSelect.SelectedValue.ToString)?

[Cerebrus]

Comments inline...

On Jan 20, 3:06 pm, Laura <mcmahon.la...@gmail.com> wrote:

> That first line in the For-Loop was there because on the form the user
> makes a selection from a combobox (cboClassSelect) and then once that
> selection has been made, the listbox will fill up with all the names
> of students who are in that selected class.

Yes, but my point was that the user's selection remains the same
throughout the life of your For-loop, therefore it makes no sense to
use it *within* the For-Loop. I also suggested removing the line
altogether because of two reasons -
a) My suggested code in my previous post does not render the statement
useless, because it is already futile. It does not help your code to
find Students matching the given class.
b) The Find() method of the DataTable only returns Rows that match the
Primary Key of the Table. ClassID is not the Primary Key in the
Student table, so theoretically this method should not return anything
(unless you have similar ID's for Student and Class)

> I had been doing what you suggested previously but that simply
> displays ALL student names whereas I need it to display student names
> by class - by the class the user selects in the combobox.

It is my mistake as well... I did not entirely grasp what your
intentions were for running the For-loop (Reading so many different
snippets of code makes one capable of cutting through the supposedly
unimportant stuff). I was entirely concentrating on the reason for
your little error. I now realize that you're simply trying to iterate
through the Students table trying to find those records that match the
ClassID.

> How can I go about this? Was I anywhere close with that line of code :
> objRow = objDataSet.Tables("tblStudent").Rows.Find
> (cboClassSelect.SelectedValue.ToString)?

Let's take another look at this from the beginning (We'll ignore the
Attendance table for now)... Here is what you are presently doing (or
trying to do) :
1. Create a Dataset with two tables - Class and Students.
2. Create a DataRelation between them, with a 1-Many relationship
between Class and Student, respectively. Also note that this Relation
is not used further so creating it is ineffectual.
3. Get a Selected ClassID based on a selection in a ComboBox.
4. Iterate through the Students table and for each record, add the
first name and the surname of the student. There is presently no code
to match the Students that have the selected ClassID. The only
statement that provides a semblance of this match is the one we've
been discussing :

> objRow = objDataSet.Tables("tblStudent").Rows.Find(cboClassSelect.SelectedValue.ToString)

But it does not work, as I have explained above. Okay, enough, let's
go on to what you should be doing :
1. Create a Dataset with two tables - Class and Students.
2. Create a DataRelation between them, with a 1-Many relationship
between Class and Student, respectively.
3. Get a Selected ClassID based on a selection in a ComboBox.
4. *USE the DataRelation* you created above to find Rows in the
Student table which match the selected ClassID. This operation will
return an array of DataRows from the Student table.
5. Iterate through the array and for each record, add the first name
and the surname in the current Student record.

Now the way to perform Step 4 and 5 is illustrated below : (Typed in
here, watch for syntax)
---
Dim selClass as String = cboClassSelect.SelectedValue.ToString()

'Find the row in the Class table that matches this ID. In this case we
can use the Find() method.
Dim classRow as DataRow = objDataSet.Tables("tblClass").Rows.Find
(selClass)

If classRow isnot Nothing then
'Use the DataRelation to find all matching Student Rows.
Dim studentRows() as DataRow = classRow.GetChildRows
("Class2Student")
If studentRows.Length > 0 then
For each student as DataRow in studentRows
Dim sName as string = student("SName")
Dim fName as string = student("FName")

'Add this information to the ListBox
lstStudents51.Items.Add(sName + ", " + fName)
Next
Else
'This class does not have any students. Must be Plant
Biochemistry ! ;-)
End If
Else
'This Class does not exist in the Class table.
End If
---

[Laura]

That was a brilliant help - thanks a million, it worked perfectly.
But just one more thing, When i select a class from the combobox it
fills the list box fine, but if I select a different class, that class
list then fills in on top of the last class in the listbox. I spose
what I need is for the listbox to refresh on each selection.

Is that another if statement or is it some simply line of code?

Thanks

> > objRow = objDataSet.Tables("tblStudent").Rows.Find(cboClassSelect.SelectedValue.ToSt­ring)

[Cerebrus]

I think you could simply clear all the items in the ListBox at the
start of your procedure.
---
lstStudents.Items.Clear()
---

...And you're welcome ! ;-)