How to make an autogenerated Primary Key when add new data in VB.NET?

Question

My primary key type is char(5) and the format is BR000
i want to add for every new data insertion the PK will generate automatically

like 'BR000' +1

in examples:

ID_PRODUCT PRODUCT_NAME
========= ============
BR001 PENCILS
BR002 ERASER

if i input the next PRODUCT_NAME on textbox it should be BR003 for new ID_PRODUCT on the textbox id

Vulpes · Answer

The Form_Load handler is called only once just before the form loads. If you've recovered the lastId from the database in the Form_Load handler and want to display this in the textbox then I'd put this code in Form_Load: Me.txtPRODUCT_ID.Text = BR & lastId.ToString(D8) Each time the Add button is clicked this should now be incremented by the following line: Me.txtPRODUCT_ID.Text = GetNextId_Product()

Rasadul Alam Rashed · Answer

A primary key uniquely identifies each record within a table. But this is only a half story. The main purpose of a primary key is to make a link between others table for others information's. In this sense the primary key is a simple pointer between related records in different tables. As such the primary-key value has no meaning to the user and no association to the data to which it's assigned. When we make a primary then we should consider the following steps- The primary key value must be unique The primary key value can't be null The primary key value must exist when the record is created. for more: http://cybarlab.com/great-or-good-primary-key

mohamad guntur · Answer

thanks it works outi just need to add lastid +=1 above this code on the frmLoad. Me.txtPRODUCT_ID.Text = BR & lastId.ToString(D8) thanks once again it's really helpful :D

mohamad guntur · Answer

How about the formLoad to get the next PK Number automatically when it's loaded? just to show up the next ID should be input. i add this code to my formLoad: Me.txtPRODUCT_ID.Text = GetNextId_Product() the PK number get doubled (it'd be +1 now is 1+1) when the add button pressed i thought the systems was read 2 times: 1st time reading function at the add button 2nd time reading function at the formLoad so the result has accumulative number... sorry for my bad logic. xD

Vulpes · Answer

Yes it's a good idea for dealing with IDs greater than 999. The code for the GetNextId_Product method will now need to be: Public Function GetNextId_Product() As String lastId += 1 Return BR & last.Id.ToString( D8 ) End Function and the code to put the next ID in the textbox will be: Me.txtPRODUCT_ID.Text = GetNextId_Product()

mohamad guntur · Answer

I'm increasing the length of char for the Primary Key from 5 to 10. Is it a good idea? well if i'm adding a new record the PK textbox should be autogenerate the next PK/available PK which is not used in database. How to display it on textbox let say txtID_PRODUCT is my textbox to get the ID_PRODUCT.

Vulpes · Answer

The error message is suggesting that cmd.ExecuteScalar is returning null which means that either there are no records in the table or at least one existing record doesn't have anything in its ID_PRODUCT column. To deal with that try changing these lines: cmd.CommandText = SELECT TOP (1) ID_PRODUCT FROM PRODUCT ORDER BY ID_PRODUCT DESC Dim PRODUCT_ID As String = cmd.ExecuteScalar().ToString() lastId = Int32.Parse(Strings.Right(PRODUCT_ID 8)) ' is 8 right here it was 3 before? to these: cmd.CommandText = SELECT TOP (1) ID_PRODUCT FROM PRODUCT WHERE NULLIF(ID_PRODUCT '') IS NOT NULL ORDER BY ID_PRODUCT DESC lastId = 0 Dim obj As Object = cmd.ExecuteScalar() If obj IsNot Nothing Then lastId = Int32.Parse(Strings.Right(obj.ToString() 8)) End If

mohamad guntur · Answer

still get an error: Dim PRODUCT_ID As String = cmd.ExecuteScalar().ToString() lastId = Int32.Parse(Strings.Right(PRODUCT_ID 8)) it said: Object reference not set to an instance of an object. and at the txtID_PRODUCT (textbox which contains the ID_PRODUCT) it won't show the autogenerated PK when add new data.

Vulpes · Answer

You've forgotten to associate your SQL command with the connection: con = New SqlConnection(connectionString) Dim cmd As New SqlCommand cmd.Connection = con cmd.CommandText = SELECT TOP (1) ID_PRODUCT FROM PRODUCT ORDER BY ID_PRODUCT DESC

mohamad guntur · Answer

when i load the Product form which contains this code Private Sub frmProduct_Load(ByVal sender As System.Object ByVal e As System.EventArgs) Handles MyBase.Load Dim connectionString As String = Data Source=.\SQLEXPRESS;AttachDbFilename=|MyDir|;Integrated Security=True;User Instance=True con = New SqlConnection(connectionString) Dim cmd As New SqlCommand cmd.CommandText = SELECT TOP (1) ID_PRODUCT FROM PRODUCT ORDER BY ID_PRODUCT DESC Dim ID_PRODUCT As String = cmd.ExecuteScalar().ToString() lastId = Int32.Parse(Strings.Right(ID_PRODUCT 3)) Me.PRODUCTTableAdapter.Fill(Me.DB01DataSet1.PRODUCT) End Sub i got this error: Dim ID_PRODUCT As String = cmd.ExecuteScalar().ToString() ExecuteScalar: Connection property has not been initialized.

Vulpes · Answer

