JimmyJimm JimmyJimm - 1 year ago 74
Vb.net Question

Creating a unique sequential (non PK) DB Value

I have a table which contains products numbers. Each artikel belongs to some producent. When user creates his product the new number is genearted. Depends on producent articel starts with specific number and then its followed by new number increased by 1 in second section of entire number. Example is shown below:

Producent: X (12 as start number)

Producent: Y (4 as start number)

I have wizard on windows form with next/back buttons user picks producent and the number is generated - by this i mean there is sql query which is looking for the highest number of already available for specific producent for instance for Producent Y next will be
so new number will be
. At the moment when application is getting next number this number is not inserted into table but later after couple diffrent things user has to finalize. At the moment this number is saved into variable to be inserted after all other things at the end of process.

The problem is when other user is creating his article for same producent his wizard will give him same number as for previous user as the mentioned number is still not there in the table so he get same number as previous user.

How you would workaround this issue? What i have on mind is to have additional table in db so when new number is generated it would go to this table and every other user besides generated his number would check also the new table whether number is there and increment it again. Of course every user after work is finish the application would clear that table from this entry. The one issue i have on mind here is what if for instance for whatever reason any application would be closed by error or whatever then this table will not clear the entry.

How you would do on my side?

Answer Source

There is a great deal we dont know: which database?, how is the data being stored and read from the DB? How is thing being stored (as 1 string or as 3 ints)?

As a result, this is a conceptual solution. If these need to be unique and sequential, then you can't really determine the value unless and until it is saved to the DB. If sequence gaps are allowed, you have it a little easier - you could burn values when a record isn't saved. Hitting a counter table is wasteful if your DB supports Triggers. Using a simple table, in MySQL:

{ID int(11), Name (varchar), Descr (varchar), Major (int), Minor (int), Revision (int)}

If you are storing it as one string value, you may want to rethink that - individually they have meaning and are also being manipulated that way, so they are distinct data elements. You can easily CONCAT them in SQL or convert them to a System.Version type in code. Add a before insert trigger:

    SET @maxR = 0;
    SET @maxR = (SELECT Max(Minor) FROM jamversion WHERE Major = NEW.Major);
    If (ISNUll(@maxR)) THEN 
        SET @maxR = 1000;
    END IF;
    SET NEW.Minor = @maxR + 1;

You may need to add a LOCK, but we dont even know what the DB is.

Your code would not pass the Minor value, the db supplies it. Adding a row suing SQL and ExecuteNonQuery your have to go get the record to see the value used. Using a DataTable and a DataAdapter you could refresh. A quick test:

enter image description here

I added 100 rows each with a random Major value between 1 and 5. When sorted by Major:

  • 2 the Minor values are unique
  • 2 Minor increment sequentially
  • 2 the Ids are also in ascending order by Minor, indicating that the order inserted matches
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download