Auto Generate Key
This tutorial will teach you on how to create a key automatically as a primary key in your table. It is sometimes needed to create your own key to format it on your own needs. Say for example you’d like to format it to start as four (4) digits rather than starting from one (1).
The code herein is based on the code that I have already submitted on some of my program like hotel reservation system. But these time a simplified one. I created this tutorial because there are some visitors in this website who asked this before.
In order to create an auto generated key for your table, all you need is a table that will hold the last key of the record that is auto generated. Below is the example of the table that we need using Microsoft Access.
Example of data within the “Key Generator” table.
[inline:Auto Generate Key Data.jpg=Auto Generate Key Data Sample]
As you can see at the above example every table has its own key for the next value.
The above record is incremented by one (1) once called.
In the form load event type the following code as shown below:
- Dim CustomerNo As Integer
- CustomerNo = GetIndex("Customers")
- txtCustomerNo.Text = CustomerNo
This will call a function called GetIndex and return the next value of the NextNo field.
The following is the code to return and increment a value for NextNo field:
- Public Function GetIndex(ByVal srcTable As String) As Long
- Dim cnCustomers As OleDbConnection
- 'create connection
- cnCustomers = New OleDbConnection
- With cnCustomers
- If .State = ConnectionState.Open Then .Close()
- .ConnectionString = cnString
- End With
- Dim sqlQRY As String = "SELECT * FROM [KEY GENERATOR] WHERE TableName = '" & srcTable & "'"
- 'create commands
- Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, cnCustomers)
- 'create data reader
- Dim rdr As OleDbDataReader = cmd.ExecuteReader
- Dim intNextNo As Integer
- 'loop through result set
- While (rdr.Read)
- intNextNo = rdr("NextNo")
- End While
- intNextNo = intNextNo + 1
- 'define update statement
- 'this will update the table "key generator" to a new value
- Dim sqlUpdate As String = "UPDATE [Key Generator] SET [Key Generator].NextNo = " & intNextNo & " WHERE TableName='" & srcTable & "'"
- Dim cmdUpdate As OleDbCommand = New OleDbCommand(sqlUpdate, cnCustomers)
- 'execute nonquery to update an index
- GetIndex = intNextNo
- Catch ex As OleDbException
- MsgBox(ex, MsgBoxStyle.Information)
- ' Close connection
- Console.WriteLine("Connection closed.")
- End Try
- End Function
I hope this will help some of you who do not know yet how to generate a key automatically.