|
List Info
Thread: RE: want to get the next ID before inserting
|
|
| RE: want to get the next ID before
inserting |
 
United States |
2007-08-22 13:58:59 |
|
> Date: Wed, 22 Aug 2007 13:37:16 -0500
> From: david davidpenton.com
> To: aspnet-databasesaspadvice.com
> Subject: [aspnet-databases] RE: want to get the next ID before inserting
>
> UGG...
>;
> Unfortunately, SQL Server is the only out of the "big 3" that *DOES NOT* have a sequence object.
>
> Silly double-negatives...
>
> - david
>
> David L. Penton wrote:
>; > Unfortunately, SQL Server is the only out of the "big 3" that doesn't
> > not have a sequence object.
> >
> > Ian Lipsky wrote:
>; >> Sequence DB object?
> >>
> >> Actually, I was planning on using a table with an auto-id field, and
> >> then insert a row to get the next ID and then delete that row to keep
> >> the table small.
>; >>
> >> Ian
> >>
> >> On 8/21/07, PhilJackson <pjackson1hot.rr.com> wrote:
>; >>> Yep - having a column defined as an identity (auto increment) or using a
> >>> Sequence DB object is certainly better. I've seen Keith's method used
> >>> even in old systems that had nothing but IBM VSAM files, with one file
> >>> containing that "next number" ,etc.
> >>>
> >>>
> >>>
> >>> On Tue, 2007-08-21 at 18:08 -0600, Keith Barrows [StarPilot] wrote:
>; >>>> One of the ways we did in "the old days" before auto generated IDs
> >>>> were the
> >>>> rage was to have a table that was solely responsible for dishing out
> >>>> new
> >>>> IDs. Single column for the ID, sproc to get the next ID and
> >>>> increment it.
> >>>> Now, you have a unique ID that no one else can ever have. The only
> >>>> caveat
>; >>>> is if it is thrown away: You end up with gaps in your sequences.
> >>>> C'est la
> >>>> vie.
> >>>>
> >>>> ________________________________
> >>>>
> >>>> Keith Barrows
> >>>>
> >>>> -----Original Message-----
> >>>> From: merkimerk.com [mailto:merkimerk.com] On Behalf Of Ian Lipsky
>; >>>>
> >>>> asp.net 2.0 and sql 2000.
> >>>>
> >>>> I have a page that does an insert into one table and an update on a 2nd
> >>>> table.
>; >>>>
> >>>> I need to get the ID of the insert before doing the insert. i.e.
> >>>> they want
> >>>> the ID # displayed on the form before they actually submit the form.
> >>>>
> >>>> I was thinking of just grabbing the highest number ID in the table,
> >>>> adding 1
> >>>> to that and displaying that. But that runs into problems if two
> >>>> people are
> >>>> submitting at the same time.
> >>>>
> >>>> So i can think of two other ways of doing this:
> >>>>
> >>>> 1. insert an empty record, get the ID and display that and then do
> >>>> an update
>; >>>> instead of insert
>; >>>> 2. create a 2nd table with just an ID field, insert into that, get
> >>>> the ID
> >>>> and use that for my record ID.
> >>>>
> >>>> For the first one...it just seems messy and i dont like having empty
> >>>> records
> >>>> ( i know I can schedule a proc to run to clean them up)
> >>>> I dont really like the 2nd option either... still seems messy to me.
> >>>>
> >>>> but given the two, the first one makes more sense to me. Seems
> >>>> pointless to
> >>>> have a 2nd table just to increment a counter.
> >>>>
> >>>> Is there a better way?
> >>>>
> >>>> ian
>
> Need SQL Advice? http://sqladvice.com
> Need RegEx Advice? http://regexadvice.com
> Need XML Advice? http://xmladvice.com
Find a local pizza place, movie theater, and more….then map the best route! Find it!
Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com
|
[1]
|
|
|
about | contact Other archives ( Real Estate discussion Medical topics )
|