TADOQuery Error While Executing The SQL Second Tim

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

TADOQuery Error While Executing The SQL Second Tim

Author

Topic

vishualsoft
Starting Member

6 Posts

Posted - 2012-05-05 : 06:39:09

Hi All,

I am getting a strange behavior by TADOQuery component in Delphi7 on Windows XP and Windows7 operating system. Following issue works fine on Windows7 oprating system, but it gives an error on XP operating system.

Let me explain the schenario, I have one TADOConnection component and two TADOQuery components. The TADOConnection component is having the value for "ConnectionString" property as follows:

ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Password = My Password; User ID = My User ID; Data Source = My Data Source';

I have already applied the role/granted permission to the User ID/Password which I am login to the application.

I have connected TADOConnection component to the TADOQuery components using "Connection" property.

When I execute the first sql then it works fine without any error, but when I execute the second sql that time I am getting following error on Windows XP oprating system.

[Microsoft] [SQL Server Native Client 10.0] [SQL Server] The SELECT permission was denied on the object 'My Table Name', database 'My Database Name', schema 'dbo'

The same code if I execute on Windows7 oprating system it works fine without any error.

Could anybody put focus what could be missing or what could be the issue ?

------------------------------------------------------------------------------------------------------------------------

I have added "Persist Security Info = False;" to a connection string as follows:

ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Persist Security Info = False; Password = My Password; User ID = My User ID; Data Source = My Data Source';

Now, whenever I execute the sql using TADOQuery component, I need to write following statement every time, even if I have already set the "Connection" property of TADOQuery at the beginning, still I need to set this property whenever I execute the sql.

ADOQuery1.Connection := ADOConnection1;

But as I said, On Windows7 there is no need to change the "ConnectionString" property of TADOConnection component and no need to set the "Connection" property of any TADOQuery component.

Could anybody tell what exactly has to be done ?

If your application is too hugh and lot of places coding changes are required, would anybody agree with that ? And what about testing efforts ? At all the places testing has to be done, right ?

So, I am still in search of proper solution. The question is still unanswered.

If anybody is having any clue or hint or proper solution that would be highly appreciated.

Thanks In Advance.

With Best Regards.

Vishal

vishualsoft
Starting Member

6 Posts

Posted - 2012-05-14 : 12:15:48

Hi,

If I execute the following 2 lines of code before executing sql using any TADOQuery component then that time there is no error, could I get any clue what is the reason, when I EXECUTE FOLLOWING @ LINES OF CODE< WHY I don't get "SELECT permission..." error.

ADoConnection1.Connected := False;
ADoConnection1.Connected := True;

-----------------------------------------------------------------
procedure TForm1.Button1Click(Sender: TObject);
begin

\\"test123" is the password

ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('EXEC sp_setapprole ''APP_USER_APP'', ''test123''');
ADOQuery3.ExecSQL;

ADoConnection1.Connected := False;
ADoConnection1.Connected := True;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Table1');
ADOQuery1.Open; \\No error in this statement

ADoConnection1.Connected := False;
ADoConnection1.Connected := True;
ADOQuery2.Close;
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add('Select * from Table1');
ADOQuery2.Open; \\No error in this statement
end;
-----------------------------------------------------------------

Thanking You And With Best Regards.

Vishal

vishualsoft
Starting Member

6 Posts

Posted - 2012-05-21 : 01:08:36

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources