That wouldn't have been with Pervasive SQL by any chance? ;-) I've never had an autoincrementing field in MS SQL Server 7 become corrupted.
Stefan At 11:57 AM 5/22/2002 -0400, you wrote: >Funny you should say that because while with EveryWare we had a support >tracking system that was developed by Dave Shelley which used an >autoincrementing identity field which consistently gave us fits because it >would become corrupt and start issuing duplicate values. > >I think it shows that there is no system which is 100% foolproof. As >developers, we must come up with the best solution and then try to foresee >any problems that might occur and create ways to handle them without impact >to the end user. > >Steve Smith > >Skadt Information Solutions >Office: (519) 624-4388 >GTA: (416) 606-3885 >Fax: (519) 624-3353 >Cell: (416) 606-3885 >Email: [EMAIL PROTECTED] >Web: http://www.skadt.com > > >-----Original Message----- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED]]On Behalf Of Stefan Gonick >Sent: May 22, 2002 8:31 AM >To: Multiple recipients of list witango-talk >Subject: RE: Witango-Talk: Next WiTango_SQL conundrum > > >I feel compelled to point out a problem with this system. I >originally used the counter table functionality for all of my >projects but have completely dropped it now. The problem >that I had was that occasionally there would be a database >glitch that would leave a counter in a locked state. This >happened regularly with MS Access (I know), but it would >also happen with MS SQL Server, which I thought would be >safe. Each time it happened it would mess things up until >it was reset. I found that it is much safer to use an autoincrementing >identity field and then look up the value, either using transactions >or with userreference stored in the table. My 2 cents. > >Stefan > >At 07:53 AM 5/22/2002 -0400, you wrote: > >One of the beauties about the method that was written for the storefront is > >that if a particular table does not have a counter created for it, it will > >create one automatically. The counter table has three fields: counter_name > >(varchar 35 characters), counter_value (integer), and locked (integer). > > > >The calling tag is: > > > ><@ASSIGN local$NewID "<@CALLMETHOD scope$variable 'GetNextID(xxx)'>"> > > > >where 'scope' and 'variable' are what you've declared your object instance > >to be and 'xxx' is the name of the counter you wish to call. I use the >table > >name for the name of the counter to keep things easy to remember. To use >the > >value brought back by the call to the counter, use <@VAR NewID >SCOPE=Local>. > >For your situation you could have this: > > > ><@ASSIGN local$NewCourseID "<@CALLMETHOD scope$variable > >'GetNextID(course)'>"> > ><@ASSIGN local$NewCourse_CrosslinkID "<@CALLMETHOD scope$variable > >'GetNextID(course_crosslink)'>"> > > > >in a results action immediately before the insert for the course table. You > >would then have two variables: > > > ><@VAR NewCourseID SCOPE=Local> > > > >would contain the courseID and: > > > ><@VAR NewCourse_CrosslinkID SCOPE=Local> > > > >would contain the course_crosslinkID. > > > >Hope this helps, > > > >Steve Smith > > > >Skadt Information Solutions > >Office: (519) 624-4388 > >GTA: (416) 606-3885 > >Fax: (519) 624-3353 > >Cell: (416) 606-3885 > >Email: [EMAIL PROTECTED] > >Web: http://www.skadt.com > > > > > >-----Original Message----- > >From: [EMAIL PROTECTED] > >[mailto:[EMAIL PROTECTED]]On Behalf Of Dan Stein > >Sent: May 21, 2002 2:34 PM > >To: Multiple recipients of list witango-talk > >Subject: Re: Witango-Talk: Next WiTango_SQL conundrum > > > > > >The first suggestion sounds interesting can you expand? > >Is the counter table pre populated how do I do GetNextID method? > >I'll try and dig up the demo. > > > >Dan > > > >on 5/21/02 1:28 PM, Steve Smith at [EMAIL PROTECTED] wrote: > > > > > Two suggestions. First, why not go with a counter table and use the > > > GetNextID method that was included in the TCF for the storefront demo. >It > > > retrieves the next id which you can then use in your insert for the >course > > > table AND for the course_crosslink table. It will guarentee that you >have > > > the right value in both. > > > > > > The other suggestion if you are determined to not use a counter table is > >to > > > use the same values for the insert in your search rather than searching > >for > > > max course_ID. You run a far less chance of obtaining the wrong >course_ID > >if > > > use the exact same <@ARG xxx> values for the search criteria that were > >used > > > in the insert action. > > > > > > Hope this helps, > > > > > > Steve Smith > > > > > > Skadt Information Solutions > > > Office: (519) 624-4388 > > > GTA: (416) 606-3885 > > > Fax: (519) 624-3353 > > > Cell: (416) 606-3885 > > > Email: [EMAIL PROTECTED] > > > Web: http://www.skadt.com > > > > > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED]]On Behalf Of Dan Stein > > > Sent: May 21, 2002 11:58 AM > > > To: Multiple recipients of list witango-talk > > > Subject: Witango-Talk: Next WiTango_SQL conundrum > > > > > > > > > OK I have fixed everything else data is where it needs to be and >everyone > > > was a big help. > > > > > > Here is my next issue. > > > > > > When I create a course with create_course.taf after the insert there is >a > > > DBMS that gets Max course_ID and set's it to a local variable. > > > > > > That is because I need to know what the course_ID that was assigned by >SQL > > > to the inserted course. I then use this to insert into the > >course_crosslink > > > table the course_ID and some other values. > > > > > > It looks like sometimes it failed to set the correct course ID I guess > >there > > > was some fast enough data entry going on from more than one person so >the > > > Max did not get the right number. At least it is the only thing I can > >think > > > of that would cause the missing course_ID's in course_crosslink. Since >the > > > insert action comes right the DBMS I can see how it could fail without >an > > > error message to the user which I think I would have heard about. > > > > > > I know there is some way to lock the table or someway to make sure I > > > retrieve the correct value but I don't know how to write it in WiTango >or > >as > > > a DBMS. > > > > > > > > > > > > -- > > > Dan Stein > > > Digital Software Solutions > > > 799 Evergreen Circle > > > Telford PA 18969 > > > 215-799-0192 > > > 610-256-2843 > > > Fax 413-410-9682 > > > FMP,Tango, EDI,SQL 7 > > > [EMAIL PROTECTED] > > > www.dss-db.com > > > > > > > > > ________________________________________________________________________ > > > TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > > > with unsubscribe witango-talk in the message body > > > > > > ________________________________________________________________________ > > > TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > > > with unsubscribe witango-talk in the message body > > > > > > >-- > >Dan Stein > >Digital Software Solutions > >799 Evergreen Circle > >Telford PA 18969 > >215-799-0192 > >610-256-2843 > >Fax 413-410-9682 > >FMP,Tango, EDI,SQL 7 > >[EMAIL PROTECTED] > >www.dss-db.com > > > > > >________________________________________________________________________ > >TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > > with unsubscribe witango-talk in the message body > > > >________________________________________________________________________ > >TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > > with unsubscribe witango-talk in the message body > >======================================================== >Database WebWorks: Dynamic web sites through database integration >http://www.DatabaseWebWorks.com > >________________________________________________________________________ >TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > with unsubscribe witango-talk in the message body > >________________________________________________________________________ >TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > with unsubscribe witango-talk in the message body ======================================================== Database WebWorks: Dynamic web sites through database integration http://www.DatabaseWebWorks.com ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
