Will Swindell Will Swindell - 1 year ago 64
SQL Question

(SQL, VB.NET) How Do I select multiple values from one row and assign them to variables?

I am writing a SELECT query to use in my project. So far, I have

Dim conn As New OleDbConnection
Dim StudentID, GradeID, SubjectID As Integer
Dim YourGrade(4), YourSubject(4) As String
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =H:\Year 13 Computer Science\Project\Usernames and Passwords.accdb"


conn.Open()
Dim sql = "Select * From Grades where StudentID =" & CurrentID
Dim cmd As New OleDbCommand(sql, conn)
Dim dr As OleDbDataReader = cmd.ExecuteReader

While dr.Read
StudentID = dr("StudentID")
GradeID = dr("GradeID")
SubjectID = dr("SubjectID")
End While


My issue is that I need to be able to have a dynamic number of SubjectIDs and GradeIDs to be selected, in case a student is taking more or fewer subjects than the normal three.

My Query produces:

StudentID GradeID SubjectID
1 2 1
1 4 13
1 3 19


the CurrentID is "1" for the purposes of this.

Each GradeID and SubjectID corresponds to values in other tables which I can work on later.

I need to be able to have each of those three Grade IDs in a separate value, an array could be used but I don't know how to code it. I attempted it earlier as shown by the "YourGrade(4), YourSubject(4)".

I intend to use the data to fill out a Data Grid.

Answer Source

Create a domain object for "Student" and then load the records into a list of Student Objects.

I don't know VB.net, but the equivalent Domain object in C# would look like this:

public class Student
{
    public int StudentId { get; set; }
    public int GradeId { get; set; }
    public int SubjectId { get; set; }
}

And then the code to loop through the dataReader and populate the list:

List<Student> results = new List<Student>();

while (dr.Read())
{
    results.Add(new Student()
    {
         StudentId = Convert.ToInt32(dr["StudentID"]),
         GradeId = Convert.ToInt32(dr["GradeId"]),
         SubjectId = Convert.ToInt32(dr["SubjectId"])
    });
}

-- Edit 2/2/2017 --

Turns out there are free converters on the web. These are the VB.net equivalents to the snippets above.

Class:

Public Class Student
    Public Property StudentId() As Integer
        Get
            Return m_StudentId
        End Get
        Set
            m_StudentId = Value
        End Set
    End Property
    Private m_StudentId As Integer
    Public Property GradeId() As Integer
        Get
            Return m_GradeId
        End Get
        Set
            m_GradeId = Value
        End Set
    End Property
    Private m_GradeId As Integer
    Public Property SubjectId() As Integer
        Get
            Return m_SubjectId
        End Get
        Set
            m_SubjectId = Value
        End Set
    End Property
    Private m_SubjectId As Integer
End Class

Database Code:

Dim results As New List(Of Student)()

While dr.Read()
    results.Add(New Student() With { _
        Key .StudentId = Convert.ToInt32(dr("StudentID")), _
        Key .GradeId = Convert.ToInt32(dr("GradeId")), _
        Key .SubjectId = Convert.ToInt32(dr("SubjectId")) _
    })
End While
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download