ADO command does not support bookmarks?

[xinyin]

Hi, I am trying to build an ADO command connect to a recordset. This is my code:

Public CommandX as New ADODB.Command
Public RecordsetX as New ADODB.Recordset
Dim SQL as string

Set CommandX.ActiveConnection = another_form.ConnectionX
("ConnectionX" is a connection object defined in another form as public, so now when I want to refer to it I have to type another_form.ConnectionX)

SQL = "Select * From <anyTable>"
CommandX.CommandText = SQL
CommandX.CommandType = adCmdStoredProc
Set RecordsetX = CommandX.Execute

During test running, when the program reachs lines like this one:
> RecordsetX.AbsolutePosition = 1 <
There will be error message saying bookmarks are not supported.

So I copy these lines from the reference book:
RecordsetX.CursorLocation = adUseClient
RecordsetX.CursorType = adOpenDynamic
RecordsetX.LockType = adLockOptimistic
but still the same result.

When I work with recordset alone the absoluteposition thing works fine, but not anymore when I am having commands involved. Is there anything wrong in the code?

 

[fredericofonseca]

First of all, your SQL is NOT a Stored procedure, so your command type is wrong.

The definitions of bookmarks with command objects will depend on the definitions of the connection object, and on the definitions of the recordset object depending on how you define the open of the recordset.

So please post the full code of both connection, command and record set objects.

If possible create a small example where you only have the code required to execute the SQL and to raise the error and post this code.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[CassandraR]

Hi xinyin:

The problem could be in your connection definition. Did you use a cursor type that permits bookmarks? Please see Bob Rodes FAQ (faq222-3670) on ADO cursor types.

Cassie

 

[xinyin]

Sorry for late reply.
Thanks for the suggestions. Now I change the CommandType of CommandX to "adcmdtext" and added this line
> ConnectionX.CursorLocation = adUseClient <
and now there is no more problem in running
> RecordsetX.AbsolutePosition = 1 <

However, I still cannot add or delete records. I just read an article in MSDN website and it says
"...a recordset opened with the Execute method is always forward-only, read-only. For flexibility, you'll want to stick to the Open method of the Recordset object."
So I think from now on I will always create the recordset directly from the connection without commands involved.

I just want to know how do most of you create recordsets. Do you usually do it by something like
> RecordsetX.Open SQL, ConnectionX, adOpenKeyset, adLockPessimistic <...
I want to know this because I don't want my programming to be too different from the "main trend

 

[fredericofonseca]

Many of us will NOT use recordsets to do the updates.

We will instead use UPDATE/INSERT SQL statements, or stored procedures, normally with the adodb.command object. This is something you may with to think of also.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com