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.Sql Client.SqlComma nd("SELECT
* FROM Keyword", conn)
Dim myReader As System.Data.Sql Client.SqlDataR eader =
myCommand.Execu teReader(Comman dBehavior.Close Connection)
While myReader.Read()
'Console.WriteL ine(myReader("c oil_search"))
If Not myReader("coil_ search") Then
keyword = myReader("keywo rd")
Dim cmd3 As New
System.Data.Sql Client.SqlComma nd("UPDATE Keyword SET coil_search =
'True' WHERE keyword =" + keyword, conn)
cmd3.ExecuteNon Query()
End If
End While
myReader.Close( )


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

"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

[zacks@construction-imaging.com]

Re: reading and updating database as the same time

On Apr 20, 7:16 am, Etayki <etay...@gmail. comwrote:

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.Sql Client.SqlComma nd("SELECT
* FROM Keyword", conn)
Dim myReader As System.Data.Sql Client.SqlDataR eader =
myCommand.Execu teReader(Comman dBehavior.Close Connection)
While myReader.Read()
'Console.WriteL ine(myReader("c oil_search"))
If Not myReader("coil_ search") Then
keyword = myReader("keywo rd")
Dim cmd3 As New
System.Data.Sql Client.SqlComma nd("UPDATE Keyword SET coil_search =
'True' WHERE keyword =" + keyword, conn)
cmd3.ExecuteNon Query()
End If
End While
myReader.Close( )
>
During runtime I get this error in association with
"cmd3.ExecuteNo nQuery()":
>
"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.

[Duracel]

Re: reading and updating database as the same time

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_searc h") Then
theKeywords.Add ( myReader("keywo rd"))
End If

End While

....

For Each theKeyword As String In theKeywords

...update them

Next

Dim myCommand As New System.Data.Sql Client.SqlComma nd("SELECT
* FROM Keyword", conn)
Dim myReader As System.Data.Sql Client.SqlDataR eader =
myCommand.Execu teReader(Comman dBehavior.Close Connection)
While myReader.Read()
'Console.WriteL ine(myReader("c oil_search"))
If Not myReader("coil_ search") Then
keyword = myReader("keywo rd")
Dim cmd3 As New
System.Data.Sql Client.SqlComma nd("UPDATE Keyword SET coil_search =
'True' WHERE keyword =" + keyword, conn)
cmd3.ExecuteNon Query()
End If
End While
myReader.Close( )
>
>
During runtime I get this error in association with
"cmd3.ExecuteNo nQuery()":
>
"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
>