Board index » delphi » MS SQL Server Insert a record with a IDENTITY field

MS SQL Server Insert a record with a IDENTITY field

Hi

Can someone tell how can I write a stored procedure that insert a record
with a IDENTITY field
Delphi gives  me an error IDENTITY_INSERT is set to OFF.
How can I solve this problem

Thanks
Ruben

 

Re:MS SQL Server Insert a record with a IDENTITY field


Quote
Ruben Przewoznik wrote:

> Hi

> Can someone tell how can I write a stored procedure that insert a record
> with a IDENTITY field
> Delphi gives  me an error IDENTITY_INSERT is set to OFF.
> How can I solve this problem

> Thanks
> Ruben

I use cached updates on my TQuery and remove the identity column
from the update's generated SQL.

Re:MS SQL Server Insert a record with a IDENTITY field


Quote
>But there is one problem. How can you obtain identity field value
>after insert? You need refresh dataset, but when you refresh
>dataset, you loss record position in dataset.

Select @@IDENTITY

Dean
Information Dynamics
Leeds, England

Re:MS SQL Server Insert a record with a IDENTITY field


If you use a stored proc you don't have any problem at all.
When inserting you just don't specify th ecolumn with the identity and
after the insert you use @@identity to get the identify used for your
insert, something like...

insert .....
select @newidentity = @@identity

@identity beging an output variable or you can just do:

select @@identity

and return, your stored proc component will receive the identity...

Oh, by the way, this is for MS SQL Server... For other databases I don't
know
--
Remove '.new' at the end of my email address to contact me.

Sith Johnny <ja...@gratex.sk> wrote in article
<35B70DDE.AA4DD...@gratex.sk>...

Quote
> But there is one problem. How can you obtain identity field value
> after insert? You need refresh dataset, but when you refresh
> dataset, you loss record position in dataset.

> I use for inserting stored procedures with identity field as output
parameter
> and other fields as input parameters. Than I can obtain identity field
value
> as output parameter from stored procedure, but I need refresh my dataset,
> because I don't know, how refresh only one field in my dataset.

> Johnny

> Michael Mitchell wrote:

> > Ruben Przewoznik wrote:

> > > Hi

> > > Can someone tell how can I write a stored procedure that insert a
record
> > > with a IDENTITY field
> > > Delphi gives  me an error IDENTITY_INSERT is set to OFF.
> > > How can I solve this problem

> > > Thanks
> > > Ruben

> > I use cached updates on my TQuery and remove the identity column
> > from the update's generated SQL.

Re:MS SQL Server Insert a record with a IDENTITY field


I know how obtain identity otuput parameter but problem is this:
When insert is done, in inserted dataset I have this:
dataset.FieldByName('identity').AsInteger=0

My question: How can I refresh this field only,
no all dataset. I have obtained identity value in
some variable, but how can I do like this (my refresh, field only):
dataset.FieldByName('identity').AsInteger:=identity;.

I'd like use construction f.e.->   if
dataset.FieldByName('identity').AsInteger=1 then .....
without refreshing all dataset after insert.

Johnny

Quote
Philippe Mermoud wrote:
> If you use a stored proc you don't have any problem at all.
> When inserting you just don't specify th ecolumn with the identity and
> after the insert you use @@identity to get the identify used for your
> insert, something like...

> insert .....
> select @newidentity = @@identity

> @identity beging an output variable or you can just do:

> select @@identity

> and return, your stored proc component will receive the identity...

> Oh, by the way, this is for MS SQL Server... For other databases I don't
> know
> --
> Remove '.new' at the end of my email address to contact me.

> Sith Johnny <ja...@gratex.sk> wrote in article
> <35B70DDE.AA4DD...@gratex.sk>...
> > But there is one problem. How can you obtain identity field value
> > after insert? You need refresh dataset, but when you refresh
> > dataset, you loss record position in dataset.

> > I use for inserting stored procedures with identity field as output
> parameter
> > and other fields as input parameters. Than I can obtain identity field
> value
> > as output parameter from stored procedure, but I need refresh my dataset,
> > because I don't know, how refresh only one field in my dataset.

> > Johnny

> > Michael Mitchell wrote:

> > > Ruben Przewoznik wrote:

> > > > Hi

> > > > Can someone tell how can I write a stored procedure that insert a
> record
> > > > with a IDENTITY field
> > > > Delphi gives  me an error IDENTITY_INSERT is set to OFF.
> > > > How can I solve this problem

> > > > Thanks
> > > > Ruben

> > > I use cached updates on my TQuery and remove the identity column
> > > from the update's generated SQL.

Re:MS SQL Server Insert a record with a IDENTITY field


I'm not sure but if youuse an output variable, on return of your proc you
can check its result (to know if everything went right) then retrieve the
output value and set the identity field with it.
--
Remove '.new' at the end of my email address to contact me.

Sith Johnny <ja...@gratex.sk> wrote in article
<35BC4326.E72C3...@gratex.sk>...

