SqlNextRow%

Reads in the next data row from the row buffer.

Syntax

SqlNextRow% ( sqlconn% )

where

sqlconn%

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

Returns

One of five types of values depending on certain conditions:

• If SqlNextRow% reads a regular row, REGROW (-1) is returned. Regular rows contain data from columns designated by a SELECT statement.
• If SqlNextRow% is unsuccessful, FAIL (0) is returned.
• If there are no more rows to be read, if the statement returns no rows, or if the server was unable to return more rows (for example, when a deadlock occurs), NOMOREROWS (-2) is returned.
• If row buffering is turned on (using SqlSetOpt% with the SQLBUFFER option) and reading the next row would fill the buffer, BUFFULL (-3) is returned. In this case, no row is read, and to continue, at least one row must be cleared from the top of the row buffer. Clear the row buffer by calling SqlClrBuf.
• If the current row is a compute row, the identification number of the COMPUTE clause is returned.

Remarks

Normally, each row is processed in turn by repeatedly calling SqlNextRow%. If row buffering is turned on and the row buffer is cleared by SqlClrBuf, the discarded rows are no longer available. When row buffering is turned off, the last row is cleared when SqlNextRow% returns NOMOREROWS. If row buffering is turned on and you call SqlGetRow% to read a row, you can call SqlNextRow% to return rows in order following the row read by SqlGetRow%.

SqlResults% must be called and must return SUCCEED before you make any calls to SqlNextRow%.

You must continue calling SqlNextRow% until it returns NOMOREROWS. This is true even when you know that your query generates only one results row. Even if SqlRows% or SqlCmdRow% returns FAIL (indicating that no rows were returned), you must process the results by calling SqlNextRow% until it returns NOMOREROWS.

SQL Server can return two types of rows:

• Regular rows containing data from columns designated by a SELECT statement's select list.
• Compute rows resulting from the COMPUTE clause.

To help process data rows from SQL Server, SqlNextRow% returns different values according to the type of row. For details, see the previous section, "Returns."

Note This function is one of the four that do not return control to the application until the server sends the required response. The application can be blocked for a considerable time if the server is waiting for a lock or is processing a large sort. If this is unacceptable, always call SqlDataReady% before SqlNextRow% and set the DB-Library timeout to regain control periodically.

Example

The following program fragment uses SqlNextRow% to process a regular row:

'Put the statement into the command buffer.
Result% = SqlCmd%(Sqlconn%, "SELECT data FROM table")

'Send the statement to SQL Server and start execution.
Result% = SqlExec%(Sqlconn%) 
'Process the statement results.
Result% = SqlResults%(Sqlconn%)

'Retrieve and process the data in each row.
DO UNTIL SqlNextRow%(Sqlconn%) = NOMOREROWS

   'Code to print or process row of data.

LOOP

See Also

SqlCancel%, SqlCanQuery%, SqlClrBuf, SqlGetRow%, SqlResults%, SqlRows%; DB-Library for Visual Basic Options