SqlDataReader read whole query

Question

I have a SQL script with a loop. In each iteration I change the where clause. So I get several selects displayed. But my .net program reads only the first select. The SQL script works in ssms.

This is my SQL script

while (@aktuellParam <= @countParam)
Begin
  SELECT name1,
         name2
  FROM dbo.tableName
  WHERE name like @var
  SET @aktuellParam = aktuellParam+1
END

This is my vb.net code

Using reader As SqlClient.SqlDataReader = _server.ConnectionContext.ExecuteReader(script)
    Dim lfdnr As Integer = 1

    Do While reader.Read()
        spaltenListe.Add(New ISpalten With
                                 {
                                     .Name1= reader.GetString(reader.GetOrdinal("name1")),
                                     .Name2= reader.GetString(reader.GetOrdinal("name2"))
                                 })
        lfdnr = lfdnr + 1
    Loop
    reader.Close()
End Using

That's because subsequent selects are actually in a different result set. Your reader needs to do a .NextResult after each read. — UnhandledExcepSean
– UnhandledExcepSean, Commented Mar 22, 2016 at 15:04
Why the rbar? What's wrong with a single select and BETWEEN operator? — mxix
– mxix, Commented Mar 22, 2016 at 18:25
@mxix My select is more complicated than the code I posted. I give the SQL script a XML with different numbers of parameters, which are used in the where clause. — battleboimatze
– battleboimatze, Commented Mar 23, 2016 at 7:24

UnhandledExcepSean · Accepted Answer · 2016-03-23 11:20:59Z

3

That's because subsequent selects are actually in a different result set. Your reader needs to do a .NextResult after each read.

answered Mar 23, 2016 at 11:20

UnhandledExcepSean

12.9k4 gold badges38 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

SqlDataReader read whole query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related