Share via

Visual Basic: RDO Data Control

  • Article

Execute Method Example

This example illustrates use of the Execute method to execute SQL queries against a remote data source. These action queries do not return rows, but in some cases do return the number of rows affected in the RowsAffected property. The example creates a work table called TestData, inserts a few rows of data in the table and proceeds to run a DELETE query against the table. Notice that the delete queries have their own embedded transaction management. Because of this, you must use the rdExecDirect option to prevent the creation of stored procedures which negate the use of query-provided transactions.

  Option Explicit
Dim er As rdoError
Dim cn As New rdoConnection
Dim qy As New rdoQuery
Dim rs As rdoResultset
Dim col As rdoColumn
Dim SQL As String

Private Sub DropRows_Click()
Dim SQL As String, Ans As Integer

SQL = "Begin Transaction Delete TestData " _
   & " Where State = " & StateWanted & ""
cn.Execute SQL, rdExecDirect
Ans = MsgBox("Ok to delete these "  _
   & cn.RowsAffected & " rows?", vbOKCancel)
If Ans = vbOK Then
   cn.Execute "Commit Transaction", rdExecDirect
Else
   cn.Execute "Rollback Transaction", rdExecDirect
End If
Exit Sub
End Sub

Private Sub Form_Load()
cn.CursorDriver = rdUseOdbc
cn.Connect = "uid=;pwd=;server=sequel;" _
   & "driver={SQL Server};" _
   & "database=pubs;dsn=;"
cn.EstablishConnection
With qy
   .Name = "TestList"
   .SQL = "Select * from TestData Where State = ?"
   .RowsetSize = 1
   Set .ActiveConnection = cn
End With
SQL = "Drop Table TestData"
cn.Execute SQL

SQL = " CREATE TABLE TestData " _
   & " (ID integer identity NOT NULL, " _
   & " PName char(10) NULL," _
   & " State Char(2) NULL) " _
   & " CREATE UNIQUE INDEX " _
   &   "TestDataIndex on TestData(ID)"
    
cn.Execute SQL
SQL = "Insert TestData (PName,State)  " _
   & "Values('Bob', 'CA')" _
   & " Insert TestData (PName,State) " _
   & " Values('Bill', 'WA')" _
   & " Insert TestData (PName,State) " _
   & " Values('Fred', 'WA') _
   & " Insert TestData (PName,State) " _
   & " Values('George', 'CA')" _
   & " Insert TestData (PName,State) " _
   & " Values('Sam', 'TX')" _
   & " Insert TestData (PName,State) " _
   & " Values('Marilyn', 'TX')"
cn.Execute SQL
Debug.Print cn.RowsAffected
' This returns 1 
'(The last INSERT statement affected 1 row)
End Sub

Private Sub SeekRows_Click()
qy(0) = StateWanted
Set rs = qy.OpenResultset(rdOpenForwardOnly, _
rdConcurReadOnly)
List1.Clear
If rs.EOF Then
   MsgBox "No hits for that state"
Exit Sub
End If
Do Until rs.EOF
   List1.AddItem rs!PName & " - " & rs!state
   rs.MoveNext
Loop
End Sub