CODEWITHSUNDEEP
mysqlsql.netdatabasevb.net
Kimberypalet
Kimberypalet

Reputation: 119

How to select on junction table depending on student ID

first sorry my english is bad.

I am having a problem using junction table it only return me 1 subject this is my query.

I have a Student_tbl, Subject_tbl , student_subject_tbl the junction table.

And this is my query i am using vb.net

SELECT subject_name 
FROM student_subject 
     JOIN student ON student.StudentID = student_subject.student_id 
     JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id 
WHERE student.StudentID='" & TextBox1.Text & "'"

What i want to do is i just want to select all subject name that is belong to the student_id student id is equals to my textbox in vb.net that is where i will type the student id of student. THank you so much.

EDIT: I am trying to display the value of all subject name of the entered student id on the textbox. this is my vb.code on displaying it only display the first subject on subject_tbl

cmd = New MySqlCommand(sql, myconn)
        dr = cmd.ExecuteReader
        If dr.Read = True Then
            TextBox2.Text = dr(0)
            'Label4.Text = dr(1)
            TextBox4.Text = dr(1)
            TextBox5.Text = dr(2)
            TextBox6.Text = dr(3)
            TextBox7.Text = dr(4)
            TextBox8.Text = dr(5)
            dr.Close()

Upvotes: 4

Views: 221

Answers (1)

Hadi
Hadi

Reputation: 37368

You don't have to Join with Students table, since the Id's are stored in the junction table also and you are not retrieving student information, also use parameterized command instead of building the command manually to avoid SQL injection.

Try using the following code:

Dim strQuery as string = "SELECT subject_name " & _
" FROM student_subject " & _
" INNER JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id" & _
" WHERE student_subject.student_id = @StudentID"

Dim cmd as New MySqlCommand(strQuery,connection)

cmd.AddParameterWithValue("@StudentID",Textbox1.text)

cmd.ExecuteReader()

Update 1

The index you pass to the datareader object is the column index not the row index, also you have to use a loop instead of an if condition:

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

While dr.Read = True Then

    Msgbox(dr(0).Tostring())

End While
dr.Close()

Update 2 - passing textboxes name dynamically

First, you have to make sure that the number of textboxes is less than or equal to the number of rows returned.

cmd = New MySqlCommand(sql, myconn)
dr = cmd.ExecuteReader()
Dim cnt as Integer = 1

While dr.Read = True Then

    Dim txt As New TextBox = DirectCast(Me.Controls.Find(string.Format("Textbox{0}", cnt ),false).FirstOrDefault(),Textbox);

    txt.Text = dr(0).ToString()

    cnt += 1

End While
dr.Close()

But Why not showing results in a GridView??

Upvotes: 1

Related Questions