AUTO GENERATE ID NUMBER ISSUE

[Omi4u]

Dear All

How are you.

guys im facing issue in my code that i am using for auto generate id . code is mentioned below

#Region "command"
Sub coaid()
Dim autoIDx As Single
conn.Open()
cmd = New SqlCommand("select count(*) as coaid from coa")
cmd.Connection = conn
objdatareader = cmd.ExecuteReader

While objdatareader.Read
autoIDx = Val(objdatareader.Item("coaid").ToString + 1)
End While

Select Case Len(Trim(autoIDx))
Case 1 : CoaidTextEdit.Text = "COA-1000-00" + Trim(Str(autoIDx))
Case 2 : CoaidTextEdit.Text = "COA-100-0" + Trim(Str(autoIDx))
Case 3 : CoaidTextEdit.Text = "COA-10-0" + Trim(Str(autoIDx))
End Select
conn.Close()
End Sub
#End Region


now issue is

if i have 5 entries like below

1000

1001

1002

1003

1004


now if user delete ID no 1001 then new record must take id no 1001 but with my code it not using it. it taking last saved it

like if you add record after deleting record no 1001 then it showing id no 1004 instead of 1005 or the deleted one.


please guide

Continue reading...