Using results of a SqlDataReader in a SQL query

Question

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

Steve · Accepted Answer · 2013-04-22 11:46:54Z

1

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

edited Apr 22, 2013 at 11:46

answered Apr 18, 2013 at 14:37

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

d99 Over a year ago

Thanks for answer and sorry for the late reply, haven't had a chance to have a look at the code again until now. I get the error "System.IndexOutOfRangeException: course_names" when I try and run the code, how do I fix this?

Steve Over a year ago

"course_names" should be the name of a field from the first execute_reader. Are you sure that it exists?

d99 Over a year ago

I have a field "course name" (without the S) in the command if thats what you mean but changing courses to course still gives the same error. Here is the first command:

d99 Over a year ago

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

Steve Over a year ago

Why don't you update the code in your question? It is better because in comments it is really difficult to spot syntax errors or other glitches

|

Collectives™ on Stack Overflow

Using results of a SqlDataReader in a SQL query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related