• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

VB6 problem inserting recorrds

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.
0
AnnaJames77
Asked:
AnnaJames77
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
Om PrakashCommented:
you can add identity column which will increment the column automatically.
0
 
AnnaJames77Author Commented:
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
0
 
Om PrakashCommented:
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.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Neil RussellTechnical Development LeadCommented:
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.
0
 
AnnaJames77Author Commented:
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


0
 
Neil RussellTechnical Development LeadCommented:
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
0
 
Deepu SreedharBI Software EngineerCommented:
Why don't you make a combined Primary key with those two columns?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now