The reason for the error is that the compiler thinks that 'Right' refers to the Form's property of that name rather than the Right string function. If you change it to this then the error should go away: lastId = Int32.Parse( Strings. Right(ID_PRODUCT 3)) D3 is a special format string which the ToString method understands when it's applied to integers. If the integer has less than 3 digits it pads it out with leading zeros to 3 digits - so 1 becomes 001 and 25 becomes 025 but 999 becomes 999 as it's 3 digits already. Note also that last.Id should be lastId in the same line. Otherwise I'm hopeful that it will now work:)

mohamad guntur · Answer

So this code should be add inside the Form_Load? cmd.CommandText = SELECT TOP (1) PRODUCT_ID FROM PRODUCT ORDER BY PRODUCT_ID DESC Dim ID_PRODUCT As String = cmd.ExecuteScalar().ToString() lastId = Int32.Parse(Right(ID_PRODUCT 3)) get error: Error1'Public ReadOnly Property Right() As Integer' has no parameters and its return type cannot be indexed. ================================ this is the function made Public Function GetNextId_Product() As String lastId += 1 Return BR & last.Id.ToString (  D3  ) End Function i still don't get it the D3  if it replaced the 000 three digits of char as a string then it should be DDD (sorry i'm a noob T_T) ================================= and this should be inside the btnAdd Me.txtPRODUCT_ID.Text = GetNextId_Product() cmd.CommandText = INSERT INTO PRODUCT(PRODUCT_ID PRODUCT_NAME DATE_IN) VALUES (' & Me.txtPRODUCT_ID.Text & ' ' & Me.txtPRODUCT_NAME.Text & ' ' & _ Me.DateTimePicker1.Value.Date.ToString(yyyy-MM-dd) & ') cmd.ExecuteNonQuery()

Vulpes · Answer

ID_PRODUCT should be obtained like this: cmd.CommandText = SELECT TOP (1) PRODUCT_ID FROM PRODUCT ORDER BY PRODUCT_ID DESC Dim ID_PRODUCT As String = cmd.ExecuteScalar().ToString() lastId = Int32.Parse(Right(ID_PRODUCT 3)) This code should only be executed just once when you first connect to the database - so it shouldn't be in btnAdd or in the function which obtains the next ID. Suppose lastId = 3. Then lastId.ToString(D3) returns the string 003. It has nothing to do with the column header. As PRODUCT_ID is the primary key then it must be unique. So you can't start again from BR000. Either you need to use a different suffix from BR (say BS) or to increase the number of digits to four i.e. BR1000

mohamad guntur · Answer

lastId = Int32.Parse(Right(ID_PRODUCT 3)) ---> what this stand for? and was it still use to place inside the btnAdd or put inside the function? Public Function GetNextId_Product() As String lastId += 1 Return BR & lastId.ToString ( D3 ) ---> and D3 means the header of column? then it should be ID_PRODUCT? End Function just to prepare if it will be exceed more than 999 what code i should add more?

Vulpes · Answer

This line: lastId = Int32.Parse(Right(ID_PRODUCT 3)) doesn't want to be in the GetNextId_Product method but within whatever method (possibly Form_Load) is used to connect to the database and (perhaps) retrieve some initial data. Also you need to specify the D3 format if you want the ID to be filled out with leading zeros up to a length of 3: Public Function GetNextId_Product() As String lastId += 1 Return BR & last.Id.ToString ( D3 ) End Function The code in btnAdd should be prepended with this line: Me.txtPRODUCT_ID.Text = GetNextId_Product() cmd.CommandText = INSERT INTO PRODUCT(PRODUCT_ID PRODUCT_NAME DATE_IN) VALUES (' & Me.txtPRODUCT_ID.Text & ' ' & Me.txtPRODUCT_NAME.Text & ' ' & _ Me.DateTimePicker1.Value.Date.ToString(yyyy-MM-dd) & ') cmd.ExecuteNonQuery() Also is there a possibility that the ID could ever exceed 999? If there is then we need to add some code to deal with that.

mohamad guntur · Answer

where to call the GenNextId_Product() function? here is my code: Private lastId As Integer Public Function GetNextId_Product() As String lastId += 1 Return BR & lastId.ToString(ID_PRODUCT) lastId = Int32.Parse(Right(ID_PRODUCT 3)) End Function and this is the code of btnAdd when it's clicked then generate the data into database cmd.CommandText = INSERT INTO PRODUCT(PRODUCT_ID PRODUCT_NAME DATE_IN) VALUES (' & Me.txtPRODUCT_ID.Text & ' ' & Me.txtPRODUCT_NAME.Text & ' ' & _ Me.DateTimePicker1.Value.Date.ToString(yyyy-MM-dd) & ') cmd.ExecuteNonQuery()

Vulpes · Answer

Well autoincrement columns in ADO.NET can only be of types Short Integer or Long. If you want to use an alphanumberic value (i.e. type String) then you'll need to increment it yourself as each new record is created. One way of doing that would be to store the last (numeric) ID in a field and then write a method which updates the Id field and generates the next ID_PRODUCT code from it: Private lastId As Integer Public Function GetNextId_Product() As String lastId += 1 Return BR & last.Id.ToString(D3) End Function When the application starts you'd need to run a query which gets the ID_Product column from your database table sorted in descending order and selects the top 1. You can then initialize the lastId field as follows: lastId = Int32.Parse(Right(ID_PRODUCT 3))