drop a database in delphi via SQL statement and adoquery

Question

I want to drop a database via an sql statement, my code goes like this using delphi XE and ADO for DB access, server is MSSQL

  aDBConnection.ConnectionString :=  'Provider=SQLOLEDB.1;Integrated    

                   Security=SSPI;Persist Security Info=False;Initial Catalog='
                   + DatabaseName + '; Data Source=' + Servername;      

  aDBConnection.Connected := true;  

  aQuery.Connection := aDBConnection;

  aQuery.add.sql (' drop database ' + DatabaseName );

  aQuery.Execsql.

running many other sql statements with this code sequence is fine, but he refused to drop the database as the claims "database is in use".

Guess this is true as I really have one connection to the database, but how to do other, I need the query to send the sql to the server ...

Answer 1

There is not need to close the connnection explicit, as mentioned by TLama all you need is USE to change your connection. There is also no need for closing of the Datasets, except they are opened with CursorLocation := clUseServer; Even after changing to master (Button_ChangeToMasterClick) you will be able to edit already opened datasets, but since CursorLocation is clUseClient, youe even will be able to DROP the database. Editing after dropping naturally leads to an error.

Const
 aDatabaseName='DeleteMeAfterUse';

procedure TForm3.ButtonCreateOpen_Click(Sender: TObject);
var
  Ads: TAdodataset;
begin
  AC.ConnectionString := 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=127.0.0.1';
  AC.Connected := true;
  AC.Execute('Create DataBase ' + aDatabaseName);
  AC.Execute('Use ' + aDatabaseName);  // change connection to 'DeleteMeAfterUse'
  AC.Execute('Create Table Test (ID int)'); // will be created in DeleteMeAfterUse
  AC.Execute('Insert into Test Values (1)');
  Ads := TAdodataset.Create(self); // Display data on surface
  // Ads.CursorLocation := clUseServer;  **
  // ** don't use clUseServer if you want to delete the database without closing the datasets
  // default is clUseClient

  With Ads do
  begin
    Connection := AC;
    CommandText := 'Select * from Test';
    Active := true;
  end;
  DataSource1.DataSet := Ads; // Conected to a DBGrid for editing
end;

procedure TForm3.Button_ChangeToMasterClick(Sender: TObject);
begin
  AC.Execute('Use master');  // change connection to master , any other database will work too
end;

procedure TForm3.Button_DropDatabaseClick(Sender: TObject);
begin
  AC.Execute('Drop Database ' + aDatabaseName);
end;