SqlQual$

Returns a string containing the WHERE clause for the current row in a specified table. In a browsable table, this string can be used to update the current row.

Syntax

SqlQual$ ( sqlconn%, tabnum%, tabname$ )

where

sqlconn%

Is a SQL Server connection. The value of sqlconn% is returned by SqlOpen%.

tabnum%

Specifies an integer to receive the number of the table. Tables are numbered in the order they are listed in the SELECT statement's FROM clause. Table numbers start at 1. If tabnum% is -1, tabname$ is used to identify the table.

tabname$

Identifies a string containing the name of a table specified in the SELECT statement's FROM clause. If tabname$ is an empty string, tabnum% is used to identify the table.

Returns

A string containing the WHERE clause for the current row in a specified table. If the specified table cannot be browsed, SqlQual$ returns an empty string. A browsable table has a unique index and a timestamp column.

Remarks

SqlQual$ is a DB-Library for Visual Basic browse-mode function. For a detailed discussion of browse mode, see DB-Library for Visual Basic Programming.

SqlQual$ provides a WHERE clause that can be used to update a single row in a browsable table. Columns from this row must have been previously retrieved through a browse-mode SELECT query.

The WHERE clause produced by SqlQual$ begins with the keyword WHERE and contains references to the row's unique index and timestamp column. You can simply append the WHERE clause to an UPDATE or DELETE statement; there is no need to examine it or manipulate it in any way.

The timestamp column indicates the time that a particular row was last updated. An update on a browsable table fails if the timestamp column in the WHERE clause that SqlQual$ generates is different from the timestamp column in the table. Such a condition, which generates SQL Server error message 532, indicates that another user updated the row since it was selected for browsing. Design your application to include the logic for handling an update failure.

SqlQual$ can construct WHERE clauses only for browsable tables. You can use SqlTabBrowse% to determine whether a table can be browsed. SqlQual$ is usually called after SqlNextRow%.

Example

The following code fragment illustrates one approach to updating a table in a multiuser environment using browse mode. The application attempts to update a record specified by the user. If the update fails, the application assumes that someone else already updated the same row and changed its timestamp. To handle this situation, the application simply repeats the process, retrieving the changed row for the user to examine and edit and allowing the user to decide whether to overwrite the change. A complete application examines the messages from the server to determine why the update failed.

DO
   'Retrieve the employee record from the database. 
   'Assuming that "empid" is a unique index, the query 
   'will return only one row.
   cmd$ = "SELECT * FROM employees WHERE empid ="
   cmd$ = cmd$  STR$(EmployeeId%)  " FOR BROWSE"
   Result% = SqlCmd%(Sqlconn%, cmd$)
   Result% = SqlExec%(Sqlconn%)
   Result% = SqlResults%(Sqlconn%)
   Result% = SqlNextRow%(Sqlconn%)

   'Assign returned data to variables.
   Name$ = SqlData$(Sqlconn%, 2)
   Salary# = VAL(SqlData$(Sqlconn%, 3))

   'Let user edit data.
   PRINT "Name: "; Name$
   PRINT "Salary: "; Salary#
   INPUT "New salary"; Salary#

'Get a WHERE clause and update the database. Sqlconn%
'is the connection used to query the database, while
'UpdSqlconn% is the connection used to update it.
Qual$ = SqlQual$(Sqlconn%, -1, "employees")
Result% = SqlNextRow%(Sqlconn%)
cmd$ = "UPDATE employees SET salary ="
cmd$ = cmd$  STR$(Salary#)  " "  Qual$
Result% = SqlCmd%(UpdSqlconn%, cmd$)
Result% = SqlExec%(UpdSqlconn%)

'If the update failed, try again.
LOOP WHILE SqlResults%(UpdSqlconn%) = FAIL 
OR Result% = FAIL 

Output:

Name: Charles Dickens
Salary: 76543.21
New salary? 80000.00

See Also

SqlColBrowse%, SqlColSource$, SqlTabBrowse%, SqlTabCount%, SqlTabName$, SqlTabSource$, SqlTsNewLen%, SqlTsNewVal$, SqlTsPut%