Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2012
    Posts
    12

    Question Unanswered: TADOQuery Error While Executing The SQL Second Time

    Hi All,

    I am getting a strange behavior by TADOQuery component 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

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Which language you are using for your application development?

  3. #3
    Join Date
    Apr 2012
    Posts
    12
    Quote Originally Posted by imex View Post
    Hi,

    Which language you are using for your application development?
    Hi,

    I am using Delphi7 for application development, I have already asked this question, but no success.


    Thank You And With Best Regards.

    Vishal

  4. #4
    Join Date
    Apr 2012
    Posts
    12
    Quote Originally Posted by imex View Post
    Hi,

    Which language you are using for your application development?
    Hi,

    I am using Delphi7.


    With Best Regards.

    Vishal

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    Hi,
    I have no experience in connection with ADO, but I believe it is best to choose "SQL Server Native Client" as provider.
    I did not see in your ConnectionString the "Initial Catalog" parameter, which should indicate the name of the database.
    About the Connection property of ADOQuery, I believe you need to configure it only once. Unless you are creating the ADOQuery dynamically via code.
    Follows an example of a ConnectionString:

    ConnectionString := 'Provider=SQLNCLI10.1;Persist Security Info=False;User ID=UserName;Password=Passowrd;Initial Catalog=DatabaseName;Data Source=ServerName;';

    Hope this helps.

  6. #6
    Join Date
    Apr 2012
    Posts
    12
    Hi imex.

    Thank you again for the reply.


    Let me try this on Monday, I would get back to you in all the cases.



    With Best Regards.

    Vishal

  7. #7
    Join Date
    Apr 2012
    Posts
    12
    Hi imex,

    I tried the connection string you suggested, if I use the provide you said, that is not connecting at all. In Initial Catalog when I am giving database name there also it is giving an error.


    With Best Regards.

    Vishal

  8. #8
    Join Date
    Apr 2012
    Posts
    213
    Hi,
    The test was done on the computer where you installed the delphi?
    If you double-click on the ADOConnection, click the Build button, click the Provider tab, you see the "SQL Server Native Client"?
    If you then click the Connection tab and fill in item 3, there is added the parameter Initial Catalog in the connection string?

  9. #9
    Join Date
    Apr 2012
    Posts
    12
    Hi imex,

    As you said, I tried but there if I do not provide server address, then connection it self is failing, even if I provide rest of all information.


    I have kept one TADOConnection component and 2 TADOQuery components and one TButton component at design time in Delphi7, using SQL Server 2005, 2008.

    I have written the code at two places:

    1. FormShow event:

    Here I am making database connection to TADOConnection component and after that assigning all TADOQuery components to it.

    2. Button click event:

    Here I am first allpying the role to a perticular use and the tring to execute the sql, where it is failing to execute the sql.


    Code is as follows:

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

    procedure TForm1.FormShow(Sender: TObject);
    var
    iCount : Integer;
    begin

    ADOConnection1.Connected := False;


    //SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component

    //but while applying the role there is no error.

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

    OR

    //SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component

    //but while applying the role there is no error.

    ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
    + 'Initial Catalog=My Database Name;Data Source= My data Source';

    OR

    //SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component

    //but while applying the role there is no error.

    ADOConnection1.ConnectionString := 'Provider=SQLNCLI10.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
    + 'Data Source=My data Source';



    Only following connection string works for all query components, i.e. there is not error while executing the sql with any query component, but when I provide "Data Source=My data Source" it is not connecting at all using TADOConnection component.


    ADOConnection1.ConnectionString := 'Provider=SQLNCLI10.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
    + 'Initial Catalog=My Database Name;Data Source= 3.204.35.38,51722';



    ADOConnection1.Connected := True;

    ADOConnection1.BeginTrans;
    for iCount := 0 to ComponentCount - 1 do
    begin
    if Components[iCount] is TADOQuery then
    begin
    TADOQuery(Components[iCount]).Connection := ADOConnection1;
    end;
    end;
    ADOConnection1.CommitTrans;
    end;


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


    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;

    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add('Select * from Table1');
    ADOQuery1.Open; \\Getting error in this statement

    ADOQuery2.Close;
    ADOQuery2.SQL.Clear;
    ADOQuery2.SQL.Add('Select * from Table1');
    ADOQuery2.Open; \\Getting error in this statement
    end;
    --------------------------------------------------------------



    With Best Regards.

    Vishal

  10. #10
    Join Date
    Apr 2012
    Posts
    213
    Only following connection string works for all query components, i.e. there is not error while executing the sql with any query component, but when I provide "Data Source=My data Source" it is not connecting at all using TADOConnection component.


    ADOConnection1.ConnectionString := 'Provider=SQLNCLI10.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
    + 'Initial Catalog=My Database Name;Data Source= 3.204.35.38,51722';
    Hi,

    Try to fill the Data Source parameter with the contents of the "Server name" of the connection window of SQL Server Management Studio.
    If that fails, post the error message.

    Hope this helps.

  11. #11
    Join Date
    Apr 2012
    Posts
    12
    Hi imex,

    I need to give DataSource as DSN Data Source Name, when I use "'Provider=SQLNCLI10.1" and when I give DataSource Name as DSN Datya Source Name then there is an error coming : "Named Pipes Provider : Could not connect to a SQL setver connection [53]"


    With Best Regards.

    Vishal

  12. #12
    Join Date
    Apr 2012
    Posts
    213
    You have SQL Server Management Studio installed on this computer?
    Can you connect filling the Server Name in the Connection window with the content you are using in the DataSource parameter?

  13. #13
    Join Date
    May 2012
    Posts
    1

    Thanks

    Thanks Very Good

  14. #14
    Join Date
    Apr 2012
    Posts
    12
    Hi imex and Hi Dear All,

    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;

    -----------------------------------------------------------------
    Code:
    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

  15. #15
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    The ADOConnection is in a DataModule?
    In which event the ConnectionString is being set?
    The Connected property is set equal to true in what event? In Design Time or run-time?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •