Hello,

How can I use an ExecuteNonQuery within the loop of a datareader?
My code is as follows:

Dim cmdToProcess As New SqlClient.SqlCommand
Dim rdToProcess As SqlClient.SqlDataReader

With cmdToProcess
   .CommandText = " SELECT * FROM tblDetails "
   .Connection = cn
End With

rdToProcess = cmdToProcess .ExecuteReader

Select Case rdToProcess
   Case True
      Do
         'here I want to add or update data
         Dim cmdToChange As New SqlClient.SqlCommand

         With cmdToChange
            .CommandText = "INSERT INTO tblNew(AD_Action) VALUES (@Action)"
            .Parameters.AddWithValue("@Action", rdToProcess("DE_Number"))
	    .Connection = cn
         End With

         cmdToChange.ExecuteNonQuery
      Loop until not rdProcess.Read
End Select

At "cmdNieuweCafetariaActiedetail.ExecuteNonQuery", I get the message that I 
have to close the first datareader in order to 

execute, but then my code cannot proceed because rdToProcess("DE_Number") 
can no longer be read.

How can I solve this?

Many thanks and greetings for your help,

Michel

Michel,

Try running the ExecuteNonQuery on a connection different than the one being 
used by the data reader.

Kerry Moorman


"Michel" wrote:


> Hello,
> 
> How can I use an ExecuteNonQuery within the loop of a datareader?
> My code is as follows:
> 
> Dim cmdToProcess As New SqlClient.SqlCommand
> Dim rdToProcess As SqlClient.SqlDataReader
> 
> With cmdToProcess
>    .CommandText = " SELECT * FROM tblDetails "
>    .Connection = cn
> End With
> 
> rdToProcess = cmdToProcess .ExecuteReader
> 
> Select Case rdToProcess
>    Case True
>       Do
>          'here I want to add or update data
>          Dim cmdToChange As New SqlClient.SqlCommand
> 
>          With cmdToChange
>             .CommandText = "INSERT INTO tblNew(AD_Action) VALUES (@Action)"
>             .Parameters.AddWithValue("@Action", rdToProcess("DE_Number"))
> 	    .Connection = cn
>          End With
> 
>          cmdToChange.ExecuteNonQuery
>       Loop until not rdProcess.Read
> End Select
> 
> At "cmdNieuweCafetariaActiedetail.ExecuteNonQuery", I get the message that I 
> have to close the first datareader in order to 
> 
> execute, but then my code cannot proceed because rdToProcess("DE_Number") 
> can no longer be read.
> 
> How can I solve this?
> 
> Many thanks and greetings for your help,
> 
> Michel
> 

As Kerry mentioned, use another conneciton instance.
There is another option with Sql Server - MARS. Here is a post that explains 
it:
http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx

-- 
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Michel"  wrote in message 
news:F33A47F6-E125-4EA6-AD7A-D1D6E115E906@microsoft.com...

> Hello,
>
> How can I use an ExecuteNonQuery within the loop of a datareader?
> My code is as follows:
>
> Dim cmdToProcess As New SqlClient.SqlCommand
> Dim rdToProcess As SqlClient.SqlDataReader
>
> With cmdToProcess
>   .CommandText = " SELECT * FROM tblDetails "
>   .Connection = cn
> End With
>
> rdToProcess = cmdToProcess .ExecuteReader
>
> Select Case rdToProcess
>   Case True
>      Do
>         'here I want to add or update data
>         Dim cmdToChange As New SqlClient.SqlCommand
>
>         With cmdToChange
>            .CommandText = "INSERT INTO tblNew(AD_Action) VALUES (@Action)"
>            .Parameters.AddWithValue("@Action", rdToProcess("DE_Number"))
>     .Connection = cn
>         End With
>
>         cmdToChange.ExecuteNonQuery
>      Loop until not rdProcess.Read
> End Select
>
> At "cmdNieuweCafetariaActiedetail.ExecuteNonQuery", I get the message that 
> I
> have to close the first datareader in order to
>
> execute, but then my code cannot proceed because rdToProcess("DE_Number")
> can no longer be read.
>
> How can I solve this?
>
> Many thanks and greetings for your help,
>
> Michel
> 

