Past Blast

• Starting Out
• eVB Database
• Wireless
• Pocket Commando

Featured Products

Stay in touch using the DEVBUSS RSS feeds.

 

News

Page 1  Page 2  Page 3 

Inserting Rows into TestTable

Click on the Insert Rows button and you will see the following screen:

Turning to the code:

Private Sub cmdInsertRows_Click()
Dim rs As ADOCE.Recordset
Dim arb1 As Integer
  If connOpen = True Then
    Set rs = CreateObject("ADOCE.Recordset.3.0")
    On Error Resume Next
    rs.Open "TestTable", conn, adOpenKeyset, adLockOptimistic

After creating a connection to the database we instantiate an ADOCE recordset on the TestTable table.

    For arb1 = 1 To 5
      rs.AddNew
      rs.Fields("fldTxt") = CStr(arb1)
      rs.Fields("fldInt") = arb1
      rs.Update
    Next

Next we iteratively add dummy data to TestTable using the AddNew method of the recordset. Once we have issued the AddNew command we assign values to the recordset fields, referencing the fields collection of the recordset by FieldName ('fldTxt' and 'fldInt'), and assigning them arbitrary values. It is not strictly necessary to call the Update method on the recordset since successive calls to the AddNew method will automatically apply the Update. I have included it here since old habit's die hard and if you don't call it you will lose the data added by the last AddNew transaction.

    If conn.Errors.Count = 0 Then
      txtDB.Text = CStr(rs.RecordCount) & " rows were added to TestTable."
    Else
      DispErrors
      txtDB.Text = "There were errors adding rows to TestTable."
    End If
    On Error GoTo 0
    rs.Close
    connClose
  End If
End Sub

Lastly we check the errors collection of the ADOCE connection for any errors. Again enable or disable the DispErrors routine as you require additional error trapping.

Accessing the data in TestTable

Now that we have a database, with a table and data in the table we may want to access that information. Click on the List Rows button and you should see values from the fldTxt column listed in the the listbox.

Looking at the code below you will see that once more we instantiate an ADOCE recordset object referencing the conn object. Since we only want to read some of the data into the listbox we use a forward only read cursor, traditionally this results in better read performance, however for ADOCE operations this has no performance advantage. We loop through the recordset until the EOF flag is true. I have included code to display either one or all of the recordset fields. Comment out the method you do not want. Also try and use better test data than I have.

Deleting the data in TestTable

What would be the fun in creating all this good stuff if we couldn't tear it all down? Click on the Delete Rows button.

Private Sub cmdDeleteRows_Click()
  ExecSQL "delete from TestTable", "All rows in TestTable were deleted.", "Err: the rows in TestTable were not deleted."
End Sub

Deleting the table

Click on the Delete Tbl button.

Private Sub cmdDeleteTbl_Click()
  ExecSQL "DROP table TestTable", "TestTable dropped.", "Err: TestTable was not dropped."
End Sub

The SQL syntax for removing a table altogether is 'drop table tablename'.

Deleting an ADOCE database

Lastly we delete the database taking us back to the beginning. Now click on the Delete DB button. You will see the following screen:

Private Sub cmdDeleteDB_Click()
Dim rc, rs
  If DBExists(gDBFileSpec) = True Then
    rc = MsgBox("Delete database " & gDBFileSpec & " ?", vbYesNoCancel, "Delete database")
    If rc = vbYes Then
      'clean up current database connection
      connClose
      'go ahead and delete the database
      On Error Resume Next
      Set rs = CreateObject("ADOCE.Recordset.3.0")
      rs.Open "DROP DATABASE '" & gDBFileSpec & "'"
      Set rs = Nothing

      On Error GoTo 0
    Else
      Exit Sub
    End If
  End If

  If DBExists(gDBFileSpec) = False Then
    txtDB.Text = gDBFileSpec & " deleted!"
  End If
End Sub

Previous Page  Next Page