Is this possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to run 2 commands within a try-catch statment with a datareader

i.
Tr
Cn.Open(
Dim reader1 As SqlDataReade
Dim reader2 As SqlDataReade
reader1 = cmd1.ExecuteReader(
reader2 = cmd2.ExecuteReader(
reader1.Read(
reader2.Read(
Dim var1 As String = (reader1.GetSqlString(0).ToString
Dim var2 As String = (reader2.GetSqlString(0).ToString)
Catch ex As Exceptio
MsgBox(ex.Message
Finall
Cn.Close(
End Try
 
Yes you can call two commands. Always possible. But when showing error you might be carfull. If you want very specific error to be shown according to different command's failure then this is not the writemethod.

..
Sachi
 
Me said:
Is it possible to run 2 commands within a try-catch statment with a datareader?

i.e
Try
Cn.Open()
Dim reader1 As SqlDataReader
Dim reader2 As SqlDataReader
reader1 = cmd1.ExecuteReader()
reader2 = cmd2.ExecuteReader()
reader1.Read()
reader2.Read()
Dim var1 As String = (reader1.GetSqlString(0).ToString)
Dim var2 As String = (reader2.GetSqlString(0).ToString)
Catch ex As Exception
MsgBox(ex.Message)
Finally
Cn.Close()
End Try
Click to expand...

SqlClient only supports a single open DataReader on a connection. You could
join the results, or run 2 queries, or use DataTables.

David
 
Hi,

As David pointed out, Sql client supports only one open reader per
connection at the same time.
Thus, you'll have to use two differenct connection instances - each for one
reader.
 
Hi Miha

How would i use a second connection within this statement
I have tried creating another instance of the connection and opening it in the 'try' partbut i still get the same message..

There is already an open DataReader associated with this connection which must be
closed first

and then after i click OK i get another message...
The connection is already Open (state = Open

here is the code i tried..

Tr
Cn.Open(
cn2.open(
Dim reader1 As SqlDataReade
Dim reader2 As SqlDataReade
reader1 = cmd1.ExecuteReader(
reader2 = cmd2.ExecuteReader(
reader1.Read(
reader2.Read(
Dim var1 As String = (reader1.GetSqlString(0).ToString
Dim var2 As String = (reader2.GetSqlString(0).ToString
Catch ex As Exceptio
MsgBox(ex.Message
Finall
Cn.Close(
End Tr
 
M E said:
How would i use a second connection within this statement?
I have tried creating another instance of the connection and opening
it in the 'try' partbut i still get the same message...
Click to expand...

You've opened the second connection, but you haven't specified which
command should execute on which connection. Use

cmd1.Connection = Cn
cmd2.Connection = cn2

(or similar)
 
In my opinion it is a serious limitation that only one DataReader per connection is possible at the same time. I hope MS will change this in a future version. Miha has proposed to use different connections. Yes that would solve the problem but with the overhead of having many connections
Another solution would be to store the results of the first query to memory (DataSet, DataTable) so the connection is free for a new query. The disadvantages of this approach are
-the entire results of the first query must be read although perhaps only the first 20 of 50,000 are needed (example 1: a large table is displayed in a grid, but in the first moment only as many records as can be displayed on the screen page are read from the DB and, as the user scrolls down, more records are read. Example 2: the program reads one record and based on some condition it decides whether another one is needed, and so on).
-it consumes RAM (DB tables can be very large
excuse my poor english.
 
The upcoming release of the .NET Framework "Whidbey" includes support for
Multiple Active Result Sets (MARS.) This will allow you to re-use a
connection while a DataReader is still open on it.

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.


8870 said:
In my opinion it is a serious limitation that only one DataReader per
Click to expand...
connection is possible at the same time. I hope MS will change this in a
future version. Miha has proposed to use different connections. Yes that
would solve the problem but with the overhead of having many connections.
Another solution would be to store the results of the first query to
Click to expand...
memory (DataSet, DataTable) so the connection is free for a new query. The
disadvantages of this approach are:
-the entire results of the first query must be read although perhaps only
Click to expand...
the first 20 of 50,000 are needed (example 1: a large table is displayed in
a grid, but in the first moment only as many records as can be displayed on
the screen page are read from the DB and, as the user scrolls down, more
records are read. Example 2: the program reads one record and based on some
condition it decides whether another one is needed, and so on).
 
Back
Top