Use multiple datareader

[manishraj]

Hi all!!!!!

In VB6.0 we use to write a code like this...........

//////////////////////////////////////////////////////////////////////////

Dim con as adodb.connection
Dim rs as adodb.recordset
Dim rs1 as adodb.recordset

set con=new adodb.connection

with con
.provider="SQLOLEDB"
.connectionstring="xyz"
.open
end with

set rs=new adodb.recordset
set rs1=new adodb.recordset

st="select sch_code from sch_master order by 1"
rs.open st,con

if not rs.eof then
while not rs.eof
st="select f_val from f_value where sch_code='"& rs("sch_code") &"'"
rs1.open st,con
if not rs1.eof then

''/////////code to display data in grid///////////////
end if
rs1.close
rs.movenext
wend
end if


///////////////////////////////////////////////////////////////////////////////

How can I perform the same operation as stated above using ADO.NET(Dataset/DataReader) and the program should must have only one connection.


Please Reply Me

Manish Raj

[Pirate]

Forget about old ADO and go with ADO.NET , here's an example shows you how to connectio DataGrid to Dataset :

'Establish a connection to the data source.
        Dim sConnectionString As String
 
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=d:\My Documents\db1.mdb"
        Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
        objConn.Open()
 
        Dim da As New System.Data.OleDb.OleDbDataAdapter("Table1", objConn)
 
        Dim ds As New DataSet("AccessDS")
 
        da.Fill(ds, "AccessTable")
 
        DataGrid1.SetDataBinding(ds, "AccessTable")
      
       objConn.Close()

[Cander]

That is what he was asking about. You can only have 1 DataReader per connection. Of course if you are using a Dataset, you dont need a DataReader, and hould just use the DataAdapter to fill the Dataset and use the Data in that.

[TheExtreme]

manishraj,

you will need to add then remove a datatable for each loop through the main recordset. Use a GUID or Key for the table name, as the old data may not be destroyed on remove of the table form the dataset. you are only allowed one dataadapter open per connection. keep in mind the connection pooling is enabled with VB.NET, so if you create a new connection with the same connection string, you are actully only getting a pointer to the connection pool.