MS-SQL : connection is in use by another statement (Views: 21)


MS-SQL : connection is in use by another statement


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':

// dataBaseNameS : string  is the name of the alias (MS-SQL 6.5)
  Query1 := TQuery.Create(Application);
  with Query1 do
    DatabaseName := dataBaseNameS;
    // the exception disappears if the following is removed
    Unidirectional := True;

  Table1 := TTable.Create(Self);
  with Table1 do
    DatabaseName := dataBaseNameS;
    TableName := 'COMPONENT_PLAN';
    UpdateMode := upWhereKeyOnly;

  Table1.FieldByName('PARTNO').AsString := IntToStr(GetTickCount);
  Table1.FieldByName('ID').AsString := 'WWxx';
  Table1.FieldByName('VERSION').AsInteger := 1;
  // the exception will occurr in the next statement:
  //     "Connection is in use by another statement"

<< Back to main page