Re: Function sequence error with ODBCExpress

On Tue, 1 Aug 2000, Ken J. Wright wrote:

> That error can occur in many odbc sql functions and for lots of different
> reasons. Can you provide a trace log containing the area where the error
> ocurred?
There is a table which rows should be modified. The solution that creating
a modal form on which a record can be modified per column seemed to be
good instead of modifying the record within the table grid. We send the
INSERT statement in a transaction with a COMMIT, and then the modal form
is destroying. At this moment the error appears at once.

I would like to quote to comments from the ODBCExpress news site for
explaining this problem a bit more:

------------------------------------------------------------------------
Martin Ziemer wrote:

> a month ago, I reported a problem with a "function sequence error"
> caused by two transactions.
>
> Did your investigations find the reason?

Yes, to quote from the ODBC Programmer's Reference:

For some ODBC drivers, Commit "closes and deletes all open cursors on
all statements associated with the connection and discards all pending
results".

In other words, if you call Commit on a connection while having open
result sets on the connection, the result sets could be closed by the
connection, depending on the ODBC driver you're using. Any "fetch"
calls on these result sets after the Commit will therefore result in a
"function sequence error".

------------------------------------------------------------------------

> I have experienced a problem with ODBCExpress. I have a similar problem to
> described by Martin Ziemer with error "Function sequence error". I have two
> transactions: one with isolation level set to "read commited" and outher to
> "read uncommited". I'm using ODBCExpress 4.52, MDAC 2.0 and SQL Server 6.5 .
> Related error occurs when I try to insert and later I abort (cancel) the
> operation. I do many inserts and updates in others tables.
> To do it I use "open transactions" (I call the StartTransact method to begin
> the manual transactions) and I inserted one row in the master table that
> have others
> dependents tables. In sequence I insert many and many rows in your
> dependents tables.
> Do you have any idea how I can solve this problem ?

Commiting or Aborting the inserted rows will work fine, whether you're inserting
the rows positionally or using an INSERT SQL statement, however after you commit
or abort (or probably before you commit or abort) you must close the result
sets, since the ODBC driver you're using will cause the result set cursors to be
closed on the commit or abort (and tehreby causing function sequence errors when
you try to access the result sets afterwards). Afterwards you can open the
result sets again.

------------------------------------------------------------------------

End of quoting. Is the solution what the answers suggest? It would be
great if the result sets need not to be closed. What is your solution for
this?

Thank you in advance, very much!

Regards, Zoltan