Error running sql command

Hi

I have below code;

Cmd = New SqlCommand("SELECT * FROM MyTable01", DBConnectionRemote())
Reader = Cmd.ExecuteReader()

While (Reader.Read())
ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))

Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID = " &
ID.ToString
Dim delCmd As New SqlCommand(delstr, DBConnectionRemote())

delCmd.ExecuteNonQuery()
End While

I am getting a 'System.InvalidOperationException: There is already an open
DataReader associated with this Command which must be closed first' error on
the delCmd.ExecuteNonQuery() statement. What is the problem and how can I
fix it?

Thanks

Regards

Miha
Thu Jun 05 07:57:11 CDT 2008

Only one operation per connection instance is allowed at same time (your
connection is used by reader).
Thus, create another connection instance for the second operation

--
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/

"John" <info@nospam.infovis.co.uk> wrote in message
news:O7N9SJuxIHA.4492@TK2MSFTNGP02.phx.gbl...
> Hi
>
> I have below code;
>
> Cmd = New SqlCommand("SELECT * FROM MyTable01", DBConnectionRemote())
> Reader = Cmd.ExecuteReader()
>
> While (Reader.Read())
> ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
>
> Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID = "
> & ID.ToString
> Dim delCmd As New SqlCommand(delstr, DBConnectionRemote())
>
> delCmd.ExecuteNonQuery()
> End While
>
> I am getting a 'System.InvalidOperationException: There is already an open
> DataReader associated with this Command which must be closed first' error
> on the delCmd.ExecuteNonQuery() statement. What is the problem and how can
> I fix it?
>
> Thanks
>
> Regards
>

Paul
Thu Jun 05 08:44:32 CDT 2008

On Thu, 5 Jun 2008 09:01:13 +0100, "John" <info@nospam.infovis.co.uk> wrote:

¤ Hi
¤
¤ I have below code;
¤
¤ Cmd = New SqlCommand("SELECT * FROM MyTable01", DBConnectionRemote())
¤ Reader = Cmd.ExecuteReader()
¤
¤ While (Reader.Read())
¤ ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
¤
¤ Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID = " &
¤ ID.ToString
¤ Dim delCmd As New SqlCommand(delstr, DBConnectionRemote())
¤
¤ delCmd.ExecuteNonQuery()
¤ End While
¤
¤ I am getting a 'System.InvalidOperationException: There is already an open
¤ DataReader associated with this Command which must be closed first' error on
¤ the delCmd.ExecuteNonQuery() statement. What is the problem and how can I
¤ fix it?

First, you will need to specify a different connection object for your DELETE command. You can't
share the connection with the DataReader in this instance.

Second, if the criteria of your SQL expression is a string then it must be enclosed in single
quotes:

Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID = '" & ID.ToString & "'"


Paul
~~~~
Microsoft MVP (Visual Basic)

William
Thu Jun 05 10:43:56 CDT 2008

Unless you use MARS (Multiple Active Resultsets) you cannot reuse a SQL
Server connection until the rowset has been fetched. That is, the rows you
requested via the SELECT must be fetched in their entirety before the
connection can be used for another operation--either that or you have to use
the Cancel method on the Command. A typical solution to this problem is to
open a second connection to perform the updates.

The basic problem with your code is that you are not using SQL Server as it
should be used. When you want to delete rows based on IDs fetched from
another rowset, you should do so on the server, not via looping through the
rowset on the client. For example,
DELETE MyTable WHERE ID IN (SELECT ID FROM SomeOtherTable WHERE <some
criteria>)

I discuss this approach in my book.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

"John" <info@nospam.infovis.co.uk> wrote in message
news:O7N9SJuxIHA.4492@TK2MSFTNGP02.phx.gbl...
> Hi
>
> I have below code;
>
> Cmd = New SqlCommand("SELECT * FROM MyTable01", DBConnectionRemote())
> Reader = Cmd.ExecuteReader()
>
> While (Reader.Read())
> ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
>
> Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID = "
> & ID.ToString
> Dim delCmd As New SqlCommand(delstr, DBConnectionRemote())
>
> delCmd.ExecuteNonQuery()
> End While
>
> I am getting a 'System.InvalidOperationException: There is already an open
> DataReader associated with this Command which must be closed first' error
> on the delCmd.ExecuteNonQuery() statement. What is the problem and how can
> I fix it?
>
> Thanks
>
> Regards
>

