Specs:

Visual Basic 6
ADO 2.7
Sybase Sql Server 11.9.2.
ODBC 3.11
Open Client 11.9.2

My problem:

I have a bunch of stored procedures that i call to insert records into
various tables. For each stored procedure i call, there is an output
parameter integer, which becomes one of the parameters to the next stored
procedure call. I haved learned that i will need to use an ADO Command
object in order to retrieve the parameter.

I have successfully implemented code to run all the stored procedures using
ADO Command objects. The problem is, when i put a BeginTrans on the ADO
Connection object the Execute called from the Command object causes the
Connection object to crash with an error message similar to:

"Transaction cannot have multiple recordsets with this cursor type. Change
the cursor type, commit the transaction, or close one of the recordsets."

Here is a snippet of the VBcode:

dsConn.BeginTrans
Dim iV1 as Integer
Sql = "Test"
Set dsCommand = Nothing
With dsCommand
.CommandType = adCmdStoredProc
.CommandText = "ins_josh"
.ActiveConnection = dsConn
.Parameters.Append .CreateParameter("@tran_id", adInteger,
adParamOutput, , -1)
.Parameters.Append .CreateParameter("@var1", adInteger,
adParamInput, , lRows)
.Parameters.Append .CreateParameter("@var2", adVarChar,
adParamInput, 20) ', Len(Sql), Sql)

.Execute

iV1 = .Parameters("@tran_id")
Debug.Print fV1
End With

Basically ins_josh is a test procedure that takes an int var1 and a
varchar(20) string var2 and inserts it into a table and returns @tran_id as
the output parameter.

the stored procedure looks something like this:

CREATE PROCEDURE ins_josh
(
@tran_id int output,
@var1 int,
@var2 varchar(20) = null
)
as
BEGIN
BEGIN TRAN
insert into josh_tran
(tran_id, var1, var2)
values
(@tran_id, @var1, @var2)
IF @@ERROR != 0
goto return_error

COMMIT TRAN
return (0)

END
return_error:
ROLLBACK TRAN
return (-1)

The strange thing about this ADO command is that if i omit the varchar
parameter it executes properly, it only fails when i try to pass the string
into the stored procedure. Any ideas why this occurs?


thanks,
rsekar