reading and updating database as the same time

[Etayki]

Hi!

I am trying to read and update my database at the same time, but it
doesn't seem to be working:

Dim myCommand As New System.Data.SqlClient.SqlCommand("SELECT
* FROM Keyword", conn)
Dim myReader As System.Data.SqlClient.SqlDataReader =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
While myReader.Read()
'Console.WriteLine(myReader("coil_search"))
If Not myReader("coil_search") Then
keyword = myReader("keyword")
Dim cmd3 As New
System.Data.SqlClient.SqlCommand("UPDATE Keyword SET coil_search =
'True' WHERE keyword =" + keyword, conn)
cmd3.ExecuteNonQuery()
End If
End While
myReader.Close()


During runtime I get this error in association with
"cmd3.ExecuteNonQuery()":

"There is already an open DataReader associated with this Command
which must be closed first."

Is there a way that I can update the database while the DataReader is
still open? It is inefficient to have to close it and open it each
time I need to update the table.

Thanks,

Etay

 

[Duracel]

You can only have 1 reader active on a connection at any time (I think).
Re-organise your code so that you add to a collection of items to update and
then do the update in a loop afterwards. It minimises nesting, which can
make your code a pita to maintain.

i.e.

Dim theKeywords As New List(of String )

....

While myReader.Read ()

If Not myReader ( "coil_search") Then
theKeywords.Add ( myReader("keyword"))
End If

End While

....

For Each theKeyword As String In theKeywords

...update them

Next

 

[zacks]

Hi!

I am trying to read and update my database at the same time, but it
doesn't seem to be working:

Dim myCommand As New System.Data.SqlClient.SqlCommand("SELECT
* FROM Keyword", conn)
Dim myReader As System.Data.SqlClient.SqlDataReader =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
While myReader.Read()
'Console.WriteLine(myReader("coil_search"))
If Not myReader("coil_search") Then
keyword = myReader("keyword")
Dim cmd3 As New
System.Data.SqlClient.SqlCommand("UPDATE Keyword SET coil_search =
'True' WHERE keyword =" + keyword, conn)
cmd3.ExecuteNonQuery()
End If
End While
myReader.Close()

During runtime I get this error in association with
"cmd3.ExecuteNonQuery()":

"There is already an open DataReader associated with this Command
which must be closed first."

Is there a way that I can update the database while the DataReader is
still open? It is inefficient to have to close it and open it each
time I need to update the table.

Thanks,

Etay

You need another connection. A connection can only have one active
command at the time.