TMSQuery and Edit, Post

[laegteskov]

I'm a little but worried about using SELECT-queries to update data in my application after this experience. My code is this:

Code: Select all

procedure AssignCampaignAddressesToPhonerPool(ImportListID, Count : integer);
var
  QueryMS : TMSQuery;
  P : integer;
begin
  QueryMS:=TMSQuery.Create(nil);
  QueryMS.Connection:=ClientDB;
  QueryMS.SQL.Add('SELECT CampaignAddressStatusID FROM CampaignCustomer WHERE ImportListID = ' + IntToStr(ImportListID) + ' AND CampaignAddressStatusID = 1');
  QueryMS.ReadOnly:=False;
  QueryMS.Open;
  P:=0;
  while (Not QueryMS.Eof) And (P1 is changed and this destroys data in the table.

I'm using SDAC ver 4.10.0.9 and Delphi 2007

Hope to get an answer - in the meantime I'm firing 1000+ single SQL-statements because I'm a bit scared to use Queries to edit data directly.

FYI a user named dragan posted a similary post Fri Aug 12, 2005 11:50 am with the subject "What is this????" but I don't think there was an answer to this.

Thanks,

Lasse

[Antaeus]

When you open a table for edit, you should make sure that it has a primary key, and all the fields included in the primary key appear in your SELECT statement.
Regarding structure of the CampaignCustomer table, you should replace this piece of you query:

Code: Select all

SELECT CampaignAddressStatusID FROM

with this one:

Code: Select all

SELECT CampaignAddressStatusID, CampaignID, CustomerID FROM

Another way to avoid this problem in the future is to set the TMSQuery.Options.UniqueRecords to True.

[laegteskov]

Ok, thank you for the answer - I was getting a bit worried. Shouldn't the default behaviour for this be that it doesn't change data when an error occours?

[Antaeus]

SDAC cannot determine how many records will be modified before an update query has been performed. After an update command has been performed, SDAC gets from the server count of affected rows, and if the value differs from one, raises an exception. To avoid this problem, you can wrap Update/Post in a transaction.

[laegteskov]

Thanks I'm back on track

[dpoulin]

Personnally, I do not think this is right. I use a select with only few fields I need, and because a field in my where is not in the select statement. After editing my with msquery.edit I post, and it update 2 or multiple rows.
Have a look
Select F01,F1000,F126,F1001,F2129,F2130,F2131,F30,F31,F33
from price_TAB where
(F01=:F01) and
(F126=:F126) and
(F1000=:F1000) and
(F113=:F113) and
(F2296=0) and
((F1001 IS NULL) or (F1001 < 6))

if my F113 isn't in the select statement (not the where part) (f113 is part of my key)
it can update more than 1 row even if my where point only on one row.
my query has a recordcount of 1 but it can update 2-3-4-5...
Then, it gives an error, something like update failed, 2 records found.
But it actually did updated 2 row. anyways that is the same behavior has the other post.
This is wrong, I don't see why I need to had a field if I don't use it...
Otherwise, very good product, it is mostly the only thing I see which has no sense