CODEWITHSUNDEEP
sqlsql-servervb.net
d99
d99

Reputation: 37

Using results of a SqlDataReader in a SQL query

I would like to get the results of my reader to be able to be used in a WHERE clause in another SQL command. I have tried to put the reader into a variable to use but it does not work. I've never used readers before so I do not know how they work. Can anyone give me an idea of how to get the result of the reader into there where statement? Thanks.

The code:

        Dim courseSelectCom = New SqlCommand("select stuff((select ','+course_name from course where school= '" & schoolSelect & "' for xml path ('')),  1, 1, '')", connection)

        Dim reader = courseSelectCom.ExecuteReader()
        If reader.Read() Then
            Dim courseVar As String
            courseVar = "%" & reader("course_name") & "%"

            Using totalStudentCom = New SqlCommand("SELECT COUNT(student_ID) FROM student " & "course_name like @course", connection)
                totalStudentCom.Parameters.AddWithValue("@course", courseVar)
                Dim result = totalStudentCom.ExecuteScalar()
                 MessageBox.Show("Students for course = " & result.ToString)
            End Using
        End If

Upvotes: 0

Views: 438

Answers (1)

Steve
Steve

Reputation: 216293

There are a couple of problems here.
First you should use parametrized query and not string concatenations for building sql commands
Second when a DataReader is opened, the connection object cannot serve another command unless you have specified the substring MultipleActiveResultSets=True; in your connection string

Dim courseSelectCom = New SqlCommand("select course_name from course where school= @school", connection)
courseSelectCom.Parameters.AddWithValue("@school", schoolSelect)
Dim reader = courseSelectCom.ExecuteReader()
if reader.Read() then
    Dim courseVar As String
    courseVar = "%" & reader("course_name") & "%"

    Using totalStudentCom = new SqlCommand("SELECT COUNT(student_ID) FROM student " & _
                                "course_name like @course", connection)
        totalStudentCom.Parameters.AddWithValue("@course", courseVar)
        Dim result = totalStudentCom.ExecuteScalar()
        MessageBox.Show("Students for course = " & result.ToString)
    End Using
End If

Remember that this works only if MARS is enabled

Upvotes: 1

Related Questions