SqlSetOpt%

Sets a DB-Library for Visual Basic option.

Syntax

SqlSetOpt% ( sqlconn%, opt%, optparam$ )

where

sqlconn% ( )

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

opt%

Is the option to be set. For a list of the options available in DB-Library for Visual Basic, see DB-Library for Visual Basic Options.

optparam$

Is a parameter for an option. Certain options take parameters. For example, the SQLBUFFER option takes as its parameter the number of rows to be buffered. The optparam$ variable must always be enclosed in quotation marks, even in the case of a numeric value.

If the option takes no parameters, the value of optparam$ is ignored. For options that take no parameters, optparam$ can be an empty string or any other string value.

When you use the Str$ function to convert a numeric optparam$ value to a string, it pads the string with a space. If you don't remove the padded space from the parameter, a call to SqlSetOpt% fails. For example, in the following code fragment, SqlSetOpt% fails:

Optparam$ = Str$(Value%)
SqlSetOpt%(Sqlconn%, X, Optparam$)

To use the Str$ function to convert a numeric optparam$ value to a string, you must use the LTrim function to remove the padded space. For example:

Optparam$ = Str$(Value%)
SqlSetOpt%(Sqlconn%, X, LTrim$(Optparam$))

For a list of the option parameters available in DB-Library for Visual Basic, see DB-Library for Visual Basic Options.

Returns

SUCCEED (1) or FAIL (0).

Remarks

Although some options can be set and cleared directly through Transact-SQL, design your application to use SqlSetOpt% and SqlClrOpt% to set and clear options because SqlSetOpt% and SqlClrOpt% provide a uniform interface for setting both SQL Server and DB-Library for Visual Basic options. They also allow the application to use SqlIsOpt% to check the status of an option.

Three options are unique to DB-Library for Visual Basic: SQLBUFFER, SQLTEXTLIMIT, and SQLNOAUTOFREE. In addition to these options, all Transact-SQL options can be set using SqlSetOpt%. If you set any of the Transact-SQL options using SqlSetOpt%, DB-Library for Visual Basic places the appropriate Transact-SQL SET statement into the command buffer. This SET statement must be executed at some later time to take effect. Each SET statement means another call to SqlResults%. Therefore, design your application to receive the results returned from the statement generated by SqlSetOpt%. If an invalid parameter is specified, its invalidity is not known until the statement is sent to SQL Server and the results for that statement are returned using SqlResults%.

See Also

SqlClrOpt%, SqlIsOpt%, SqlResults%; DB-Library for Visual Basic Options