Executing a Stored Procedure which could update a record instead of inserting it.

[sLesae]

Hi everyone,

I have a very specific situation / problem and I was wondering how I should sole it. I have a table called T_DAGTAAK_DETAIL which looks like this :

Code: Select all

CREATE TABLE [dbo].[T_DAGTAAK_DETAIL](
	[DagtaakDetailId] [int] IDENTITY(1,1) NOT NULL,
	[DagtaakId] [int] NOT NULL,
	[ArtikelId] [int] NOT NULL,
	[Aantal] [decimal](11, 4) NOT NULL,
	[Prijs] [float] NOT NULL,
	[Subtotaal]  AS ([Aantal]*[Prijs]),
	[InVerwerking] [decimal](11, 4) NOT NULL,
	[Verwerkt] [decimal](11, 4) NOT NULL,
 CONSTRAINT [PK_T_DAGTAAK_DETAIL] PRIMARY KEY CLUSTERED 
(
	[DagtaakDetailId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And I have a TMSQuery on my form which selects all the data from that table (Using a Master / Detail relation ship based on DagTaakId).

Code: Select all

SELECT [T_DAGTAAK_DETAIL].[DagtaakDetailId]
      ,[T_DAGTAAK_DETAIL].[DagtaakId]
      ,[T_DAGTAAK_DETAIL].[ArtikelId]
      ,[T_DAGTAAK_DETAIL].[Aantal]
      ,[T_DAGTAAK_DETAIL].[Prijs]
      ,[T_DAGTAAK_DETAIL].[Subtotaal]
      ,[T_DAGTAAK_DETAIL].[Verwerkt]
      ,[T_ARTIKEL].[Thumbnail]
  FROM [T_DAGTAAK_DETAIL]
JOIN T_ARTIKEL ON T_DAGTAAK_DETAIL.ArtikelId = T_ARTIKEL .ArtikelId

When the user adds a new record to T_DAGTAAK_DETAIL, the system should check if it has already such a record. If it doesn't, it can simply add the new record, but if it does ... it should update the existing record and add the new Aantal (Qty) to the existing Aantal. So I created the following Stored Procedure :

Code: Select all

CREATE PROCEDURE [dbo].[P_CREATE_DAGTAAK_DETAIL]
( 
  @DagTaakId       AS INT = 0,
  @ArtikelId       AS INT = 0,
  @Aantal          AS DECIMAL( 11, 0 ) = 0,
  @Prijs           AS FLOAT--,
) 
AS
BEGIN
  DECLARE @DagTaakDetailId INT
  
  SET @Prijs = coalesce( @Prijs, 0 )
  
  IF ( EXISTS( SELECT DagtaakDetailId FROM T_DAGTAAK_DETAIL TDD WHERE ArtikelId = @ArtikelId AND DagtaakId = @DagTaakId ) )
  BEGIN
    SELECT @DagTaakDetailId = DagtaakDetailId FROM T_DAGTAAK_DETAIL TDD WHERE ArtikelId = @ArtikelId AND DagtaakId = @DagTaakId
    UPDATE T_DAGTAAK_DETAIL SET Aantal = Aantal + @Aantal WHERE DagtaakId = @DagTaakId AND ArtikelId = @ArtikelId
    RETURN @DagTaakDetailId
  END
  ELSE
  BEGIN
    INSERT INTO T_DAGTAAK_DETAIL
      (
        DagtaakId
       ,ArtikelId
       ,Aantal
       ,Prijs
      )
    VALUES
      (
        @DagTaakId
       ,@ArtikelId
       ,@Aantal
       ,@Prijs
      )
      
    RETURN scope_identity()
  END
END

Next I had to look through the examples, and found that I could simply use the SQLInsert to set up a call to the Stored Procedure instead of executing the actual Insert :

Code: Select all

{:DagtaakDetailId = CALL P_CREATE_DAGTAAK_DETAIL;1 (:DagTaakId, :ArtikelId, :Aantal, :Prijs)}

I added the necessary code to the BeforeUpdateExecucte and AfterUpdateExecute :

Code: Select all

procedure TdtmDagtaak.qryDagtaakDetailAfterUpdateExecute(
  Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
var
  iDagtaakDetailId : Integer;
begin
  inherited;

  if stInsert in StatementTypes then
  begin
    iDagtaakDetailId := Integer(Params.ParamByName('DagtaakDetailId').Value);
    Sender.FieldByName('DagtaakDetailId').ReadOnly := False;
    try
      Sender.FieldByName('DagtaakDetailId').NewValue := iDagtaakDetailId;
    finally
      Sender.FieldByName('DagtaakDetailId').ReadOnly := True;
    end;
  end;
end;

procedure TdtmDagtaak.qryDagtaakDetailBeforeUpdateExecute(
  Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
  inherited;

  if stInsert in StatementTypes then
  begin
    Params.ParamByName('DagtaakDetailId').ParamType := ptResult;
  end;
end;

This seems to be working, but it is giving me some problems as well. Adding a record for which we didn't find a matching DagtaakId and ArtikelId is working fine now. The record gets added and I see the results in my grid.

The problem occurs when users try to add a record for which a DagtaakId and ArtikelId combination already exists. In that case after the post, the system seems to have updated the existing record in the DB. But on my display (in the grid) I now still see 2 lines for that ArtikelId ... one with the correctly updated Aantal (Qty), but the old line with the previous quantity is still there as well.

Is there any way I could work around that problem or solve it ? I could probably refresh the whole dataset after every post, but I'm wondering if there isn't an easier / more efficient way to achieve the same thing.

Best regards,


Stefaan

[AndreyZ]

Hello,

When you use such server logic (updating a record instead of inserting it), your application knows nothing about it. That's why, to obtain changes made on the server, you need to refresh your datasets on the client.