DevX Home Today's Headlines   Articles Archive   Tip Bank   Forums

# Thread: how to automatically generate the number to database ?

1. Registered User
Join Date
Jul 2009
Location
Malaysia
Posts
49

## how to automatically generate the number to database ?

Hi friends,
How to write the vb6 code to automatically generate the number into database?
(Example: When i click the command button will generate the number into the database,just simple vb6 code.tq

2. Which number? What does it represent?

3. Registered User
Join Date
Jul 2009
Location
Malaysia
Posts
49
Example i want store the number 0001 into my database table 'barcode', after i click the command button. if i click the command button again will store the number 0002 into database table 'barcode'.

4. Well, a "number" is not in a table, it is in one of the Fields of one of the Records of the table.
You have to open the table, then read the appropriate Record. At this point you can either modify the right Field (adding 1, in your case), or add a new Record.

Did you already write some code?

5. Registered User
Join Date
Jul 2009
Location
Malaysia
Posts
49

## how to automatically generate the sequence number in database ?

Refer to attachment file,please provide the simple vb6 code, if i click the save or add button will generate the sequence number in db table 'barcode',my database is toys.mdb.

7. I just noticed you are using Access....in that case, you really don't need code.

Just set the data type of your field to AutoNumber and let Access do all the work.

8. Registered User
Join Date
Jul 2009
Location
Malaysia
Posts
49
but hack i using the data control to connect to the database and if i set the data type to autonumber the access only run the sequence number in 1 digit(e.g: 1,2,3,4,5..) how about if my number 955511700001,955511700002..??

9. Registered User
Join Date
Jul 2009
Location
Malaysia
Posts
49
Is better also provide some simple example program for my refence..tq

10. Originally Posted by hwkong1688
but hack i using the data control to connect to the database
Well, there is probelm number 1...throw the data control away (which is outdated by about 12 years or so anyway) and connect to your database using ADO programming code.

Here is an example
Code:
'in a module
Public ConnString As String
Public strBarCodeNo As String

Public Sub OpenDB()
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\path\to\your\database\toys.mdb;" & _
"Persist Security Info=False"

End Sub

OpenDB
End Sub

'now your database is open - get the next barcode number

Private Sub GetNewBarcode()
Dim sSQL As String
'get next available appeal number
sSQL = "SELECT MAX(barcode) FROM barcode "
If IsNull(adoRS(0)) Then 'then this is the first of the new records
strBarCodeNo = "95551170000"
Else 'just increment from what is there
strBarCodeNo = Format$(Val(Mid$(adoRS(0), 1) + 1), "0000")
End If
End Sub
Last edited by Hack; 06-04-2010 at 08:03 AM.

11. Registered User
Join Date
Jul 2009
Location
Malaysia
Posts
49
hi Hack, got this error msg in this line ADOCn.ConnectionString = ConnString
compile error

12. That code has worked for me in a zillion different places. Do you have reference set to ADO?

13. Registered User
Join Date
Jul 2009
Location
Malaysia
Posts
49
Hack, I find out maybe this line need to change
and which reference set i need to checked ?

14. You can't declare the same variable to be two different types
Code:
Public ADOCn As ADODB.Command
Public ADOCn As ADODB.Connection
That is not going to work.

However, as my original post indicates, you do take the command object and SET it as a connection object.
Code:
Set ADOCn = New ADODB.Connection

15. Registered User
Join Date
Jul 2009
Location
Malaysia
Posts
49
Hi Hack, now ok already no more error thank you guy...but when i run my program the database number still not in sequence,where should i type in the GetNewBarcode ?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

 FAQ Latest Articles Java .NET XML Database Enterprise