John
Thu Jun 05 12:35:05 CDT 2008

Hi Bill

I am using a loop as I am processing a command queue. I fetch a command from
a table, process it (code not included) and if command executes successfully
I delete it form the queue. Can't think of a way of doing this without a
loop.

Thanks

Regards

"William Vaughn [MVP]" <billvaNoSPAM@betav.com> wrote in message
news:DC7069B8-F5BF-49AA-8023-A806CFF1EBA1@microsoft.com...
> Unless you use MARS (Multiple Active Resultsets) you cannot reuse a SQL
> Server connection until the rowset has been fetched. That is, the rows you
> requested via the SELECT must be fetched in their entirety before the
> connection can be used for another operation--either that or you have to
> use the Cancel method on the Command. A typical solution to this problem
> is to open a second connection to perform the updates.
>
> The basic problem with your code is that you are not using SQL Server as
> it should be used. When you want to delete rows based on IDs fetched from
> another rowset, you should do so on the server, not via looping through
> the rowset on the client. For example,
> DELETE MyTable WHERE ID IN (SELECT ID FROM SomeOtherTable WHERE <some
> criteria>)
>
> I discuss this approach in my book.
>
> --
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
>
> "John" <info@nospam.infovis.co.uk> wrote in message
> news:O7N9SJuxIHA.4492@TK2MSFTNGP02.phx.gbl...
>> Hi
>>
>> I have below code;
>>
>> Cmd = New SqlCommand("SELECT * FROM MyTable01", DBConnectionRemote())
>> Reader = Cmd.ExecuteReader()
>>
>> While (Reader.Read())
>> ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
>>
>> Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID = "
>> & ID.ToString
>> Dim delCmd As New SqlCommand(delstr, DBConnectionRemote())
>>
>> delCmd.ExecuteNonQuery()
>> End While
>>
>> I am getting a 'System.InvalidOperationException: There is already an
>> open DataReader associated with this Command which must be closed first'
>> error on the delCmd.ExecuteNonQuery() statement. What is the problem and
>> how can I fix it?
>>
>> Thanks
>>
>> Regards
>>

Cor
Thu Jun 05 23:39:51 CDT 2008

John,

You can set them in a collection (as it is just about one integer even a
simple arraylist does this or as you wish a simple generic list of integers)
and delete them then in a loop when the first one is ready.

In my idea the most simple one to complete your code.

Cor

"John" <info@nospam.infovis.co.uk> schreef in bericht
news:uzUu9JzxIHA.4952@TK2MSFTNGP05.phx.gbl...
> Hi Bill
>
> I am using a loop as I am processing a command queue. I fetch a command
> from a table, process it (code not included) and if command executes
> successfully I delete it form the queue. Can't think of a way of doing
> this without a loop.
>
> Thanks
>
> Regards
>
> "William Vaughn [MVP]" <billvaNoSPAM@betav.com> wrote in message
> news:DC7069B8-F5BF-49AA-8023-A806CFF1EBA1@microsoft.com...
>> Unless you use MARS (Multiple Active Resultsets) you cannot reuse a SQL
>> Server connection until the rowset has been fetched. That is, the rows
>> you requested via the SELECT must be fetched in their entirety before the
>> connection can be used for another operation--either that or you have to
>> use the Cancel method on the Command. A typical solution to this problem
>> is to open a second connection to perform the updates.
>>
>> The basic problem with your code is that you are not using SQL Server as
>> it should be used. When you want to delete rows based on IDs fetched from
>> another rowset, you should do so on the server, not via looping through
>> the rowset on the client. For example,
>> DELETE MyTable WHERE ID IN (SELECT ID FROM SomeOtherTable WHERE <some
>> criteria>)
>>
>> I discuss this approach in my book.
>>
>> --
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> ____________________________________________________________________________________________
>>
>> "John" <info@nospam.infovis.co.uk> wrote in message
>> news:O7N9SJuxIHA.4492@TK2MSFTNGP02.phx.gbl...
>>> Hi
>>>
>>> I have below code;
>>>
>>> Cmd = New SqlCommand("SELECT * FROM MyTable01", DBConnectionRemote())
>>> Reader = Cmd.ExecuteReader()
>>>
>>> While (Reader.Read())
>>> ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
>>>
>>> Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID =
>>> " & ID.ToString
>>> Dim delCmd As New SqlCommand(delstr, DBConnectionRemote())
>>>
>>> delCmd.ExecuteNonQuery()
>>> End While
>>>
>>> I am getting a 'System.InvalidOperationException: There is already an
>>> open DataReader associated with this Command which must be closed first'
>>> error on the delCmd.ExecuteNonQuery() statement. What is the problem and
>>> how can I fix it?
>>>
>>> Thanks
>>>
>>> Regards
>>>
>
>

