Error running sql command

  • Thread starter Thread starter John
  • Start date Start date
J

John

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
 
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
 
¤ 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)
 
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)
____________________________________________________________________________________________
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top