Multiple Datareaders on One Connection? 1

[byrne1]

In my code (see sample below) I have a connection to a DB and have opened a datareader on that connection. But within the datareader I need to execute SQL statements. When I try to do this I get the error message "there is already an open datareader associated with this connection which must be closed first".

Is there a way to execute a SQL command while looping through a datareader?


Dim ConnString As String = "dsn=brombergs"

' Set up query string
Dim CmdString As String = "select eventln.itemnum,webitemflag from eventln,item where item.itemnum=eventln.itemnum and eventln.eventmode='R' and (item.webitemflag<>'I' and item.webitemflag<>'Y')"

'Declare Connection and DataReader variables
Dim Conn2 As OdbcConnection
Dim Reader As OdbcDataReader
Dim itemnum As String
Dim storeemail As String

Try
'Open Connection
Conn2 = New OdbcConnection(ConnString)
Conn2.Open()

'Execute Query
Dim Cmd As New OdbcCommand(CmdString, Conn2)
Dim cmd2 As OdbcCommand
Reader = Cmd.ExecuteReader()

'Process The Result Set
While (Reader.Read())
itemnum = Reader("itemnum")
CmdString = "update item set webxmitflag='Y', webitemflag='Y' where itemnum='" & itemnum & "'"
cmd2 = New OdbcCommand(CmdString, Conn2)

'ERROR MESSAGE --> cmd2.ExecuteNonQuery()
End While

Finally
'Close Connection
Reader.Close()
Conn2.Close()

End Try

 

[KDavie]

In order to achieve what you are trying to accomplish you will need to create two separate methods, one for the select and one for the update. When you are reading through the select datareader, save everything out to a datatable (this isn't the only option but, imo, it is easy), then pass the datatable to the update method, and iterate through it and run your updates from there. Let me know if you need help with the syntax.

Regards,

-Kevin

 

[chrissie1]

Just make a second connection and command and you won't have a problem.

But I don't think your method is very fast and this could easily be done serversde with one update statement.

something like this.

update item set webxmitflag='Y', webitemflag='Y' where itemnum in (select eventln.itemnum,webitemflag from eventln,item where item.itemnum=eventln.itemnum and eventln.eventmode='R' and (item.webitemflag<>'I' and item.webitemflag<>'Y'))

And this itemnum='" & itemnum & "'" should be replaced by a parameter.



Christiaan Baes
Belgium

"My old site" - Me

 

[byrne1]

Thanks. I ended up creating the second connection and it worked just fine. I'm just used to doing things in vb.6 and I'm still very wet behind the ears on vb.net.

You are correct in saying that I could have done it w/ a serverside SQL statement. It's just a one-time program and I needed the coding practice anyway

 

[chrissie1]

I wasn't talking serverside stored procedure. I was talking parameter as part of the command object. Which will also prevent sql-injection just like a stored proc.

Christiaan Baes
Belgium

"My old site" - Me