Board index » delphi » inserting records to mssql7.0/delphi 4.0 cs sp3/ and can't retrieve key

inserting records to mssql7.0/delphi 4.0 cs sp3/ and can't retrieve key

I have delph 4.0 client/server (sp3), bde (5.01), MSSql 7.0 driver, and
MSSql 7.0

I have a table that has an autoincrement (aka identity) primary key of let's
say TransId, and TransName

I can through Access 7.0 add a record and automatically retrieve the new key
that was generated, but through bde, i cannot !

why is that?

can anyone tell me what can i do to retrieve the key without actually moving
to a new record or refreshing?

Refreshing doesn't work and moving to a new record doesn't work, cause if
someone else puts data on the database table, you move the the lastest
record, not the record you last entered!

Anyone?

Thanx!
Dan.

 

Re:inserting records to mssql7.0/delphi 4.0 cs sp3/ and can't retrieve key


I also had a problem with identity fields so instead I use Int fields and my
AutoInc table and a few procedures for managing it.
In that way I can get new ID for some Item whenever I need it in the program
or in some stored procedure and I know it's value.
Stored procedure for getting the new value automaticaly increment the old
value for required entry in AutoInc table.

tomi.

Quote
Daniel Serrano wrote in message <7crvhb$9r...@forums.borland.com>...
>I have delph 4.0 client/server (sp3), bde (5.01), MSSql 7.0 driver, and
>MSSql 7.0

>I have a table that has an autoincrement (aka identity) primary key of
let's
>say TransId, and TransName

>I can through Access 7.0 add a record and automatically retrieve the new
key
>that was generated, but through bde, i cannot !

>why is that?

>can anyone tell me what can i do to retrieve the key without actually
moving
>to a new record or refreshing?

>Refreshing doesn't work and moving to a new record doesn't work, cause if
>someone else puts data on the database table, you move the the lastest
>record, not the record you last entered!

>Anyone?

>Thanx!
>Dan.

Re:inserting records to mssql7.0/delphi 4.0 cs sp3/ and can't retrieve key


BTW Dan, what is your method for doing this with Access?

Thanks,
Mitch Baker

Quote
Mitch Baker wrote in message <7eetr2$rp...@forums.borland.com>...
>You can do a 'Select @@Identity' to get the last Identity value inserted.

>See the SQL Server 7.0 docs.

>Regards,
>Mitch Baker

>>Daniel Serrano wrote in message <7crvhb$9r...@forums.borland.com>...
>>>I have delph 4.0 client/server (sp3), bde (5.01), MSSql 7.0 driver, and
>>>MSSql 7.0

>>>I have a table that has an autoincrement (aka identity) primary key of
>>let's
>>>say TransId, and TransName

>>>I can through Access 7.0 add a record and automatically retrieve the new
>>key
>>>that was generated, but through bde, i cannot !

>>>why is that?

>>>can anyone tell me what can i do to retrieve the key without actually
>>moving
>>>to a new record or refreshing?

>>>Refreshing doesn't work and moving to a new record doesn't work, cause if
>>>someone else puts data on the database table, you move the the lastest
>>>record, not the record you last entered!

>>>Anyone?

>>>Thanx!
>>>Dan.

Re:inserting records to mssql7.0/delphi 4.0 cs sp3/ and can't retrieve key


You can do a 'Select @@Identity' to get the last Identity value inserted.

See the SQL Server 7.0 docs.

Regards,
Mitch Baker

Quote
>Daniel Serrano wrote in message <7crvhb$9r...@forums.borland.com>...
>>I have delph 4.0 client/server (sp3), bde (5.01), MSSql 7.0 driver, and
>>MSSql 7.0

>>I have a table that has an autoincrement (aka identity) primary key of
>let's
>>say TransId, and TransName

>>I can through Access 7.0 add a record and automatically retrieve the new
>key
>>that was generated, but through bde, i cannot !

>>why is that?

>>can anyone tell me what can i do to retrieve the key without actually
>moving
>>to a new record or refreshing?

>>Refreshing doesn't work and moving to a new record doesn't work, cause if
>>someone else puts data on the database table, you move the the lastest
>>record, not the record you last entered!

>>Anyone?

>>Thanx!
>>Dan.

Re:inserting records to mssql7.0/delphi 4.0 cs sp3/ and can't retrieve key


<<BTW Dan, what is your method for doing this with Access?

Quote

Select Max(FieldName)

I know because we just create a enable our ASTA servers to be able to fetch
AutoIncrement and other values on client inserts.

When you do an insert into a remote TAstaClientDataSet you can now (ASTA
1.91) optionally tag a field as an AutoIncrement and also tag fields to be
refetched on inserts.

You just then use append/insert/edit to post your data to the
TAstaClientdataSet and when you call applyUpdates the the server will fire
an sql query to retrieve the next autoinc value (depending your your backend
for sql server, sqla it does the @@identity, for access, paradox it does
max, etc) and then any fields you defined to be Refetched On inserts will be
streamed back to the client and matched up to the row.

This allows you to get server side inserted values without closing and
opening the remote query.

For stuff like this and more visit www.astatech.com

Our ASTAOdbcExpress server works great against ms sql server and access and
can run in one of 3 different threading models and of course requires no
BDE. ASTA clients require Nothing of course, except the knowledge of the
servers ip address and port.

--
Steve Garland sgarl...@astatech.com
Does your Database run on the Internet? It can!
ASTA: Internet-Ready Client/Server Products & Services
ASTA Technology Group http://www.astatech.com

Other Threads