Getting the SQL AutoNumber with requery() 2

[PaulBarbeau]

I have a SQL view that I am adding records to in VFP6 however the problem that I have is I want to get the sql autonumber for the record without =requery() as the database has 200k records. When I use =refresh() the record disappears I guess because it does not know the primary key. Anyone have any idea how to do this.

Paul

 

[TomasDill]

@@IDENTITY variable on SQL Server gives you last number generated as auto number for last INSERT command on SQL Server. Simple query "SELECT @@IDENTITY" sent to SQL Server using SQL Pass-through will give you cursor with one field and one row wich contain that number. Use this query immediately after updating VFP view/cursor to get this number. Than:
- replace auto number field in the view
- send update again
Note that if auto number field is not updatabale, nothing will happen after last update except VFP will mark record that it does not contain any changes. However, you will have complete record with required ID, and refresh will now work too.



Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[PaulBarbeau]

Ok i must be doing something wronge. In my Database File i have file called TOMORROW that is my table in the SQL database. No mater what i do i can not get the value on the unique. Any ideas?


[tt]
SET MULTILOCKS ON && Must be on for table buffering
open database sqllink
use tomorrow in 0 && TABLE in the SQL Database
Holder = SQLConnect("SC2000","XXXXXXXXX","XXXXXXX&quot
select opport && File with into to be inserted
select tomorrow
=CURSORSETPROP('Buffering', 5, 'tomorrow' )
append blank
replace title with opport.title
=TABLEUPDATE(.T.)
=SQLEXEC(Holder,"select @@identity","newoppno&quot
=SQLDisConnect(Holder)
SET MULTILOCKS OFF
[/tt]

 

[TomasDill]

Hi!

In the tomorrow view you probably have an unique key field. this field should be identity field on the SQL server (auto number field). Only in that case @@IDENTITY will return you correct value.

To set field as identity key, in the SQL Server Enterprise Editor in the table designer uncheck 'Allows NULLs' checkbox and check 'Identity' checkbox. Note that field values should be unique. It is recommended also to make that field as key field (right click on the field's row).




Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs