Implicit Connections in ADO with Visual Basic 6

When a same connection is used to open two different RecordSets, ADO opens

two ports (TCP/IP) in the machine where application is running. If a RecordSet is opening inside a loop with the same connection then ado will open a separate port for its each iteration



Resolution

1. Use a separate connection for each RecordSets rather than using the same

connection as follows.



Dim conOne as new Adodb.Connection

Dim rsOne as Adodb.Recordset

Dim rsTwo as Adodb.Recordset

conOne.open(connection string goes here)

Set rsOne= con.execute select query



Do until rsOne.EoF

Set rsTwo = conOne.execute select c2,c3 from tbl1 where

c1 = & rsOne(0)

rsOne.MoveNext

Next



The code segment should be modified as follows



Dim conOne as new Adodb.Connection

Dim conTwo as new Adodb.Connection



Dim rsOne as Adodb.Recordset

Dim rsTwo as Adodb.Recordset

conOne.open(connection string goes here)

conTwo.open(connection string goes here)

Set rsOne= con.execute select query

Do until rsOne.EoF

Set rsTwo = conTwo.execute select c2,c3 from tbl1 where

c1 = & rsOne(0)

rsTwo.close()

rsOne.MoveNext

Next

In this case, only two ports will be opened even though the RecordSet is

opened several times inside the loop



2. If the SQL server is 2005 then the MARS(Multiple Active Result Sets)

will manage opening and closing the ports internally. Even though it is managing

the ports internally, the ports will be opened for each RecordSet opening.



conOne.Open "Provider=SQLNCLI;MARS Connection=true" & _

"Uid=" & UserId _

& ";Pwd=" & Password _

& ";Database=" & Database _

& ";Server=" & Server



1. For both the cases described above, the recordset is not opened in a proper

way. The following will be the ideal way of opening a recordset with readonly,

serverside cursor(known as firehose cursor).



Dim rstOne As New Recordset

rst.CursorLocation = adUseServer

rst.Open select goes here, conOne, adOpenForwardOnly,

adLockReadOnly

http://www.usedcarschennai.in/list/makes/audi