Update while within Select cursor using VB and ODBC API 
Author Message
 Update while within Select cursor using VB and ODBC API

Do I need to create a 2nd connection if I want to open a cursor and then
perform updates based on the selected rows.  A very simplified version of
the VB6 code is below.  When I run this using a single connection I get an
error from the SQL Server ODBC driver saying that the connection is busy
with the results of another hStmt.  This and several other calls are all
part of a single transaction so I do not want to have to use two connections
and then manage two transactions if possible.

SQLExecute(Select * from table1)
SQLFetch
Do while SQL_SUCCESS
    SQLExecDirect(Update table2 set column1 = 'value')
    SQLFetch
Loop
Close Cursor

Any help would be greatly appreciated.

-Vincent Beggs



Sat, 15 Dec 2001 03:00:00 GMT
 Update while within Select cursor using VB and ODBC API

Yes.  You need to do your update processing on another connection.

Bob Dunlop


Quote:
> Do I need to create a 2nd connection if I want to open a cursor and then
> perform updates based on the selected rows.  A very simplified version of
> the VB6 code is below.  When I run this using a single connection I get an
> error from the SQL Server ODBC driver saying that the connection is busy
> with the results of another hStmt.  This and several other calls are all
> part of a single transaction so I do not want to have to use two
connections
> and then manage two transactions if possible.

> SQLExecute(Select * from table1)
> SQLFetch
> Do while SQL_SUCCESS
>     SQLExecDirect(Update table2 set column1 = 'value')
>     SQLFetch
> Loop
> Close Cursor

> Any help would be greatly appreciated.

> -Vincent Beggs



Mon, 17 Dec 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Dealing with Cursors thru VB 4.0 ODBC API

2. Dealing with Cursors from a VB 4.0/ODBC API application

3. A problem about ODBC API cursor positioned update and delete

4. Create ODBC DSN for SQL Server using VB and API

5. Using the ODBC APIs with VB.Net

6. SQL Select/Update VS using Cursors

7. ODBC: Updating using cursor library and C++

8. Problems with ODBC Cursor library when using Update

9. Positioned update using ODBC cursor library

10. Excel ODBC Driver and error 42000 while updating using the cursor library

11. SELECT...FOR UPDATE, cursor updates

12. problem with ODBC in Excel 5.0 using VBA and ODBC API calls


 
Powered by phpBB® Forum Software