GHJKL
Meta Builder
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
500 XP
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.
[inline:Table.jpg=Table Schema]
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.
The Code
In the form load event type the following code as shown below:
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:
I hope this will help some of you who do not know yet how to generate a key automatically.
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.
[inline:Table.jpg=Table Schema]
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.
The Code
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
- .
Open
(
)
- End
With
- Dim
sqlQRY As
String
=
"SELECT * FROM [KEY GENERATOR] WHERE TableName = '"
&
srcTable &
"'"
- 'create commands
- Dim
cmd As
OleDbCommand =
New
OleDbCommand(
sqlQRY, cnCustomers)
- Try
- '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
- cmdUpdate.
ExecuteNonQuery
(
)
- GetIndex =
intNextNo
- Catch
ex As
OleDbException
- MsgBox
(
ex, MsgBoxStyle.
Information
)
- Finally
- ' Close connection
- cnCustomers.
Close
(
)
- 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.