Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise, Visual Basic
Expertise: Intermediate
Jul 3, 2002

Updatable Join Recordset Using ADO and SQL Server


Contrary to popular thought, you can add new records to an ADO 2.1 Recordset object that is the result of a Join operation executed on multiple-base tables. You must specify the UniqueTable, or the name of the base table upon which updates, insertions, and deletions are allowed. This example uses the SQLOLEDB.1 provider and a disconnected ADO recordset as a bonus:
 
Private Sub Form_Load()
' You could combine the following 5 steps in
' the .Open method
objRecordset.ActiveConnection = _
objConnection 'An ADO connection object
objRecordset.CursorLocation = adUseClient
' Must use client-side server with this
' property!
objRecordset.CursorType = adOpenStatic
' Must use this with client-sided server cursor
objRecordset.LockType = adLockBatchOptimistic
' Hooking this up to a bound grid in a
' disconnected mode using the Northwind
' database
objRecordset.Open "SELECT * " & _
"FROM Customers JOIN Orders ON " & _
"Customers.CustomerID = " & _
"Orders.CustomerID WHERE city = " & _
"'London' ORDER BY CustomerID"
objRecordset.Properties("Unique Table"). _
Value = "Orders"
objRecordset.Properties("Resync " & _
Command").Value = "SELECT * FROM " & _
"(SELECT * FROM Customers JOIN " & _
"Orders ON Customers.CustomerID = " & _
"Orders.CustomerID WHERE city = " & _
"'London' ORDER BY CustomerID) " & _
"WHERE Orders.OrderID = ?"
objRecordset.ActiveConnection = Nothing
' A disconnected ADO recordset
Set grdTest.Datasource = objRecordset
End Sub
Private Sub Save()
objRecordset.ActiveConnection = _
objConnection
' Reconnect for the purpose of saving only
objRecordset.UpdateBatch
' Don't forget to check the ADO errors
' collection!
objRecordset.ActiveConnection = Nothing
' Disconnect again
End Sub

It
Alexander Meissel
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date