7 Replies - 41034 Views - Last Post: 19 January 2009 - 06:11 AM Rate Topic: -----

#1 McMahon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 09-January 09

Filtering data in a DataSet

Posted 14 January 2009 - 09:35 AM

I have a database with a Student table and a Class table. There are many students and many classes and each student is a member of a class.

I then have a windows form that needs to display a list of student names in a listbox. However, I have a different form for each class. So for example in this form I have coded below, I need to display all students that are members of class51. The code I have so far is reading in all students from tblStudent and this is not what I want.

I know that if I was doing SQL or something I'd simply write SELECT * FROM TBLSTUDENT WHERE CLASSID = 8. What can I add to my code below to help me do this?

Your help would be greatly 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



Is This A Good Question/Topic? 0
  • +

Replies To: Filtering data in a DataSet

#2 bflosabre91  Icon User is offline

  • go sabres

Reputation: 105
  • View blog
  • Posts: 1,439
  • Joined: 22-February 08

Re: Filtering data in a DataSet

Posted 14 January 2009 - 09:52 AM

if i understand you correctly it seems like you want to filter the dataset based on what class? You can do a select from a dataset:

yourDataSet.Tables(0).Select("ClassNumber = 50")



it something like that. u will have to change it accordingly but this might push u in the right direction.
Was This Post Helpful? 0
  • +
  • -

#3 McMahon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 09-January 09

Re: Filtering data in a DataSet

Posted 14 January 2009 - 10:21 AM

View Postbflosabre91, on 14 Jan, 2009 - 08:52 AM, said:

if i understand you correctly it seems like you want to filter the dataset based on what class? You can do a select from a dataset:

yourDataSet.Tables(0).Select("ClassNumber = 50")



it something like that. u will have to change it accordingly but this might push u in the right direction.




Thanks, I think that will help. But would it just be that one line of code? And also, where would I put it? Would it go something like this:

		 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")
			objDataSet.Tables("tblStudent").Select("ClassID = 8")
			lstStudents51.Items.Add(strSurname + ", " + strFirstName)
		Next

		lstStudents51.SelectedIndex = 0


This post has been edited by McMahon: 14 January 2009 - 10:20 AM

Was This Post Helpful? 0
  • +
  • -

#4 bflosabre91  Icon User is offline

  • go sabres

Reputation: 105
  • View blog
  • Posts: 1,439
  • Joined: 22-February 08

Re: Filtering data in a DataSet

Posted 14 January 2009 - 10:27 AM

before you add the rows in the listbox you would have to declare a new dataset or declare a new datatable and set that equal to the result of the select on the original dataset. then use the new dataset or datatable as the rows that you want to add to the listbox. hope this helps.
Was This Post Helpful? 0
  • +
  • -

#5 McMahon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 09-January 09

Re: Filtering data in a DataSet

Posted 14 January 2009 - 10:42 AM

I don't really get ya...

Do you mean like;
dim objDataSet2 As New DataSet

objDataSet2 = objDataSet

objDataSet.Tables("tblStudent").Select("ClassID = 8")

Was This Post Helpful? 0
  • +
  • -

#6 bflosabre91  Icon User is offline

  • go sabres

Reputation: 105
  • View blog
  • Posts: 1,439
  • Joined: 22-February 08

Re: Filtering data in a DataSet

Posted 14 January 2009 - 11:16 AM

close:

dim objDataSet2 As New DataSet

objDataSet2 = objDataSet.Tables("tblStudent").Select("ClassID = 8")



then add the rows in objDataSet2 to the listbox.

i think this will work like that... if not post the errors u are getting and i know another more difficult way to do it...

This post has been edited by bflosabre91: 14 January 2009 - 11:17 AM

Was This Post Helpful? 0
  • +
  • -

#7 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Filtering data in a DataSet

Posted 14 January 2009 - 12:50 PM

Modified title to be more descriptive of the problem.
Was This Post Helpful? 0
  • +
  • -

#8 McMahon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 09-January 09

Re: Filtering data in a DataSet

Posted 19 January 2009 - 06:11 AM

Thanks for your help but actually, in the meantime I have made some changes to my project. I had originally intended on having a seperate windows form for each class but that would mean having 20 forms which is madness. So now I have one form where you select a class ID and the selected class ID is used to populate the dataset. However, I keep getting an error and cannot figure out what's up.
	
		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 user selects the class from a combobox and then the listbox needs to be populated with the selected class list.

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?

This post has been edited by McMahon: 19 January 2009 - 06:13 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1