DelphiFAQ Home Search:

MS-SQL : connection is in use by another statement

 

comments3 comments. Current rating: 5 stars (1 votes). Leave comments and/ or rate it.

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)
begin
  Query1 := TQuery.Create (Application);
  With Query1 do
  begin
    DatabaseName := dataBaseNameS;
    SQL.Text := 'SELECT * FROM ABLESTOP';
    // the exception disappears if the following is removed
    Unidirectional := True;
    Open;
  end;
  ShowMessage ('ok')

  Table1 := TTable.Create (Self);
  With Table1 do
  begin
    DatabaseName := dataBaseNameS;
    TableName := 'COMPONENT_PLAN';
    UpdateMode := upWhereKeyOnly;
    Open
  end;

  Table1.Insert;
  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"
  Table1.Post;
end;

Content-type: text/html

Comments:

2006-09-25, 20:42:39
anonymous from Mexico  
Aun Haciendo todos estos cambios el error sigue ocurriendo
BDE ver 5.01
Delphi 6 Enterprise
SQL Server 2000 Standard


with qryAux do begin
Active:=False;
SQL.Clear;
SQL.Add(' Update tbaRoomList ');
SQL.Add(' Set status = 3 ');
SQL.Add(' Where (idroomlist = '+IntToStr(4)+') ');
Prepare;
// the exception occurr in the next statement:
// 'Connection is in use by another statement'
ExecSQL
end;


2007-04-16, 13:23:12
zabnsiay@dgimcgat.com from Brazil  
rating
2007-11-09, 21:44:15
anonymous from Vietnam  
Đủ má đéo hiểu

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: