Re: Error running sql command

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@xxxxxxxxxxxxxxxxxxxx> wrote in message news:uzUu9JzxIHA.4952@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxx> wrote in message news:DC7069B8-F5BF-49AA-8023-A806CFF1EBA1@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxx> wrote in message news:O7N9SJuxIHA.4492@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages