Board index » delphi » MS SQL Server TTable cursor problem

MS SQL Server TTable cursor problem

Quote
Henry Willering wrote:

> I am having the following problem:

> I have a TTable component linked (via ODBC) to a SQL Server table. The
> table has an IDENTITY field.
> After posting a new record (by .AppendRecord method) and calling the
> Refresh method I expect the TTable's cursor to point to the record it
> just appended so I can get the value of the IDENTITY field generated
> by the server.

        [SNIP]

        The solution I use it to forget about TTables and only
        use TQuery.  This is a breif summary of how I handle
        data access.

        type TCustomer = class
                private
                F...etc
                public
                  property FirstName
                  property LastName
                  property CustomerId // IDENTITY
                  procedure New;
                  procedure Find;
                  procedure Post;
                  procedure Delete;
        end;

        procedure TCustomer.New;
        begin
          with Query do         // just 1 query needed
          begin                 // defined in Datamodule
            SQL.Clear;
            SQL.Add('InsCustomer');
            Open;
            FCustomerId := Fields[0].AsInteger;
            Close;      //more on this later
          end;
        end;

        procedure TCustomer.Find(CustomerId);
        begin
          with Query do
          begin
            SQL.Clear;
            SQL.Add('SELECT * FROM Customer WHERE CustomerId = :C');
            ParamByName('C').AsInteger := CustomerId;
            Open;
            FirstName := FieldByName('FirstName').AsString;
            LastName  := FieldByName('LastName').AsString;
            FCustomerId := CustomerId;
            Close;
          end;
        end;

        procedure TCustomer.Delete;
        begin
                // Pretty Basic
        end;

        /* SQL Stored Procedures */
        CREATE PROCEDURE InsCustomer
        AS
        BEGIN
            INSERT INTO Customer(FirstName, LastName)
                VALUES('', '')
            SELECT RETURN_VALUE = @@IDENTITY
        END

        The syntax is a little loose but this should give
        you a start.  It takes a little work to setup your
        classes but once you get started maintaining is
        a real dream.

        brant
        ...

 

Re:MS SQL Server TTable cursor problem


I am having the following problem:

I have a TTable component linked (via ODBC) to a SQL Server table. The
table has an IDENTITY field.
After posting a new record (by .AppendRecord method) and calling the
Refresh method
I expect the TTable's cursor to point to the record it just appended so I
can get the
value of the IDENTITY field generated by the server.
This procedure did work all right until I made various changes to my
environment.
But I am not sure this upgrade caused the problem.

I use the following environment:

Delphi 2.0
MS SQL Server 6.5
ODBC 3.0
BDE 3.5

I hope that there is someone who can explain or recognizes this problem.

Greetings Henry Willering

Other Threads