>>>>>You need to query the server for the global variable
>>>>>@@identity
>>>>>Unfortunately this will mean two SQLs for every insert. I always specify what the id should be from the front end myself.
>>>>>
>>>>>Ian
>>>>
>>That solution is good, but be carefull with the connection handle, because @@identity return null if connection handle is diferent than view.
>>I mean
>
>>use myrview nodataonload
>>insert into myrview values (xxxx) &&except ID identity
>>nhandle=Cursorgetprop('myrview','ConnectHandle')
>>=sqlexec(nhandle,'select @@identity nidentity','tempcursor')
>>m.var=tempcursor.nidentity &&m.var or variable defined in the view
>>=requery('myrview')
>
>>this program probably works so so
>>
>>Bye
>
>Hi Claudio,
>
>Thanks for the advice. At the moment I don't know whether I am going to use Ian's technique for storing references in a table on the server and using stored procedures to lock the table and get the next one etc, or go for the @@IDENTITY solution. I guess I've got some more reading/experimenting before I can make a decision. Thanks for the code though, as I wasn't exactly sure how to do that anywany, you saved me the trouble of finding out!
>
>Is this the technique that you use in your systems Claudio, or do you use a table with the values for each key stored in it? (Hope you don't mind me asking)

My technique to develop apps c/s is without remoteviews, i write 100% Stored Procedure system, for each table I write a lot of SP to emulate actions like insert, update, delete, search, and navigate(first, next, previous and last record)
Each table with Identity column and timestamp column (to prevent concurrent update to one record)
For example:
/****** Object: Table dbo.medios Script Date: 04/03/1998 16:20:30 ******/
CREATE TABLE dbo.medios (
med_ncodigo int IDENTITY (1, 1) NOT NULL ,
med_cnombre char (20) NOT NULL ,
med_fbaja datetime NULL ,
med_timestamp timestamp NOT NULL ,
us_ncodigo int NOT NULL ,
us_fultmod datetime NOT NULL
)
GO

CREATE UNIQUE INDEX XAK1medios ON dbo.medios(med_cnombre) WITH FILLFACTOR = 90
GO

/****** Object: Stored Procedure dbo.sp_medios_agregar Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_agregar
@cnombre char(20),
@fbaja datetime

AS
insert into medios (med_cnombre,med_fbaja,us_ncodigo,us_fultmod)
values (@cnombre,@fbaja,suser_id(),getdate())
if @@rowcount=1
execute sp_medios_refrescar @@identity
else
raiserror 32000 'No pude insertar debido a un error'

GO

/****** Object: Stored Procedure dbo.sp_medios_anterior Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_anterior
@nclave integer
AS
if null<>(select max(medios.med_ncodigo)
from medios
where medios.med_ncodigo<@nclave)
select medios.*,suser_name(medios.us_ncodigo) us_nombre
from medios
where medios.med_ncodigo=(select max(medios.med_ncodigo)
from medios
where medios.med_ncodigo<@nclave)
else
execute sp_medios_primero

GO

/****** Object: Stored Procedure dbo.sp_medios_ayudar Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_ayudar AS
select med_ncodigo,med_cnombre
from medios
where med_fbaja=null

GO

/****** Object: Stored Procedure dbo.sp_medios_borrar Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_borrar
@nclave integer,
@timestamp char(8)
AS
declare @oktime timestamp
select @oktime=convert(varbinary,@timestamp)

delete medios where @nclave=medios.med_ncodigo and
@oktime=medios.med_timestamp
if @@rowcount=1
execute sp_medios_proximo @nclave
else
raiserror 32000 'No pude borrar debido a un error'

GO

/****** Object: Stored Procedure dbo.sp_medios_buscar Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_buscar
@med_ncodigo int,
@med_cnombre char(20),
@med_fbaja datetime,
@us_ncodigo int,
@us_fultmod datetime
AS
if @med_ncodigo<>null
select * ,suser_name(us_ncodigo) us_nombre
from medios
where @med_ncodigo=med_ncodigo
if @med_cnombre<>null
select * ,suser_name(us_ncodigo) us_nombre
from medios
where med_cnombre LIKE @med_cnombre
if @med_fbaja<>null
select * ,suser_name(us_ncodigo) us_nombre
from medios
where @med_fbaja=med_fbaja
if @us_ncodigo<>null
select * ,suser_name(us_ncodigo) us_nombre
from medios
where @us_ncodigo=us_ncodigo
if @us_fultmod<>null
select * ,suser_name(us_ncodigo) us_nombre
from medios
where @us_fultmod=us_fultmod

GO

/****** Object: Stored Procedure dbo.sp_medios_modificar Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_modificar
@nclave integer,
@cnombre char(20),
@fbaja datetime,
@timestamp char(8)
AS
declare @oktime timestamp
select @oktime=convert(varbinary,@timestamp)
update medios set
medios.med_cnombre=@cnombre,
medios.med_fbaja=@fbaja,
medios.us_ncodigo=suser_id(),
medios.us_fultmod=getdate()
where medios.med_ncodigo=@nclave and
med_timestamp=@oktime

if @@rowcount=0
raiserror 32000 'No pude modificar'
else
execute sp_medios_refrescar @nclave

GO

/****** Object: Stored Procedure dbo.sp_medios_primero Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_primero
AS
select *,suser_name(medios.us_ncodigo) us_nombre
from medios where medios.med_ncodigo in
(select min(medios.med_ncodigo) from medios)

GO

/****** Object: Stored Procedure dbo.sp_medios_proximo Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_proximo
@nclave integer
AS
if null<>(select min(medios.med_ncodigo) from medios where medios.med_ncodigo>@nclave)
select *,suser_name(medios.us_ncodigo) us_nombre from medios where medios.med_ncodigo in
(select min(medios.med_ncodigo) from medios where medios.med_ncodigo>@nclave)
else
execute sp_medios_ultimo

GO

/****** Object: Stored Procedure dbo.sp_medios_refrescar Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_refrescar
@nclave integer
AS
select *,suser_name(medios.us_ncodigo) us_nombre
from medios
where
medios.med_ncodigo=@nclave

GO

/****** Object: Stored Procedure dbo.sp_medios_ultimo Script Date: 04/03/1998 16:20:30 ******/
CREATE PROCEDURE sp_medios_ultimo AS
select *,suser_name(medios.us_ncodigo) us_nombre from medios where medios.med_ncodigo in
(select max(medios.med_ncodigo) from medios)

GO

-------------------------------------------------------------------------------------------------
read
sp_medios_primero = go first
sp_medios_ultimo = go last
sp_medios_refrescar= give that record
sp_medios_anterior =give that record less than....
sp_medios_proximo = give that record greater than...
sp_medios_modificar = update record and return the modified record
sp_medios_agregar = insert record
sp_medios_borrar= delete record
sp_medios_ayudar= help to references combos in forms
sp_medios_buscar= search records in several conditions

Pay attention in the SP to insert a record, when a record is inserted then return the inserted record with the identity column filled, but all actions occurs in a Compiled SP it's too fast than a remote view or a lot of sqlexec calls.
That technique is called "black box", because users can't permission to select a table, they have permission to execute a SP, and each SP write information automaticaly from who is the user, and datetime of update.

I hope that example help you

Bye,
Excuse me English, learn Slowly