Quote
> I know how obtain identity otuput parameter but problem is this:
> When insert is done, in inserted dataset I have this:
> dataset.FieldByName('identity').AsInteger=0

> My question: How can I refresh this field only,
> no all dataset. I have obtained identity value in
> some variable, but how can I do like this (my refresh, field only):
> dataset.FieldByName('identity').AsInteger:=identity;.

> I'd like use construction f.e.->   if
> dataset.FieldByName('identity').AsInteger=1 then .....
> without refreshing all dataset after insert.

> Johnny

> Philippe Mermoud wrote:

> > If you use a stored proc you don't have any problem at all.
> > When inserting you just don't specify th ecolumn with the identity and
> > after the insert you use @@identity to get the identify used for your
> > insert, something like...

> > insert .....
> > select @newidentity = @@identity

> > @identity beging an output variable or you can just do:

> > select @@identity

> > and return, your stored proc component will receive the identity...

> > Oh, by the way, this is for MS SQL Server... For other databases I
don't
> > know
> > --
> > Remove '.new' at the end of my email address to contact me.

> > Sith Johnny <ja...@gratex.sk> wrote in article
> > <35B70DDE.AA4DD...@gratex.sk>...
> > > But there is one problem. How can you obtain identity field value
> > > after insert? You need refresh dataset, but when you refresh
> > > dataset, you loss record position in dataset.

> > > I use for inserting stored procedures with identity field as output
> > parameter
> > > and other fields as input parameters. Than I can obtain identity
field
> > value
> > > as output parameter from stored procedure, but I need refresh my
dataset,
> > > because I don't know, how refresh only one field in my dataset.

> > > Johnny

> > > Michael Mitchell wrote:

> > > > Ruben Przewoznik wrote:

> > > > > Hi

> > > > > Can someone tell how can I write a stored procedure that insert a
> > record
> > > > > with a IDENTITY field
> > > > > Delphi gives  me an error IDENTITY_INSERT is set to OFF.
> > > > > How can I solve this problem

> > > > > Thanks
> > > > > Ruben

> > > > I use cached updates on my TQuery and remove the identity column
> > > > from the update's generated SQL.

Re:MS SQL Server Insert a record with a IDENTITY field


I don't know, how set the identity field.

I have retrived output value.
When I set the identity field, the exception occured:
'DataSet is not in Edit mode'

Johnny

Quote
Philippe Mermoud wrote:
> I'm not sure but if youuse an output variable, on return of your proc you
> can check its result (to know if everything went right) then retrieve the
> output value and set the identity field with it.
> --
> Remove '.new' at the end of my email address to contact me.

> Sith Johnny <ja...@gratex.sk> wrote in article
> <35BC4326.E72C3...@gratex.sk>...
> > I know how obtain identity otuput parameter but problem is this:
> > When insert is done, in inserted dataset I have this:
> > dataset.FieldByName('identity').AsInteger=0

> > My question: How can I refresh this field only,
> > no all dataset. I have obtained identity value in
> > some variable, but how can I do like this (my refresh, field only):
> > dataset.FieldByName('identity').AsInteger:=identity;.

> > I'd like use construction f.e.->   if
> > dataset.FieldByName('identity').AsInteger=1 then .....
> > without refreshing all dataset after insert.

> > Johnny

> > Philippe Mermoud wrote:

> > > If you use a stored proc you don't have any problem at all.
> > > When inserting you just don't specify th ecolumn with the identity and
> > > after the insert you use @@identity to get the identify used for your
> > > insert, something like...

> > > insert .....
> > > select @newidentity = @@identity

> > > @identity beging an output variable or you can just do:

> > > select @@identity

> > > and return, your stored proc component will receive the identity...

> > > Oh, by the way, this is for MS SQL Server... For other databases I
> don't
> > > know
> > > --
> > > Remove '.new' at the end of my email address to contact me.

> > > Sith Johnny <ja...@gratex.sk> wrote in article
> > > <35B70DDE.AA4DD...@gratex.sk>...
> > > > But there is one problem. How can you obtain identity field value
> > > > after insert? You need refresh dataset, but when you refresh
> > > > dataset, you loss record position in dataset.

> > > > I use for inserting stored procedures with identity field as output
> > > parameter
> > > > and other fields as input parameters. Than I can obtain identity
> field
> > > value
> > > > as output parameter from stored procedure, but I need refresh my
> dataset,
> > > > because I don't know, how refresh only one field in my dataset.

> > > > Johnny

> > > > Michael Mitchell wrote:

> > > > > Ruben Przewoznik wrote:

> > > > > > Hi

> > > > > > Can someone tell how can I write a stored procedure that insert a
> > > record
> > > > > > with a IDENTITY field
> > > > > > Delphi gives  me an error IDENTITY_INSERT is set to OFF.
> > > > > > How can I solve this problem

> > > > > > Thanks
> > > > > > Ruben

> > > > > I use cached updates on my TQuery and remove the identity column
> > > > > from the update's generated SQL.

Other Threads