How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server [Edit]

How to create a Delphi 7 ADO "LIVE" query (one SQL Server table only) that once a table field is edited - the underlying query results are updated.  Kind of like the "LIVE" query concept from the BDE etc.  

Below is the ADO query that I am using and it generally *seems* to work well where once a field is edited (via TADOQuery.Edit, .Post etc), the query result are also changed.  I think it is true that after an edit the change is always visible in the same program using the same query object.  

{code}procedure ExecuteQryTable( Connection: TADOConnection; Qry: TADOQuery; Sql: string; Params: Variant; OpenQuery: Boolean );
    begin
      assert( Connection <> nil);
      assert( Qry <> nil);
      assert( Sql <> '');
      if Qry.Active then
        Qry.Close;
      Qry.Filtered := False;
      Qry.Filter := '';
      Qry.SQL.Text := Sql;
      AddParametersToQry(Connection, qry, Params);
      Qry.Connection := Connection;
      //qry.CursorLocation := clUseClient;    // Default
      Qry.CursorType := ctDynamic;          // Dynamic cursor: data modifications, deletions, and insertions by other users are visible; scrolling forward and back allowed.
      Qry.LockType := ltOptimistic;         // Optimistic locks: locked are initiated on a record-by-record basis, but only when updates are applied and a record is being updated.
      if OpenQuery then
        Qry.Open;
    end;
{code}

However, I am running into an *issue* where I sometimes need to run a separate "Delete query" on this same table and thus the row changes are not reflected in the original "LIVE query".  I suppose this seems like normal standard behaviour.. but is there a way that SQL Server 2012 is so smart that it knows to update "LIVE" queries (server side cursor maybe?) when it's underlying single table is changed by another process or another query?

The ADO Locate call on the original "LIVE" query says a record exists but in SSMS the records is missing because it was deleted by a  separate SQL Delete query.  I guess this behaviour makes sense, that if the row was not edited by the same query how can the query know the record is missing?

Any solutions to this or do I have to do what I suspect and that is re-query / refresh the "LIVE" original query every time a separate update/delete query has been run against the same underlying single table?  Or maybe there is a way to detect table changes and then re-query only then to speed up things?

Thanks for any tips / links.

P.S.  Please also let me know if the TAdoQuery.Edit and .Post is a good/bad idea.  And at what point is the SQL Server table too large to do this with dynamic queries...?

Edited by: mynameis V on May 8, 2013 7:53 AM

Edited by: mynameis V on May 8, 2013 8:10 AM
0
mynameis
5/8/2013 3:17:05 PM
embarcadero.delphi.non-tech 5933 articles. 1 followers. Follow

2 Replies
2680 Views

Similar Articles

[PageSpeed] 32

On 5/8/2013 8:17 AM, mynameis V wrote:
>
> Thanks for any tips / links.
>

The first tip is to post it in the appropriate newsgroup.  And 
non-technical isn't that group.

David Erbas-White
0
David
5/8/2013 3:21:27 PM
Hello.

I am pretty sure you can not do such thing in ADO but there is an Event Notification mechanism in SQL server. In theory, SQL Server could notify your application about changes made on this table, but I've never implemented such feature so I can not tell you more details. It could be useful for scenerio where you have mutliple applications working on the same table. If you have only one instance of your application then you should just notify other part of your application about deleted row. You could do t
his in AfterDelete event of your query.

