VB6 problem inserting recorrds

Posted on 2011-05-05
Last Modified: 2012-05-11
Hi, I have a table which holds serialno. while inserting it grabs the max of the serialno and insert max of serialno +1.
 Now my problem is ,if two users try to insert at the same time i donot want the duplicate serialno been added. how can i handle such situation.
Question by:AnnaJames77
    LVL 22

    Expert Comment

    by:Om Prakash
    you can add identity column which will increment the column automatically.

    Author Comment

    i know about it. but its for each say r1 i have serialno 1 to n and again r2 will have the same 1to n and r3 will have the same 1to n
    LVL 22

    Assisted Solution

    by:Om Prakash
    If all the users are pointing to same database then the identity column will be incermented irrespective of users.

    Other option is to create a separate table which will have the incremental values.
    All the users will get max+1 from this table and update the same.
    whatever value u get insert it in the main table.

    LVL 37

    Accepted Solution

    Have a seperate table that holds you Next serial number, just one record. Then create a stored proc the a user calls that..
    Locks the table, gets the serial number, increments it by 1, writes it back into the table, unlocks the table and returns that new incremented number.  Avoids all possible problems.

    Author Comment

    actually what i meant is ; say i have field1 and field2 field1 holds r1,r2,r3 and
    for each r1- 1,2,3,4, to n
    then for r2 -1,2,3,4,5 to n
    struc will be;

    week  serialno
    r1         1
    r1         2
    r1         3
    r2        1
    r2        2
    r2        3
    r2        4
    r3        1
    r3        2

    what happens now is if user select r1, need to get the max of serialno of r1 ie, 3 and then while inserting will be 4. now my problm is if 2 users both get the max of r1 ie 3, while inserting both will insert as 4. I donot need it to be duplicated for same r1.

    Hope you got the scenario

    LVL 37

    Expert Comment

    So use the same principle I stated above but with 2 fields. One to hold the r value and one the index be used last
    LVL 3

    Assisted Solution

    Why don't you make a combined Primary key with those two columns?

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now