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; 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 a possible *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 behavior, but:
1)	Is there a way that SQL Server 2012 is smart enough to know about this change and update any "LIVE" queries (server side cursor maybe?) when it's underlying single table is changed by another process or another query?  
2)	Is there a way to detect table changes and only re-query then?

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?  

Please also let me know if the TAdoQuery.Edit / .Post is a good/bad idea.  At what point is the SQL Server table too large to do the TADOQuery.Edit using dynamic queries?

Thanks for any tips / links.
0
mynameis
5/8/2013 3:35:54 PM
embarcadero.delphi.oodesign 456 articles. 0 followers. Follow

0 Replies
1223 Views

Similar Articles

[PageSpeed] 6

Reply:

Similar Artilces:

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: TADOQuer...

"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 ...

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; ...

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...

"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) ...

"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...

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...

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...

Is there a "Starts With" or "Contains" I can use in my SQL query ??
Hi,   I have a piece of code written where I am trying to pull out certian fields based on them containing a certain letter eg C or S ???? Is there a simple piece of code I can use for this. At the moment I am using "AND sl.Name IN ('CDL') " & _.......but this will not return the rows that contain CDL ?  Any Ideas much APPRECIATED ?  You would use the LIKE keyword for that:http://msdn.microsoft.com/en-us/library/ms179859.aspxLike "AD sl.Name LIKE '%CDL%'"That will return anything where that name has CDL in it. Cheers, &nb...

No "Update" or "Delete"!
Hi,I am using the dataset wizard to connect to a SQL database. The wizard creates my dataset and generates  "Select" and "Insert" statements but no "Update" or "delete". What can be the reason? By the way, this happens only on certain tables and not on others. I just can't figure out what the difference between these tables is. And, they all have a primary key. Actually I found this error before, running the configuration for the table again gives me that, sometimes I had to delete the table and start again. Hope this helps, let me...

SQL Server 2005 error: "Incorrect syntax near "-" "
Hello, I have an application written in visual basic which connects to a SQL Server 2005In DB of SQL Server I have many tables. Table names can contain chars like: "-",  "\",  ".", etc. When I execute the instruction in visual basic sTableName= "Table-3" ' or ""Table-3"" or "Table_1" or sTableName= """" + "Table_1" + """"   -> """" = 4 quates (2 for string identify and two for writing in string a quate chr(34)->" strQu...

NOT SOLVED "Delphi.Personality" and platform "win32" missing or unavailable
Hello, Just installed (21st February 2012) Delphi XE2 Starter. Build 16.0.4276.44006 *Installed Updates* (RAD Studio XE2 Delphi and C++ Builder Update 1) (BTW why can't this info be copied instead of typing from Help>About) Got: *[DCC Error] Compiler for personality "Delphi.Personality" and platform "win32" missing or unavailable* when trying to run 1st Sample VBlock (BTW why there is no standard Hello World to show that IDE works?) *In spite of CASE SOLVED:* Report #: 98901 Status: Closed "Delphi.Personality" and platform "Win32...

Delphi 2007 assert
The assertion message from Delphi 2007 is... "(!"SetThreadContext failed")" in ..\win32src\thread32.cpp at line 412 Any way I can patch this out? CodeGear™ Delphi® 2007 for Win32® Version 11.0.2902.10471 El 18/01/2014 21:48, Michael Leahy escribió: > The assertion message from Delphi 2007 is... > > "(!"SetThreadContext failed")" in ..\win32src\thread32.cpp at line 412 > > Any way I can patch this out? > > CodeGear™ Delphi® 2007 for Win32® Version 11.0.2902.10471 > An unofficial fix fo...

Web resources about - How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server - embarcadero.delphi.oodesign

Resources last updated: 11/30/2015 8:02:42 PM