Hello,
I use vb 2005 Express Edition with a remote MySQL (version 4.1.14) database (MySQL Net connector).
I want to execute more queries on 1 connection.
There is already an open DataReader associated with this Connection which must be closed first.
... appears by executing the 2nd query.
> CommandText is "SELECT category_id FROM categories"
> Then, I execute (with ExecuteReader), works fine
> I use while reader.read
> Within this while, I want to start a new query with a result id of the first query.
When I close the connection, the while-loop doesn't work. Help!
1 connection, more queries
Terry125
The keyword you want is MARS (Multiple Active Result Sets), but I think only MSSQL supports it: http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsql90/html/MARSinSQL05.asp
But by default, connections are pooled anyway, so you can open and close a second connection in your while loop all the time, you will always get the same connection from the pool, so there's no performance issue.
jkotas
Hi
While you are using the DataReader, no other commands can use the same connection until you have closed the DataReader. You can either create a new connection which you can then use to run a new command with a value from your DataReader, although this does require two connections to the DataSource. Or alternatively, do not use a DataReader in this instance but instead populate a DataTable and enumerate through the DataTable and run your new command.
Another approach, but this would require two enumerations, would be to continue using the DataReader and populate a List (ArrayList or similar) with the values that you want to use and then after you have populated all of the items, enumerate through your list running your command.
HTH