PRB: ErrorNum 10034 When Calling SqlSetOpt%()

ID: Q81396

The information in this article applies to:

Microsoft SQL Server Programmer's Toolkit, version 4.2

SYMPTOMS

The optparam$ parameter of the Visual Basic Library for SQL Server (VBSQL) function SqlSetOpt%() specifies the desired parameter of a particular option to be set. This parameter must always be passed to the function as a string enclosed in quotation marks, even in the case of numeric values. For example, the following line of code calls the SqlSetOpt%() function and sets the SQLBUFFER option to 100 rows:


   Results% = SqlSetOpt%(SqlConn%, SQLBUFFER, "100")

In addition, it is also possible to pass the parameter to the function as a string variable:


   Size$ = "100"
   Results% = SqlSetOpt%(SqlConn%, SQLBUFFER, Size$)

However, in Basic, it is common to convert numeric variables to strings with the STR$ function before passing them to other functions. This can lead to problems when calling SqlSetOpt%() because during the conversion process, the STR$ function automatically concatenates a space onto the left side of the resulting string. This additional space causes SqlSetOpt%() to fail with the following error:

ErrorNum 10034
Invalid or out of range dboption parameter

WORKAROUND

To work around this problem, you can use the LTRIM$ function to strip off any leading spaces prior to calling SqlSetOpt%(). The following example works properly:


   Size% = 100
   Results% = SqlSetOpt%(SqlConn%, SQLBUFFER, LTRIM$(STR$(Size%)))

Keywords : kbinterop SSrvVisB
Version : 4.2
Platform : OS/2
Issue type :