选择并显示属于特定ID的所有行 [英] Select and display all rows belonging to a specific ID

问题描述

我有

表学生， student_subject 和 subject_bsit

      "student"
-----------------------
|studentID | FullName |
-----------------------
|1234      | John    |
|1235      | Michael |
|1236      | Bryce   |

        "subject_bsit"
-----------------------------------
|subject_id| subject_name  |grade |
-----------------------------------
|    1     | Programming   |  3   |
|    2     | Networking    |  2.5 |
|    3     | Algorithm     |  1.75|
|    4     | Physical Educ |  2   |

This is the Junction table to connect the 
two now.

       "student_subject"
----------------------------
| student_id | subject_id |
----------------------------
|   1235     |      1     |
|   1235     |      2     |
|   1235     |      3     |
|   1234     |      1     |

如您所见，表ID 1235是michael他具有三个三个主题，subject_id 1,2和3.我要做的是在文本框中而不是在datagrid视图中显示michael的所有主题名称和等级. .

As you can see the table ID 1235 is michael He has three three subjects, subject_id 1,2 and 3. What I want to do is to display all the subject name and grades of michael in textboxes, not in a datagrid view.

到目前为止，我仍然无法将其输出到文本框.这是我的示例查询

As of now I still have failed to output it to textboxes. This is my sample query

    sql = "SELECT subject_name " & _
        " FROM student_subject " & _
        " INNER JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id" & _
        " where student_subject.student_id='" & Txtbox.Text & "'"

最后一个查询中的Txtbox.text是用户输入ID号的位置.

The Txtbox.text in the last query is where the user will input the ID number.

这是我在文本框中显示数据的代码.我对如何在文本框上循环并在每个文本框上显示它没有任何想法或方法.

This is my code on displaying the data to the textbox. I don't have any idea or approach on how can i loop on the textbox and display it on each textbox.

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

    While dr.Read
        TextBox1.Text = dr.Item("subject_name").ToString
        TextBox2.Text = dr.Item("subject_name").ToString
    End While

这是我要实现的示例用户界面.非常感谢.

This is the sample User Interface of what i am trying to achieve. Thank you so much.

推荐答案

当您读取查询的结果集时，就会使用一个已知的循环.

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 
    ...
End While

While循环不断进行，直到您已读取所有行.

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 row
 End If
 If dr.Read
    ' just the second row
 End If
 If dr.Read
    ' just the third row
 End If
 ...

从您的问题中我想您的表单上有Textbox1，Textbox2，... Textbox5.我也猜你有Grade1，Grade2 ....

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
 End If
 If dr.Read
    TextBox2.Text = dr.Item("subject_name").ToString
    Grade2.Text = dr.Item("grade").ToString
 End If
 If dr.Read
    TextBox3.Text = dr.Item("subject_name").ToString
    Grade3.Text = dr.Item("grade").ToString
 End If
 ' more of these sets of four lines to fill your whole form.

这可以解决您的问题.但是您可能会注意到它是重复的，荒谬的.您真正需要的是一个文本框数组(实际上是两个数组).您在程序中创建并填写这些texbox. 我还没有调试过:这是您要做的.

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 = 200
  Dim 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 + 50
  End While

运行此命令时，将有两列控件，每个主题一列.但是这段代码很复杂，您必须先使用Something.Y = value然后使用Y = Y + 50算术来完成表单的所有布局.

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.

这篇关于选择并显示属于特定ID的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！