How to automatically generate the sequence number in database ?

[hwkong1688]

How to write the vb6 code to automatically generate the number into database?
Example i want store the number 955117000001 into my database table 'barcode', after i click the command button. if i click the command button again will store the next number 955117000002 into database table 'barcode'.if i set the data type autonumber in Microsoft access is only can count on 1 digits number,I connect to my database using ADO.

 

[Andrzejek]

You table name is barcode, and let's say the Firld in your table is called BarNumber. If you want to INSERT a record with next BarNumber:

INSERT INTO barcode (BarNumber)
VALUES (SELECT MAX(BarNumber) + 1 FROM barcode)

Would that work for you?

Have fun.

---- Andy

 

[dilettante]

While Counter/Identity fields are long integers they can certainly be started at values besides 1 and even incremented by values other than 1:

CREATE TABLE tblCodeCounterDataTypes
   Field1_IDENTITY IDENTITY(90000000,5),
   Field2 TEXT(10))

However these are meant for use as internal unique ID values, not to hold data, If you're trying to do that you'll find you have other problems very soon.

 

[TysonLPrice]

Adding to Andrzejek post I would lock the table.

INSERT INTO barcode (BarNumber)
VALUES (SELECT MAX(BarNumber) + 1 FROM barcode with (updlock))

 

[JoeAtWork]

In general this is not something you do in VB code but at the database level. While you are calculating the number in VB somebody else may have already used up the same number and updated the database.

 

[hwkong1688]

Thank you for your helps friends..

 

[Andrzejek]

I am always willing to learn somethine new, that's why I would ask:

TysonLPrice - why would you lock the table while inserting a new record?

JoeAtWork - I cannot think of a scenario of two people inserting the same record number (using above SQL). And VB is not calculating the number, the new number comes from DB in the Insert statement - so "somebody else may" NOT "have already used up the same number and updated the database."

Have fun.

---- Andy

 

[TysonLPrice]

TysonLPrice - why would you lock the table while inserting a new record?

Lock the table in the select to get the next number thinking someone else may be inserting also. It would need to be done right before the insert. Here's an example of what I meant (not posted):

set @EmployerClaimNumber = isnull((select max(EmployerClaimNumber)  from Claim with (updlock) 
		    where fkEmployerAddress = @pkEmployerAddress),0) + 1

 

[JoeAtWork]

Andy - I was not referring to your SQL statement but to the OP's original question "How to write the vb6 code to automatically generate the number".