Hi All,
I have got the starnge solutions for the strange problem. Till now it is working perfectly.
As I said, the SQL execution would not give error untill I use any second TADOQuery component. But the moment I use any other TADOQuery rather than first one, I get an
error like, "SELECT permision was denied on object <'Table Name'>......".
I have found two solutions:
I am using DSN as Datya Source, so even if I use SQL Sever or Oracle as a database, so there is no need to make much changes.
Solution1:
I have just assigned the TADOQuery No. 1 to any other TaDOQuery which I am going to use for SQL execution as below:
ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=True;'
+ 'User ID= My User ID;Password= My Password;'
+ 'Data Source= My DSN Data Source Name';
ADOConnection1.Connected := True;
ADOQuery1.Connection := ADOConnection1;
ADOQuery2.Close;
ADOQuery2 := ADOQuery1; //Assigning ADOQuery1 to ADOQuery2
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add('Select * from Table1');
ADOQuery2.Open; // OR ADOQuery2.Active := True;
In above sample code, there is no effect whether I use ADOQuery1 for execution for any time or not, i.e. for the first time or never, everything works fine.
So, whenever I need to use any other ADOQuery rather than ADOQuery1, I just assign the ADOQuery1 to the respective ADOQuery component before SQL execution i.e. before
assigning SQL to the respective TADOQuery component as below.
ADOQuery_N := ADOQuery1;
Solution 2:
I have used TClientDataSet and TDataSetProvider component with TADOQuery component as follows:
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Table1');
DataSetProvider2.DataSet := ADOQuery1; //As It Is Required, Assigning ADOQuery1 to TDataSetProvider2
ClientDataSet2.SetProvider(DataSetProvider2);
ClientDataSet2.Active := True;
ClientDataSet2.First;
for iCount := 0 to ClientDataSet2.RecordCount - 1 do
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Table2 where Column_Name = ' + QuotedStr(ClientDataSet2.FieldByName('Column_N_Name').AsString));
DataSetProvider3.DataSet := ADOQuery1; //As It Is Required, Assigning ADOQuery1 to TDataSetProvider3
ClientDataSet3.SetProvider(DataSetProvider3);
ClientDataSet3.Active := True;
//Workout with ClientDataset3 in your code, then clear the data in it as shown below.
//So, in the loop we could workout freshly with ClientDataSet3.
ClientDataSet3.DisableControls;
ClientDataSet3.EmptyDataSet;
ClientDataSet3.EnableControls;
ClientDataSet2.Next;
end;
Kindly Let me know, if there is anything to say from your side.
With Best Regards.
Vishal
TADOQuery Error While Executing The SQL Second Tim
Posted - 05/05/2012 : 06:39:09
