Re: Identity error?? Please help

From: Kevin Frevert (Work_at_drinkingcoffee.com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 07:30:37 -0500

Andre,

  The "problem" is that all that stuff is happening on the server, not the
client (Delphi). For master-detail(s) situations, I recommend using a seed
table and a stored procedure to increment/return the next available number.

I'll post a demo to borland.public.attachments.

krf

"Andre Greyling" <agreyling@optusnet.com.au> wrote in message
news:4085148d$1@newsgroups.borland.com...
> Thanks for the help - yes the problem is @@IDENTITY related. It is still
> strange how Delphi behaves though. The code is simply:
> (1) qry1.append;
> (2) qry1.fieldbyname('ID').asinteger
>
> qry1 does a simple SELECT on table Master. Master has a trigger (on
INSERT)
> which creates detail tables
> each table has an identity column.
> line (2) then returns the identity value of the last detail table - odd !
>
> However, what is the best way to create a master record and retrieve it's
> identity value ? Should the code be in a stored procedure or is there a
> simpler way ?
> Any ideas ?
>
> Bye
>
>
> "Brian Hollister" <bhollisterATfuturaintlDOTcom> wrote in message
> news:40844810$1@newsgroups.borland.com...
> > Check out @@SCOPE_IDENTITY in SQL Servers BOL maybe that will help
> >
> > "Morpheus" <morpheusNOPSAM@redpoint.co.za> wrote in message
> > news:4083b79b@newsgroups.borland.com...
> > > Hi Andre,
> > >
> > > Sounds like you're an expat now living in Aus ;-).
> > >
> > > I do know that in SQL Server, if you get the value of an IDENTITY
column
> > in
> > > a trigger, depending upon which global variable you look at, you will
> > always
> > > get the last IDENTITY value inserted. This would be consistent with
the
> > > value that you are gettting back. I am not too sure how Delphi handles
> > this,
> > > but how are you getting the last inserted identoty value? A bit of
> detail
> > > might help you solve the problem.
> > > Regards,
> > > Norman
> > >
> > > "Andre Greyling" <agreyling@optusnet.com.au> wrote in message
> > > news:40837755@newsgroups.borland.com...
> > > > Hi all
> > > >
> > > > I have an appplication that does an insert into a table that has an
> > > identity
> > > > column. In the afterpost event of the adoquery i want to get the
newly
> > > > inserted indentity value. Normally i don't have a problem. However,
> when
> > I
> > > > have a detail dataset hanging off it (relationship is defined using
> the
> > > > Datasource property of the detail query) AND I have trigger on the
> > master
> > > > table that inserts into the detail table(which also has an identity
> > > column),
> > > > the value I get in my afterpost event is the value of the details
> > > identity
> > > > column!!!!!!!!!! What the...??? As soon as I remove the insert in
the
> > > > trigger, I get the correct value back (i.e. the identity value of
the
> > > master
> > > > record).
> > > >
> > > > How do I overcome this problem? Any suggestions on what is the best
> way
> > to
> > > > retrieve an identity value given the above situation (I may have
> > multiple
> > > > detail tables of master table) or generally.
> > > >
> > > > Any help would be greatly appreciated
> > > >
> > > > TIA
> > > > Andre
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Cannot update identity column PointOfSaleID.
    ... The sp_MSupd_PointOfSales stored procedure checks to see if the bit flag ... set and if so it executes an update statement which includes the identity ... for the identity column, it executes fine and chooses the else path. ...
    (microsoft.public.sqlserver.replication)
  • Re: Cannot update identity column PointOfSaleID.
    ... The sp_MSupd_PointOfSales stored procedure checks to see if the bit flag ... set and if so it executes an update statement which includes the identity ... for the identity column, it executes fine and chooses the else path. ...
    (microsoft.public.sqlserver.replication)
  • Delphi 2005, ADO, XML
    ... Could someone plz post a working example of retrieving XML data ... Delphi 2005, Microsoft SQL Server 2000. ... I have a stored procedure called myStoredProc which returns ... Comprehensive help on ADO w/ Delphi is almost non-existent! ...
    (borland.public.delphi.database.ado)
  • Re: storedproc.edit: "select permission denied" when assigning to a field
    ... Delphi isn't doing anything inside the stored procedure, ... your loop that you do "updatebatch" to actually apply the changes to the ... That would probably go much faster since the database engine will update the ...
    (borland.public.delphi.database.ado)
  • Re: Sychronize Key between Database and Dataset on Insert
    ... Assuming your 'problem' is caused by having an IDENTITY column as your primary key, all you need do is add a SELECT statement after the INSERT inside the stored procedure, after capturing the new IDENTITY in a variable ... In fact is is only an SQL commandstring that is stored on the server and not in your program and which can therefore have because of that a slight performance advantage. ...
    (microsoft.public.dotnet.framework.adonet)