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: Web Development
Expertise: Beginner
Mar 11, 1999

Deleting From a Join Table With an ADODB.Recordset

Question:
How do I delete from an ADODB.Recordset that is created with a join expression and a where statement to minimize the result from the SQL query? I am using Visual Basic 6 and SQL Server 6.5 and I want to use the recordset delete function.

Answer:
You have to know a bit about ADO cursors and ADO locks and about what each type supports. The default combination of lock and cursor does not allow the action you described. It is easy to change the lock and cursor type from the default types to ones that better suit your needs. For example, you can change the cursor to dynamic cursor to allow most any action. Be careful with the dynamic cursor, it is the most resource intensive. This ADO code will accomplish what you want to do. Notice that the SQL statement uses both JOIN and WHERE clauses.

Private Sub DeleteRecord()
    Dim RS 
    Set RS = server.CreateObject("ADODB.Recordset")
    Dim strSQL
    strSQL = "SELECT authors.au_lname, authors.au_fname,
titleauthor.royaltyper FROM titleauthor INNER JOIN authors ON
titleauthor.au_id = authors.au_id WHERE (titleauthor.royaltyper
> 50)"
    
    Dim objConnectionPubs
    Set objConnectionPubs = Server.CreateObject ("ADODB.Connection")
    
    ' Establish a connection
    With objConnectionPubs
        .Provider = "SQLOLEDB"
        .ConnectionString = "User ID=sa;" & _
                   "Data Source=(local);" & _
                   "Initial Catalog=Pubs"
        .Open
    End With

'	THE FOLLOWING LINE OF CODE CORRESPONDS TO:
'    RS.Open strSQL, objConnectionPubs, adOpenDynamic,
adLockOptimistic
    RS.Open strSQL, objConnectionPubs, 2, 3
    
    RS.Delete
    RS.MoveNext
    
End Sub
DevX Pro
 
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