How to display data (multiple rows and columns) from database to label

[ahteck]

Hello there,

I really need help ASAP.

A user has two students(maybe more) where he need to complete marking the score sheet for the students.

So, I have a score sheet where the user will insert their id in the textbox and then it will display the first studentName, studentID, studentTitle and studentProgram on the form.

Then the user will insert mark for the first student, and click button "next"

After clicking, the score sheet will display the second studentName, studentID, studentTitle and studentProgram on the form.

I only able to do up to the first student. I dont know how to read on the second row from the database.
I really need help cause I had try hard to solve the problem.

Hope to hear suggestions from the expert.

Below is some of my coding that i have done.

Imports FYPFOMS.connection
Imports MySql.Data.MySqlClient
Imports System.Data
Imports System.Web.UI

Partial Public Class extendProposal
    Inherits System.Web.UI.Page
    Private cmd As New MySqlCommand
    Private dr As MySqlDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub ButtonSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSelect.Click

        Dim sqlStr As String

        ConnectDatabase()

        sqlStr = "SELECT a.StName, s.StId, s.StTitle, s.StProgramme FROM supervisor v, allocate a, student s WHERE(v.SvName = a.SvName) AND a.StName = s.StName AND SvId = '" & TextBoxID.Text & "'"
        Dim cmd As New MySqlCommand(sqlStr, conn)

        Try

            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While dr.Read()

                LabelStudentName.Text = dr("StName").ToString
                LabelStudentName.Visible = True
                Label1.Visible = True

                LabelStudentID.Text = dr("StId").ToString
                LabelStudentID.Visible = True
                Label2.Visible = True

                LabelStudentTitle.Text = dr("StTitle").ToString
                LabelStudentTitle.Visible = True
                Label3.Visible = True

                LabelStudentProgramme.Text = dr("StProgramme").ToString
                LabelStudentProgramme.Visible = True
                Label4.Visible = True

            End While

        Catch ex As Exception
            'MsgBox.alert("Error create account " & ex.Message)

        Finally
            DisconnectDatabase()

        End Try

    End Sub

 Protected Sub ButtonNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonNext.Click
        Try

            Dim myCommand As New MySqlCommand
            Dim myAdapter As New MySqlDataAdapter
            Dim myData As New DataTable


        ?????????

[Haibo]

Dataset

[AleMonteiro]

Can you post a diagram with the used tables so we can analyse it?

[ahteck]

Hi AleMonteiro,

this is the diagram of the database:

this the the database of the student available.

this is the database of the supervisor(user) available

this is the database of the allocation between supervisor(user) with student.

I really hope you can help.

Thanks

[ahteck]

Hi Haibo,

can you generously show some coding? :)

thanks

[AleMonteiro]

ahteck,

by what I see, your query is already returning all students allocated for the supervisor.
If you debug your code, it may enter While dr.Read() more than once. This means that in the end, the labels will have the value of the last student returned by the query.

One solution to your problem is to use a DataSet so you can get the returned rows by an index. So in the first case you'll use index 0, in the second, index 1 and so on.

An example (in c#, cause I don't have any code written in VB)

DataSet        ds        = new DataSet();
SqlDataAdapter adapter;

adapter = new SqlDataAdapter( cmd );
adapter.Fill( ds );

cmd.Dispose();

adapter.Dispose();
adapter = null;

DataTable table = ds.Tables[0];

int numStudents = table.Rows.Count;

// First Student

table.Rows[0]["StName"].ToString()
table.Rows[0]["StId"].ToString()

// Second Student

table.Rows[1]["StName"].ToString()
table.Rows[1]["StId"].ToString()

This code assume that cmd is already created with the query.

[ahteck]

Hi AleMonteiro,

Yes, u were right. My code only display the last person in the table.

Thanks for the code but I really need one in vb if you have?

Because my vb doesnt recognized sqlDataAdapter and I dont know how and what else should i do now, cause dataset is the only answer to my question, but since vb doesnt define sqlDataAdapater, im stuck.

Do you know the reason and maybe ways to solve?

Big thanks for your help.

[AleMonteiro]

You are probably missing a namespace reference.

using System.Data