William
Fri Jun 06 11:02:35 CDT 2008

Sure. In my TSQL classes at MSU, one of the example students wrote was to
recreate ISQL (now SQLCMD). This is a text processor that takes a batch or
script of TSQL statements and executed them in sequence. Each batch in the
script was separated from the other by GO (just as in SQLCMD). In your case,
I would simply write the commands to a file and process them using simple
parsing code. Again, the point is, I would not do two round trips per
operation. If the commands are in a table on the server, I would create
strings on the server and use SQL EXECUTE to execute them... not bring them
to the client to do so.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

"John" <info@nospam.infovis.co.uk> wrote in message
news:uzUu9JzxIHA.4952@TK2MSFTNGP05.phx.gbl...
> Hi Bill
>
> I am using a loop as I am processing a command queue. I fetch a command
> from a table, process it (code not included) and if command executes
> successfully I delete it form the queue. Can't think of a way of doing
> this without a loop.
>
> Thanks
>
> Regards
>
> "William Vaughn [MVP]" <billvaNoSPAM@betav.com> wrote in message
> news:DC7069B8-F5BF-49AA-8023-A806CFF1EBA1@microsoft.com...
>> Unless you use MARS (Multiple Active Resultsets) you cannot reuse a SQL
>> Server connection until the rowset has been fetched. That is, the rows
>> you requested via the SELECT must be fetched in their entirety before the
>> connection can be used for another operation--either that or you have to
>> use the Cancel method on the Command. A typical solution to this problem
>> is to open a second connection to perform the updates.
>>
>> The basic problem with your code is that you are not using SQL Server as
>> it should be used. When you want to delete rows based on IDs fetched from
>> another rowset, you should do so on the server, not via looping through
>> the rowset on the client. For example,
>> DELETE MyTable WHERE ID IN (SELECT ID FROM SomeOtherTable WHERE <some
>> criteria>)
>>
>> I discuss this approach in my book.
>>
>> --
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> ____________________________________________________________________________________________
>>
>> "John" <info@nospam.infovis.co.uk> wrote in message
>> news:O7N9SJuxIHA.4492@TK2MSFTNGP02.phx.gbl...
>>> Hi
>>>
>>> I have below code;
>>>
>>> Cmd = New SqlCommand("SELECT * FROM MyTable01", DBConnectionRemote())
>>> Reader = Cmd.ExecuteReader()
>>>
>>> While (Reader.Read())
>>> ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
>>>
>>> Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID =
>>> " & ID.ToString
>>> Dim delCmd As New SqlCommand(delstr, DBConnectionRemote())
>>>
>>> delCmd.ExecuteNonQuery()
>>> End While
>>>
>>> I am getting a 'System.InvalidOperationException: There is already an
>>> open DataReader associated with this Command which must be closed first'
>>> error on the delCmd.ExecuteNonQuery() statement. What is the problem and
>>> how can I fix it?
>>>
>>> Thanks
>>>
>>> Regards
>>>
>
>