Deleting Records

< BACK  NEXT >

[oR]

Records can be deleted by opening a recordset, navigating to the record to be deleted, and then calling the _RecordsetPtr::Delete function to delete the record. It is much more efficient, though, to execute a SQL DELETE statement with a WHERE clause specifying the record or records to be deleted (for example, "DELETE FROM Customers WHERE CustID = 1"). You can execute such code using the _ConnectionPtr::Execute function.

The Execute function takes the following parameters:

• A BSTR containing the SQL Statement to execute.

• A VARIANT that contains, on return, the number of rows affected. For example, this would be the number of rows actually deleted.

• A constant, which is usually AdoNS::adCmdText, indicating that a SQL statement is being passed.

The code in Listing 16.7 obtains a connection from the function GetConnection (described in the last section) and uses the Execute function to delete all the rows in the Customer table. The actual number of rows deleted is displayed. Note that the call to Execute succeeds even if there are no records to delete.

Listing 16.7 The SQL DELETE statement

 void Listing16_7() {   AdoNS::_ConnectionPtr pConnection;   if(!GetConnection(pConnection))       return;   _variant_t varRowsAffected;   _bstr_t bstrSQL(_T("DELETE FROM Customers"));   pConnection->Execute(bstrSQL,       &varRowsAffected,       AdoNS::adCmdText);   cout   _T("Rows Deleted: ")   varRowsAffected.lVal          endl;   pConnection->Close(); } 

You can execute any appropriate SQL statement. For example, you can execute an INSERT statement to add records to a table and thereby avoid using recordsets and safe arrays.

< BACK  NEXT >