Using the Single-Row Query part
VA Smalltalk provides a Single-Row Query part for constructing and issuing SQL statements that return exactly one row of information from a database. This part is useful for looking up records in a database by a key, such as looking up an employee from the STAFF table using the ID as a key. The functions and interface for this part are similar to the SQL Statement part. The main difference between these parts is the connections you can make. The Single-Row Query part has a few additional actions, attributes, and events for updating, deleting, and creating new rows in a table:
Actions
newRow
Returns an empty row. Use this action to add rows to a database
deleteRow
Deletes the resultRow from the database.
updateRow
Updates the resultRow in the database. This action also adds a row, if it is not already in the database.
Attributes
resultRow
The row returned by the query. You can tear off this attribute to access the result row returned by the query. This attribute is analogous to the resultTable attribute of the Multi-row Query part.
Events
rowDeletedSuccessfully
Signaled when a row has been deleted from the database.
rowDeleteFailed
Signaled when no result row is returned by the query. You can use this event to display a message box.
rowUpdatedSuccessfully
Signaled when a row has been updated in the database.
rowUpdateFailed
Signaled when no result row is returned by the query. You can use this event to display a message box.
The resultRow part, which you can tear off of the Single-Row Query part, is similar in function to the currentRow part of the Multi-row Query part, but it has two additional attributes: isInDatabase and isNotInDatabase. These attributes both return either true or false.
Note:
If you use this part to issue a query that returns more than one row, then an error will be returned. The SQL Editor provides no means for checking that your SQL will return a single row. It is up to you to ensure that your SQL returns a single row.
The following example illustrates how to use the Single-Row Query part. It focuses on using the new actions, attributes, and events described above. For some of the other tasks required to build this example, refer to previous database examples in this book:
Beginning the example
1. To begin this example, create a new visual part with a label, a spin button (from the Lists category), and a push button as shown in the following illustration. Set the minimum value of the spin button to 10, its maximum value to 500, and its increment to 10.
2. Select 
, the Single-Row Query part, from the Database Functions category, and add it to the free-form surface of the Composition Editor to create the following query.
, the Single-Row Query part, from the Database Functions category, and add it to the free-form surface of the Composition Editor to create the following query. SELECT *
FROM STAFF
WHERE (STAFF.ID = :id)
The Single-Row Query settings window is similar to the settings view for the SQL Statement part and you can use it the same way. For information on creating host variables, refer to Using a host variable.
3. To display the query results in the window, tear off the resultRow attribute of the Single-Row Query part, and generate a quick form for it. For information on quick forms, refer to Using quick forms.
4. Now add four buttons below the quick form fields: New Row, Add, Update, and Delete. In the settings view for the Add, Update, and Delete buttons (page 3 of the General tab), turn off the enabled setting. These buttons will enable you to do the following:
a. Add a new row to the database
b. Update a row in the database
c. Delete a row from the database
Your window should now appear as follows:
Adding a message box
You can use the rowNotFound event of the Single-Row Query part to inform the user that the ID selected from the spin button does not correspond to an entry in the table:
1. Add a Message prompter (from the Prompters category) to the free-form surface. In the settings for this part, change the title to Row Not Found, the message to There is no entry for the ID you have selected, and the button type to OK.
2. Connect the rowNotFound event of the Single-Row Query part to the prompt action of the message prompter.
Adding a new row
You can obtain an empty row for a user to fill with information and add it to the database as follows:
1. Connect the clicked event of the New Row push button to the newRow action of the Single-Row Query. Then connect its clicked event to the setFocus action of the ID text entry part on the result row's quick form.
These connections obtain an empty row and place the cursor in the text field so that the user can add information to the text fields.
2. To add the new row to the database, connect the isNotInDatabase attribute of the resultRow, to the enable action of the Add push button. Then connect the clicked event of the Add push button to the updateRow action of the Single-Row Query part.
These connections add the new row to the database with the information in the text fields.
Updating a row
You can update an existing row in the database as follows:
1. Connect the isInDatabase attribute of the resultRow to the enable action of the Update push button.
2. Connect the clicked event of the Update push button to the updateRow action of the Single-Row Query.
These connections allow information that has been changed in the text entry fields to be applied to the result row.
Deleting a row
You can delete an existing row in the database as follows:
1. Connect the isInDatabase attribute of the resultRow to the enable action of the Delete push button.
2. Connect the clicked event of the Delete push button to the deleteRow action of the Single-Row Query.
These connections allow the row displayed in the text entry fields to be deleted from the database.
When you test the window, try selecting ID 500 to display the message box. Then add a new entry to the database and try updating and deleting it.
Last modified date: 06/01/2018