Loading

Auto Generate Key

Submitted by: 
Visitors have accessed this post 11166 times.


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:

  1. Dim CustomerNo As Integer
  2.  
  3. CustomerNo = GetIndex("Customers")
  4. 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:

  1. Public Function GetIndex(ByVal srcTable As String) As Long
  2. Dim cnCustomers As OleDbConnection
  3. 'create connection
  4. cnCustomers = New OleDbConnection
  5.  
  6. With cnCustomers
  7. If .State = ConnectionState.Open Then .Close()
  8.  
  9. .ConnectionString = cnString
  10. .Open()
  11. End With
  12.  
  13. Dim sqlQRY As String = "SELECT * FROM [KEY GENERATOR] WHERE TableName = '" & srcTable & "'"
  14.  
  15. 'create commands
  16. Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, cnCustomers)
  17.  
  18. Try
  19. 'create data reader
  20. Dim rdr As OleDbDataReader = cmd.ExecuteReader
  21. Dim intNextNo As Integer
  22.  
  23. 'loop through result set
  24. While (rdr.Read)
  25. intNextNo = rdr("NextNo")
  26. End While
  27.  
  28. intNextNo = intNextNo + 1
  29.  
  30. 'define update statement
  31. 'this will update the table "key generator" to a new value
  32. Dim sqlUpdate As String = "UPDATE [Key Generator] SET [Key Generator].NextNo = " & intNextNo & " WHERE TableName='" & srcTable & "'"
  33.  
  34. Dim cmdUpdate As OleDbCommand = New OleDbCommand(sqlUpdate, cnCustomers)
  35.  
  36. 'execute nonquery to update an index
  37. cmdUpdate.ExecuteNonQuery()
  38.  
  39. GetIndex = intNextNo
  40. Catch ex As OleDbException
  41. MsgBox(ex, MsgBoxStyle.Information)
  42. Finally
  43. ' Close connection
  44. cnCustomers.Close()
  45. Console.WriteLine("Connection closed.")
  46. End Try
  47. End Function

I hope this will help some of you who do not know yet how to generate a key automatically.




Comments

Good day
thanks am enjoy ur programs pls can u send me the cods of a simple calculator,and a table on banking system.and the cods.thanks have a nice day.

Good morning. I really find your codes very helpfull. But please i have a problem. I need a code to automatically upload information form excel to access table. The code that i tried would do taht but replaces the data that already exists in the table, it doesnt update or add on to the already existing data.

Thanks in advance

OITSILE MAPITSE (BOTSWANA)

oitsilem@yahoo.com

Add new comment

(If you're a human, don't change the following field)
Your first name.
(If you're a human, don't change the following field)
Your first name.

Filtered HTML

  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
To prevent automated spam submissions leave this field empty.