Author: Tomas Rutkauskas
MS-SQL : connection is in use by another statement
Answer:
When porting a larger database application (130k LOC) that worked fine with Oracle
and InterBase to MS-SQL (6.5), I frequently got the error message 'connection is in
use by another statement'.
At first, creating a new TDatabase for each TTable/ TQuery seemed to be necessary.
Then I found what was 'wrong' (not really wrong.. :-)
To speed up some of my queries, I had set the property Unidirectional to true.
Delphi creates for such queries only one cursor (versus two for bidirectional
queries or TTables). After removing the assignments of Unidirectional := true the
error message disappeared and everything worked fine.
The following code resulted in the exception 'connection is in use by another
statement':
1 2 // dataBaseNameS : string is the name of the alias (MS-SQL 6.5)3 begin4 Query1 := TQuery.Create(Application);
5 with Query1 do6 begin7 DatabaseName := dataBaseNameS;
8 SQL.Text := 'SELECT * FROM ABLESTOP';
9 // the exception disappears if the following is removed10 Unidirectional := True;
11 Open;
12 end;
13 ShowMessage('ok')
14 15 Table1 := TTable.Create(Self);
16 with Table1 do17 begin18 DatabaseName := dataBaseNameS;
19 TableName := 'COMPONENT_PLAN';
20 UpdateMode := upWhereKeyOnly;
21 Open
22 end;
23 24 Table1.Insert;
25 Table1.FieldByName('PARTNO').AsString := IntToStr(GetTickCount);
26 Table1.FieldByName('ID').AsString := 'WWxx';
27 Table1.FieldByName('VERSION').AsInteger := 1;
28 // the exception will occurr in the next statement:29 // "Connection is in use by another statement"30 Table1.Post;
31 end;