ey tnx for the reply i haven't check the list of function of MySQL and its
all there, you folks just me an idea

lewell

The content of this email when sent is as full as practical. Due to the
settling of the words the contents might not appear as full as it was but
the weight remains the same. The contents is being sent by weight and not by
volume.
----- Original Message -----
From: "Helen Warn" <hwarn@s...>
To: "professional vb" <pro_vb@p...>
Sent: Wednesday, November 20, 2002 6:04 AM
Subject: [pro_vb] Re: get the new ID


Good points all Jeff.

FWIW, I ALWAYS used stored procs for all of my database access. Also, when I
have the choice, I use uniqueidentifiers instead of Identity fields for
keys. However, if you inherit someone else's database this is not always
possible. In either case, your suggestion of using a stored procedure return
var for the new key always makes sense.

Cheers,

Helen

> -----Original Message-----
> From: Jeff Mason [mailto:je.mason@a...]
> Sent: Tuesday, November 19, 2002 1:52 PM
> To: professional vb
> Subject: [pro_vb] Re: get the new ID
>
>
> Note that the code to which Helen responded does a SELECT of
> @@IDENTITY and
> returns the result in a recordset.  This code has absolutely
> no connection
> with the code that does the actual insert (wherever that is),
> so the value
> returned here is only remotely related, if at all, to the
> INSERT for which
> the identity value is desired.  If any other user (much less
> a trigger) does
> an insert in between the original insert and when this code
> executes, the
> identity value will that of the latest insert, and not the
> one desired.
>
> This is a *really* nasty bug waiting to happen.  It will be virtually
> impossible to debug and may take a long time to show up, when
> the next poor
> slob who has to maintain your code gets the call at 2:00am... ;-)
>
> It would be much better to do the INSERT in a stored procedure and
> immediately return the resultant @@IDENTITY value as an
> output or return
> parameter.  In that case, it is probably better to use
> SCOPE_IDENTITY() for
> exactly the reasons Helen suggested.  For example:
>
> INSERT MyTable (Cola, Colb...) VALUES (@A,@B, ...)
> RETURN Scope_Identity()
>
> If you insist on using a recordset for the insert, you could open an
> updateable recordset with nothing in it, execute the addnew
> method to create
> a new (empty) row, populate the fields, call the update
> method, and then
> retrieve the identity column field value, as:
>
> sql="Select colID, col1, col2 FROM YourTable WHERE 1=0"
> rs.open sql,cn,adOpenKeyset,adLockPessimistic,adCmdText
> rs.addnew
> rs.Fields("col1").value="abc"
> rs.Fields("col2").value="def"
> rs.update
> TheIdentity=rs.Fields("colID").value
>
> But this is *way* slower and much less efficient than using a stored
> procedure.
> --
> Jeff Mason              Custom Apps, Inc.
> Jeff@c...
>
>
> -----Original Message-----
> From: Helen Warn [mailto:hwarn@s...]
> Sent: Tuesday, November 19, 2002 4:19 PM
> To: professional vb
> Subject: [pro_vb] Re: get the new ID
>
>
> If you are using SQL Server, you should really use
> SCOPE_IDENTITY() rather
> than @@IDENTITY.
>
> SCOPE_IDENTITY returns the last Identity added during the
> current scope e.g.
> batch, stored procedure or trigger during the current
> session. @@IDENTITY
> returns the identity added. If you are using a stored proc
> that does an
> action that fires a trigger which creates an identity var,
> you may not get
> back the value you are expecting.
>
> Cheers,
>
> Helen
>
> > -----Original Message-----
> > From: Ned [mailto:nedashraf@h...]
> > Sent: Tuesday, November 19, 2002 1:19 PM
> > To: professional vb
> > Subject: [pro_vb] Re: get the new ID
> >
> >
> > Use the following code to get the ID of the new record
> >
> >         strSQL = "SELECT 'FieldID' = @@IDENTITY"
> >         recset.Open strSQL, conn, adOpenForwardOnly, , adCmdText
> >         If Not recset.BOF And Not recset.EOF Then
> >             iFieldIDNew = recset("FieldID")
> >         End If
> >         recset.Close
> >
> > Enjoy!
> > Ned
> >
> > > i have a table which has 2 fields
> >
> > fieldID
> > field1
> >
> > i just inserted a record using
> >
> > Set rs = cn.execute("insert into table set field1 = 'abc'")
> >
> > the problem is how do i get the newly inserted row in my database
> >
> > intRecNo = rs.fields("fieldID")????? is this the suppose to
> > be solution?
> >
> > lewell
> >
> >
> > The content of this email when sent is as full as practical.
> > Due to the
> > settling of the words the contents might not appear as full
> > as it was but
> > the weight remains the same. The contents is being sent by
> > weight and not
> > by volume.
> >
> >
> >
>
>
>
>