Executing a command inside a read loop

How can I execute an SQL command inside a read loop? My code looks something
like this:
With cmd1.ExecuteReader
Do While .Read
cmd2.CommandText = "..."
Name = CStr(cmd2.ExecuteScalar)
Loop
.Close
End With
But it fails at the ExecuteScalar method, with an invalid operation
exception.ADO.NET 1 doesn't allow sending a query against a connection that you are cu
rrently using. Either
have a separate connection for cmd2, or cache the result from cmd1 immediate
and loop that cached
result.
Or, use ADO.NET 2.0, and read up on "MARS" (multiple active resultsets). You
need to set this
attribute in the connection string.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Philip Sheard" <sheardp@.myisp.com> wrote in message news:Ois4sTHZGHA.3444@.TK2MSFTNGP05.phx
.gbl...
> How can I execute an SQL command inside a read loop? My code looks somethi
ng like this:
> With cmd1.ExecuteReader
> Do While .Read
> cmd2.CommandText = "..."
> Name = CStr(cmd2.ExecuteScalar)
> Loop
> .Close
> End With
> But it fails at the ExecuteScalar method, with an invalid operation except
ion.
>|||(I know this isn't the answer that you want to hear)
I would highly recommend looking into how your query is written. You
shouldn't have to loop through things like this. If you're trying to
append a column to the result set of a stored procedure, consider using
the INSERT EXEC syntax:
CREATE TABLE #ResultSet
(
:: <column-list>
)
INSERT #ResultSet
EXEC <procedure_name>
or, if the returned column-list is nondeterministic: SELECT a.*,
b.ColumnName FROM OPENQUERY('...', 'EXEC <procedure-name>') a,
<other_table> b
-Alan
Philip Sheard wrote:
> How can I execute an SQL command inside a read loop? My code looks somethi
ng
> like this:
> With cmd1.ExecuteReader
> Do While .Read
> cmd2.CommandText = "..."
> Name = CStr(cmd2.ExecuteScalar)
> Loop
> .Close
> End With
> But it fails at the ExecuteScalar method, with an invalid operation
> exception.|||Philip Sheard (sheardp@.myisp.com) writes:
> How can I execute an SQL command inside a read loop? My code looks
> something like this:
> With cmd1.ExecuteReader
> Do While .Read
> cmd2.CommandText = "..."
> Name = CStr(cmd2.ExecuteScalar)
> Loop
> .Close
> End With
> But it fails at the ExecuteScalar method, with an invalid operation
> exception.
If you are on SQL 2005 and use ADO .Net 2.0, you can enable MARS.
However, as Alan points out, you are probably barking up the wrong
tree. Calling back to the server for every row you get, does not
bode well for performance. You should try to get that scalar with
the first result set.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx