Select And Display All Rows Belonging To A Specific Id

I have Table student, student_subject and subject_bsit 'student' ----------------------- |studentID | FullName | ----------------------- |1234 | John | |1235 |

Solution 1:

When you read a query's resultset, you use a loop as you know.

While dr.Read
    ' run this for every row in your resultset 
    ...
EndWhile

The While loop keeps going until you have read all the rows.

You don't have to use a loop. If you wish you can read the rows one at a time, like this

If dr.Read
    ' just the first rowEndIfIf dr.Read
    ' just the second rowEndIfIf dr.Read
    ' just the third rowEndIf
 ...

From your question I guess you have Textbox1, Textbox2, ... Textbox5 on your form. I also guess you have Grade1, Grade2 ....

To handle both of the subject name and grade, change the first line of your query to

sql = "SELECT subject_name, grade " & _

You can populate those items like this:

If dr.Read
    TextBox1.Text = dr.Item("subject_name").ToString
    Grade1.Text = dr.Item("grade").ToString
 EndIfIf dr.Read
    TextBox2.Text = dr.Item("subject_name").ToString
    Grade2.Text = dr.Item("grade").ToString
 EndIfIf dr.Read
    TextBox3.Text = dr.Item("subject_name").ToString
    Grade3.Text = dr.Item("grade").ToString
 EndIf' more of these sets of four lines to fill your whole form.

This solves your problem. But you probably notice it is absurdly repetitive. What you really need is an array (actually two arrays) of textboxes. You create, and then fill in, these texboxes in your program. I have not debugged this: that is for you do to.

Dim Subjects As Textbox()
  Dim Grades As Textbox()
  ...

  Dim rownumber, Y
  rownumber = 0
  Y = 200Dim Subject
  Dim Grade
  While dr.Read
    Subject = New Textbox
    Subject.Text = dr.Item("subject_name").ToString
    Subject.Width = 200
    Subject.Height = 40
    Subject.X = 175
    Subject.Y = Y
    Subjects(rownumber) = Subject
    Form.Controls.Add(Subject)
    Grade = New Textbox
    Grade.Text = dr.Item("grade").ToString
    Grade.Width = 50
    Grade.Height = 40
    Grade.X = 400
    Grade.Y = Y
    Grades(rownumber) = Grade
    Form.Controls.Add(Grade)
    rownumber = rownumber + 1
    Y = Y + 50EndWhile

When this runs you will have two columns of controls, one for each subject. But this code is complex, and you have to do all the layout of your form with Something.Y = value and then Y = Y + 50 arithmetic.

That's why grid controls exist. They take care of that kind of thing.

Solution 2:

If you are looking to create Textboxes dynamically then you should refer to the @OJones answer

You can simply loop over Me.Controls.OfType(Of TextBox)()

cmd = New MySqlCommand(sql, myconn)
dr = cmd.ExecuteReader

While dr.Read
    ForEach txt As TextBox InMe.Controls.OfType(Of TextBox)()
        txt.Text = dr.Item("subject_name").ToString
    NextEndWhile

Or you can do a similar approach if you need to fill the first subjects name inside the textboxes (if returned subjects are more than textboxes additional subjects will be ignored):

While dr.Read = TrueDim txt AsNew TextBox = DirectCast(Me.Controls.Find(string.Format("Textbox{0}", cnt ),false).FirstOrDefault(),Textbox);

    IfNot txt IsNothingThen txt.Text = dr.Item("subject_name").ToString

    cnt += 1EndWhile
dr.Close()