SQL 7.0 / ADO / VB / CommandText / Prepared=True / Bug 
Author Message
 SQL 7.0 / ADO / VB / CommandText / Prepared=True / Bug

Unless I do not understand what 'Prepared' means there is a major bug in ADO
and SQL 7.0. The example below works with SQL 6.5 but does not work with SQL

 If you create a Command with Prepared = True as below and pass a parameter
to the command that returns X number of records the first time the command
is executed, you will never be able to return MORE than X number of records.
If Prepared = False this does not

The following example would return 1 record and is run the first time the
command is opened.

 sql = "select * from tblLine where Ln like ?+'%' "
 qLstLine.Prepared = True
 qLstLine.ActiveConnection = gAdoDB1
 qLstLine.CommandText = sql
 qLstLine.CommandType = adCmdText
 qLstLine.Parameters(0) = "AB"
 dsLstLine.Open qLstLine, , adOpenStatic, adLockReadOnly, adCmdText

The second time the command is opened with a key of %:

qLstLine.Parameters(0) = "%"
dsLstLine.Open qLstLine, , adOpenStatic, adLockReadOnly, adCmdText

In the example above you would imagine that you would get back all records.
But with Prepared = True you can only access one record since that is how
many records that were returned the first time the command was executed.
Recordcount returns the correct number of records but when you perform a
MoveNext you get a Bookmak error.

If Prepared  = False everything always works. Again this 'bug' only occurs
with SQL 7.0

Is this the way Prepared is supposed to work or is this a documented bug.


Sat, 25 Oct 2003 21:01:03 GMT
 [ 1 post ] 

 Relevant Pages 

1. OLEDB/ADO SQLOLEDB prepared query bug?

2. Prepared Statements, Access 7.0 ODBC Driver, and VB 4.0 Enterp

3. ADO Error 3021 - Either BOF or EOF is True problem using VB

4. Parametised query fails when Prepared=true

5. ADO AddNew command with VB 6.0 and SQL 7.0

6. Multiple Recordsets Using VB, ADO and SQL 7.0 Stored Procedures

7. SQL Server 7.0 error 8525 with DTC, MTS, VB, ADO

8. ADO 2.5 / Win2K / VB IDE / SQL Server 7.0

9. VB 6.0 / ADO 2.0 / SQL 7.0 Beta 3 (UniqueIdentifier)

10. SQL Server 7.0 , ADO & VB 6.0

11. Newbie SQL Server 7.0, VB 6.0 & ADO

Powered by phpBB® Forum Software