SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 TADOQuery Error While Executing The SQL Second Tim
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vishualsoft
Starting Member

India
6 Posts

Posted - 05/05/2012 :  06:39:09  Show Profile  Reply with Quote
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

India
6 Posts

Posted - 05/14/2012 :  12:15:48  Show Profile  Reply with Quote

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
Go to Top of Page

vishualsoft
Starting Member

India
6 Posts

Posted - 05/21/2012 :  01:08:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000