You have two choices which come to my mind (haven't used ADO for 3 years now, so there could be more).

1. As you said, refresh the whole query.
2. Delete row from your edit query . This is possible. You just need to create a disconnected recordset. The pros are obvious, no reloading query - it will be very fast. But there are some drawbacks - you need to change the way you are posting your edits to the table because the query will be disconnected from the database.

Regards.
0
Dimitrij
5/8/2013 8:51:43 PM
Reply:

Similar Artilces:

How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server
How to create a Delphi 7 ADO "LIVE" query (one SQL Server table only) that once a table field is edited - the underlying query results are updated. Kind of like the "LIVE" query concept from the BDE etc. Below is the ADO query that I am using and it generally *seems* to work well where once a field is edited (via TADOQuery.Edit and .Post), the query result are also changed. I think it is true that after an edit the change is always visible in the same program using the same query object. {code}procedure ExecuteQryTable( Connection: TADOConnection; Qry: TADOQuery...

Error: "Query: dataset not in Edit mode": in delphi code for Rave Report [Edit]
I am trying to write a Delphi code (Delphi 2010) to print a rave report from the access database; I am using RVSystemprint method; q1 is a query name. I get error message " q1: dataset not in edit or insert mode", at the line: while not q1.Eof. Even though I added q1.Edit or q1.insert, it doesn't work. Database's readonly property is FALSE. What is missing in the code? Thanks. procedure TForm1.rsysPrint(Sender: TObject); begin with Sender as TBaseReport do begin ..... q1.Open; q1.Edit; q1.first; while not q1.Eof do begin ... end; .... ...

"Sneak Preview: Delphi 2011 is Delphi XE"
Today, Delphi's Welcome page showed this: Sneak Preview: Delphi 2011 is Delphi XE http://www.embarcadero.com/rad-studio-xe-preview When trying to watch the video, it gives an error message: Server not found: rtmp://etnastream01.embarcadero.com/vod/ How nice :-( Achim Achim Kalwa wrote: > Sneak Preview: Delphi 2011 is Delphi XE > http://www.embarcadero.com/rad-studio-xe-preview > > When trying to watch the video, it gives an error message: > > Server not found: > rtmp://etnastream01.embarcadero.com/vod/ Works for me on second try (first ...

regarding "in" and "=" in a SQL query
Hi, I am using a sybase database client . When i use "in" in a complex nested query say "select * from emp where emp_id in (select emp_id ..........)" then the execution time is really slow but in the same nested query when i use = as in "select * from emp where emp_id = (select emp_id ..........)", then the query is fast. Is there any particular reason for the same? Regards, Supreeth The obvious difference is that the "=" implies that the subquery can only return a single value as opposed to the "in" which implies that th...

How to give "And" and "OR" in my sql query?
      My layout (.aspx) have 3 TextBox and 2 Dropdownlist and 1 Button which just looks like Search KeyWord 【Texbox1】DorpDownList1 (have 2 items "And" and "Or")  【TextBox2】DorpDownList2 (have 2 items "And" and "Or") 【TextBox3】 Button ( this Button is for submit query)   1 "txtQuery" style="Z-INDEX: 101; LEFT: 152px; POSITION: absolute; TOP: 32px" runat="server" 2 Width="264px"> 3 "ddlBoolean" style="Z-INDEX: 102; LEFT: 152px; ...

no "enable editing" "enable deleting" "enable updating"
In my visual studio 2005 beta1,there is no "enable editing", "enable deleting", "enable updating"options. why? is the vision wrong??thanks! Which control are you talking about?/Fredrik Normén - fredrikn @ twitterMicrosoft MVP, MCSD, MCAD, MCTASPInsidersMy Blog "gridview" control!! I have, so you should also have it if you create a data source for the GridView control in the design view and make sure you have specified the Insert and Update Command for the DataSource. You can also add a CommandField to the GridView columns <asp:CommandFi...

[Delphi XE2] "Search projects" missing in "Add to Project" dialog box [Edit]
"Search projects" missing in "Add to Project" dialog box when using windows classic theme (windows 7 64bits). Notepad on the contrary still have the "Seacrh ..." box in its open file dialog. Check the images below(they are not mine but show the issue) This one does have the "search document" box in the upper right corner. http://img98.imageshack.us/img98/9518/dialog2.png This one does not have it. http://img291.imageshack.us/img291/4793/dialogl.png Any help on this matter will be greatly appreciated. ...

"Assertion Failed" with ADO connection, Windows 7, Delphi 2007
I have Delphi 2007 for Win 32 installed on both the XP and Win7 OS of my dual boot machine. In Win 7, when I use an ADO connection on a simple Access database, it runs fine, until I reset the program. When I click the Program Reset, I get an error "Assertion Failed". If I click this several times, it goes away, and the program resets. Note: I can build exactly the same program in XP with no problems. What does this error mean? What should i do about it? Thanks Ken Moore Ken Moore wrote: > I have Delphi 2007 for Win 32 installed on both the XP and Win7 OS of > my...

"Mricrosoft SQL Server" or "Mricrosoft SQL Server Dababase File
Hi: Trying to understand this logic: We can choose: "Mricrosoft SQL Server" or "Mricrosoft SQL Server Dababase File" as our data source. I understand that if we choose: "Mricrosoft SQL Server", the database is located in server, such as: c:\program files\Microsoft SQL Server\MSSQL1\Data\Northwind.mdf But if we choose: "Mricrosoft SQL Server Dababase File", what will it happen? does that mean it will create a copy of a database (say Northwind.mdf) to my current web application foler, say in: App_Data foder? What is the main advantage (or purpose) ...

Multiple "AND" and "OR" Statements in an SQL Query
Hello,  I've got a table called "plants" which, up-until-recently, was being searched by category using the following SQL (and using the CategoryID field): SELECT plants.Latin_Name, Size.ekm_ID, plants.Common_Name, plants.Short_Description, plants.Thumb_URL, plants.CategoryID, plants.ProductID, Size.Size, Size.Price, Size.MostPopular FROM plants INNER JOIN Size ON plants.CategoryID = Size.CategoryID AND plants.ProductID = Size.ProductID WHERE (plants.CategoryID = @CategoryID) AND (Size.MostPopular = 'True') AND (Size.Availability = 'True') ORDER BY plants.Latin_Name  I now want ...

"Before Update" and "After Update Of" triggers
Hi all, In a row-level before update trigger I set the value of a column ("SomeColumn) to some value (the value is only changed by this trigger, not by the update statement itself) Now I notice that the row-level trigger defined as "after update of SomeColumn" doesn't fire when I change the value of this column in the before update trigger. It does fire (as expected) when the change comes from the update statement itself) Questions: - Is this expected behaviour? - how can I uniformly trigger a change of a column wether set by the update statement or by...

"Using other editions of SQL Server for report data sources and/or the report server database" is not supported in this edition of Reporting Services
Hi My project is in .NET 2003 i.e. framework 1.1 and database in SQLServer 2000. But the reports have been developed using SQLServer 2005 Reporting Services. Now when I am trying to deploy them through deployment project of .NET its giving me following error:"Using other editions of SQL Server for report data sources and/or the report server database" is not supported in this edition of Reporting Services.  Now I am really confused with this. Can any one please guide me regarding this ASAP. Thanks, Falguni    You will need to use the SQL Server ...

Delphi install issue "Cannot create drectory f:\temp\mia3793.tmp\setup.exe" [Edit]
I had Delphi 7, Delphi XE2 and Delphi XE3 all installed in my machine. All fine and working fine, all licensed. My folders were not well organized (a mess) so I decided to uninstall XE3 and XE2 and start from scratch. Same as I did always, I mounted the XE3 ISO thru Virtual Clonedrive and first thing I notice is that the path suggested for demos was: F:\temp\mia3793.tmp\Setup.exe\RAD Studio\9.0\Samples\ I went ahead and modified it to something more sensible. Then after a while I get the error "Cannot create the directory f:\temp\mia3793.tmp\Setup.exe. A file with this name alre...

Can't find "Delphi 7.1 Update
Using: Delphi 7 Enterprise Browsing the registered users downloads page on EDN to find the updates, I found an update for "Delphi 7 General Update 7.1 / Enterprise / English". However I am unable to find the "Delphi 7.1 Update - Database supplemental" update. Its available on CodeCentral (search for ID 22051 ) but as a beta. Does anyone have a direct download link from the Embarcadero website for this update? TIA. -- Steve Faleiro On 3/11/2012 21:08, Steve Faleiro wrote: > Using: Delphi 7 Enterprise > > Browsing the registered users downloa...

Web resources about - How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server [Edit] - embarcadero.delphi.non-tech

Resources last updated: 12/22/2015 4:48:51 AM