Databases - Page 3

HOME

Part 4 - Editing/Updating Records

NOTE: You can drag and drop or copy and paste the blocks of code below into the code editing area of the appropriate button events.

Put this code in the Edit Record button''s Action event:
if idField.text <> "" then
    dataControl1.readOnly = false//allows editing in all data fields
    pushButton5.enabled = true//enables the Update Record button for saving changes
end if

Put this code in the Update Record button:
dim cur as DatabaseCursor
dim theSQL as string

//a cursor "points at" a particular record
//db is a global property in Module1; it's initialized in the app class's Open event

theSQL = "select "
theSQL = theSQL + " firstName"//field names
theSQL = theSQL + " ,lastName"//separate field names with a comma
theSQL = theSQL + " ,emailAddress "
theSQL = theSQL + " ,ID "
theSQL = theSQL + " from People where ID =" + idfield.text
//Strangely, we use a string (idField.text) here even though ID is defined as an integer in the database.

cur = db.SQLSelect(theSQL)//get the record to update
cur.Edit

cur.IdxField(1).setString (firstField.text)//update firstName
cur.IdxField(2).setString (lastField.text)//update lastName
cur.IdxField(3).setString (emailField.text)//update emailAddress
//don't edit idField because the database uses this field to keep track of the record (primary key)

cur.Update//do the update
db.Commit//save the update

iif db.error then
    msgBox db.errormessage
end if

cur.close
dataControl1.runQuery//update dataControl1
dataControl1.readOnly = true
me.enabled = false

The code in the Edit Record button makes it possible to edit the data in the Data Browser window's fields. It also enables the Update Record button so that changes to the data can be saved.

The code in the Update Record is mostly SQL. Basically, it tells the database "SimpleDB" to update changes to the record in the "People" table identified by the number in idField's text.

Once this code for the Edit Record and Update Record buttons has been entered, users can edit records by first clicking the Edit Record button to allow editing in the data fields and then, once editing is completed, clicking the Update Record button to save the changes made to the record's data.

Part 5 - Deleting Records

Put this code in the Delete Record button''s Action event:
dim theSQL as string
dim cur as databaseCursor

//a cursor "points at" a particular record
//db is a global property in Module1; it's initialized in the app class's Open event

iif idField.text <> "" then
    cur = db.SQLSelect("select * from People where ID=" + idField.text)
    cur.deleteRecord
    cur.close
    dataControl1.runQuery//update dataControl1
end if

This code tells the database to delete the record from the table "People" where the ID field (in the record) has the number entered in idField on the window.

next page...