Update the same record by different client in SQL Server 2008 R2

I am developing an application in Delphi-2010 using SQL Server 2008 R2 in network Mode.

My problem is that in certain cases, multiple clients open the same record for update.

The first client can update the record but the others can't because SQL SERVER can't find the record in question because it has been modified. The application should allow the 2 updates without informing the client.

The table contains many fields nd' the client can update anyone of it for that am not using SQL statement nd' am using UPDATEBATCH().

// Press UPDATE
procedure TarticleEditForm.saveButtonClick(Sender: TObject);
begin
 if (articleCode.Text <> '') AND (counter.Text <> '') AND (articleLabel.Text <> '') AND      (tbCombo.Text <> '') AND (griffeCombo.Text <> '') then begin
      ADOArticleFind.SQL.Text := 'SELECT * FROM article WHERE ID<>''' + ADOArticle.FieldByName('ID').Value + ''' AND article=''' + articleCode.Text + ''' AND mode=''' + modeCombo.Text + ''' AND counter=''' + counter.Text + '''';
      ADOArticleFind.Open;
      // UPDATE
      if ADOArticleFind.RecordCount = 0 then begin
           // SET Date Modification
           ADOArticle.FieldByName('dateModification').Value := Now;
           ADOArticle.FieldByName('modifiePar').Value := mainForm.user;
           ADOArticle.UpdateBatch();
           // Update ArticleColor/ArticleTissu tables
           ADOArticleColor.SQL.Text := 'UPDATE articleColor SET article=''' + articleCode.Text + ''', mode=''' + modeCombo.Text + ''', counter=''' + counter.Text + ''' WHERE article=''' + tmpArticleCode + ''' AND mode=''' + tmpMode + ''' AND counter=''' + tmpCounter + '''';
           ADOArticleColor.ExecSQL;
           ADOArticleTissu.SQL.Text := 'UPDATE articleTissu SET article=''' + articleCode.Text + ''', mode=''' + modeCombo.Text + ''', counter=''' + counter.Text + ''' WHERE article=''' + tmpArticleCode + ''' AND mode=''' + tmpMode + ''' AND counter=''' + tmpCounter + '''';
           ADOArticleTissu.ExecSQL;
           // create event log
           mainForm.ADOUser.SQL.Text := 'SELECT * FROM users WHERE online=1 AND editArticleEvent=1 AND username<>''' + mainForm.user + '''';
           mainForm.ADOUser.Open;
           while not mainForm.ADOUser.Recordset.EOF do begin
                mainForm.ADOMainEventLog.Insert;
                mainForm.ADOMainEventLog.FieldByName('event').Value := 'Article modifié:开发者_C百科 ' + designationCombo.Text + ' ' + saisonCombo.Text + ' ' + articleCode.Text + '-' + modeCombo.Text + counter.Text + ' de ' + griffeCombo.Text;
                mainForm.ADOMainEventLog.FieldByName('eventFrom').Value := mainForm.user;
                mainForm.ADOMainEventLog.FieldByName('eventTo').Value := mainForm.ADOUser.FieldByName('username').Value;
                mainForm.ADOMainEventLog.FieldByName('eventType').Value := 'editArticleEvent';
                mainForm.ADOMainEventLog.UpdateBatch();
                mainForm.ADOUser.Next;
           end;
           // Finish
           Self.Close;
      end
      else begin
           MessageBox(Application.Handle, 'Cet article existe déja.', 'GET© Driver', MB_ICONWARNING);
           articleCode.SetFocus;
      end;
 end
 else
      MessageBox(Application.Handle, 'Champs obligatoire(s) manquant(s).', 'GET© Driver', MB_ICONWARNING);
end;

---

I always prefer to use pure SQL to make stuff happen in databases, instead of relying on the database-abstraction in the datasets/tables.

Query1.SQL.Text:= 'UPDATE table1 SET a=:newvalue WHERE A=:oldvalue';
Query1.ParamByName('newvalue').AsString:= '1';
Query1.ParamByName('oldvalue').AsString:= '2';
Query1.Prepare;
Query1.ExecSQL;

Using code like this you can set as many concurrent updates to SQL-server as you'd like.

---

Since you say that you use updatebatch I assume that you use TADODataSet (or perhaps TADOTable, TADOQuery).

How ADO builds the update statement is controlled by the recordset property Update Criteria. Default value is adCriteriaUpdCols which means that the update's where clause compares all modified fields against the old/original value. To make updatebatch only use the key columns you can do something like this.

ADODataSet1.CommandText := 'select * from SomeTable';

ADODataSet1.Open;
ADODataSet1.Recordset.Properties['Update Criteria'].Value := adCriteriaKey;

ADODataSet1.Edit;
ADODataSet1.FieldByName('SomeColumn').AsString := 'New value';
ADODataSet1.Post;

ADODataSet1.UpdateBatch();

The above code for a TADOQuery would look like this.

ADOQuery1.SQL.Text := 'select * from SomeTable';

ADOQuery1.Open;
ADOQuery1.Recordset.Properties['Update Criteria'].Value := adCriteriaKey;

ADOQuery1.Edit;
ADOQuery1.FieldByName('SomeTable').AsString := 'New value';
ADOQuery1.Post;

ADOQuery1.UpdateBatch();

BTW, adCriteriaKey is defined in ADOInt.pas