Page 242 - Open Soource Technologies 304.indd

Event Driven Programming

Listing 2. Using multiple active recordsets from Visual Basic 6
‘
‘ Connect to the database
‘
Dim cn As ADODB.Connection
Set cn = New Connection
cn.ConnectionString = “Provider=SQLNCLI.1;Integrated Security=SSPI;” & _
“Persist Security Info=False;” & _
“AttachDBFileName=” & App.Path & “\northwnd.mdf;Data
Source=server1\sqlexpress”
cn.Open

‘
‘ Get all the orders
‘
Dim rsOrders As Recordset
Set rsOrders = New Recordset
rsOrders.Open “Select * from orders”, cn
Do While Not rsOrders.EOF
‘
‘ If the order matches some custom business logic then get
the details for
‘ that order, without opening a new connection.
‘
If SomeBusinessLogic(rsOrders(“CustomerID”)) Then
Dim rsDetails As Recordset
Set rsDetails = New Recordset
‘
‘ Open a new recordset using the same connection.
Normally it’s not
‘ possible to have two recordsets simultaniously using
the same
‘ connection, but MARS makes this possible
‘
rsDetails.Open “Select sum(quantity * unitprice) as total
“ & _
“from [order details] “ & _
“where OrderID=” & rsOrders(“OrderID”), _
cn
grandTotal = grandTotal + rsDetails(“total”)
End If

236 LOVELY PROFESSIONAL UNIVERSITY