Wait a sec. While it's possible to use MARS and such, it's silly to pull the 
rows down from the system to build an INSERT statement. Even JET can execute 
a INSERT based on the values supplied with a SELECT or an expression or 
both. Do all of this work on the server with the database engine. That's 
what its for.

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Michel"  wrote in message 
news:F33A47F6-E125-4EA6-AD7A-D1D6E115E906@microsoft.com...

> Hello,
>
> How can I use an ExecuteNonQuery within the loop of a datareader?
> My code is as follows:
>
> Dim cmdToProcess As New SqlClient.SqlCommand
> Dim rdToProcess As SqlClient.SqlDataReader
>
> With cmdToProcess
>   .CommandText = " SELECT * FROM tblDetails "
>   .Connection = cn
> End With
>
> rdToProcess = cmdToProcess .ExecuteReader
>
> Select Case rdToProcess
>   Case True
>      Do
>         'here I want to add or update data
>         Dim cmdToChange As New SqlClient.SqlCommand
>
>         With cmdToChange
>            .CommandText = "INSERT INTO tblNew(AD_Action) VALUES (@Action)"
>            .Parameters.AddWithValue("@Action", rdToProcess("DE_Number"))
>     .Connection = cn
>         End With
>
>         cmdToChange.ExecuteNonQuery
>      Loop until not rdProcess.Read
> End Select
>
> At "cmdNieuweCafetariaActiedetail.ExecuteNonQuery", I get the message that 
> I
> have to close the first datareader in order to
>
> execute, but then my code cannot proceed because rdToProcess("DE_Number")
> can no longer be read.
>
> How can I solve this?
>
> Many thanks and greetings for your help,
>
> Michel
> 

I agree. Michel, google "select into" to learn how to insert data directly 
from one table to another.


"William Vaughn"  wrote in message 
news:FEA52168-D83A-4177-A8B9-A41DF9D4BD4D@microsoft.com...

> Wait a sec. While it's possible to use MARS and such, it's silly to pull 
> the rows down from the system to build an INSERT statement. Even JET can 
> execute a INSERT based on the values supplied with a SELECT or an 
> expression or both. Do all of this work on the server with the database 
> engine. That's what its for.
>
> -- 
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no 
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Michel"  wrote in message 
> news:F33A47F6-E125-4EA6-AD7A-D1D6E115E906@microsoft.com...
>> Hello,
>>
>> How can I use an ExecuteNonQuery within the loop of a datareader?
>> My code is as follows:
>>
>> Dim cmdToProcess As New SqlClient.SqlCommand
>> Dim rdToProcess As SqlClient.SqlDataReader
>>
>> With cmdToProcess
>>   .CommandText = " SELECT * FROM tblDetails "
>>   .Connection = cn
>> End With
>>
>> rdToProcess = cmdToProcess .ExecuteReader
>>
>> Select Case rdToProcess
>>   Case True
>>      Do
>>         'here I want to add or update data
>>         Dim cmdToChange As New SqlClient.SqlCommand
>>
>>         With cmdToChange
>>            .CommandText = "INSERT INTO tblNew(AD_Action) VALUES 
>> (@Action)"
>>            .Parameters.AddWithValue("@Action", rdToProcess("DE_Number"))
>>     .Connection = cn
>>         End With
>>
>>         cmdToChange.ExecuteNonQuery
>>      Loop until not rdProcess.Read
>> End Select
>>
>> At "cmdNieuweCafetariaActiedetail.ExecuteNonQuery", I get the message 
>> that I
>> have to close the first datareader in order to
>>
>> execute, but then my code cannot proceed because rdToProcess("DE_Number")
>> can no longer be read.
>>
>> How can I solve this?
>>
>> Many thanks and greetings for your help,
>>
>> Michel
>>
>