How to generate Auto Number(Id) in vb6

[Amiet Mhaske]

Hi, guys I am working on vb project, which will save client database.

I've done the coding of ADD, NEW, SAVE, EDIT button. Now, I am stuck at DELETE button. When I delete any particular client then I want my client_id (it's a column in my access database) should get updated (like if I have five clients in database and if I delete 3rd client, then the client_id should start from 1,2,3,4; not like 1,2,4,5)

I am using ADODB and Ms-access at backend, please help!!!!!

for DELETE, I've done following coding:

Private Sub cmdDelete_Click()

If rs.State = 1 Then rs.Close
admincon.Execute "delete from clientdetails where client_id=" & (lblId.Caption) & ""
MsgBox ("Record is Deleted successfully")
Clear
generateid ' It's a function i am using for generating ids. It's coding is given follow
Unload Me
frmAddClient.Show

End Sub

And for function generateid, I've done following coding:

Public Sub generateid()
Dim i As Integer
i = 1
If rs.State = 1 Then rs.Close
rs.Open "clientdetails", admincon, adOpenDynamic, adLockOptimistic, adCmdTable
Do
admincon.Execute "update clientdetails set client_id=" & i & ""

i = i + 1
rs.movenext

Loop While rs.EOF = True

End Sub

[WaltP]

Hi, guys I am working on vb project, which will save client database.

I've done the coding of ADD, NEW, SAVE, EDIT button. Now, I am stuck at DELETE button. When I delete any particular client then I want my client_id (it's a column in my access database) should get updated (like if I have five clients in database and if I delete 3rd client, then the client_id should start from 1,2,3,4; not like 1,2,4,5)

No it shouldn't. Once assigned, a client ID should never be changed. It is their unique value used throughout the system. All records pertaining to that person should be using this number and not their name, so the change would be monumental in a 'real' database.

What do you think would happen in the real world if, when someone stops driving, all the driver's license numbers changed?

[Netcode]

WaltP is very right. Please all IDs should remain unique

[QVeen72]

Hi,

Yes, Client_ID's should be Unique.
But still if, you want to renumber them, Change your code to :

Public Sub generateid()
    Dim i As Integer
    i = 1
    If rs.State = 1 Then rs.Close
    rs.Open "clientdetails", admincon, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
       rs("Client_ID") = i
       rs.Update
       i = i + 1
       rs.MoveNext
    Loop 
End Sub

Regards
Veena

[WaltP]

Hi,

Yes, Client_ID's should be Unique. But still if, you want to renumber them, Change your code to :

Public Sub generateid()
    Dim i As Integer
    i = 1
    If rs.State = 1 Then rs.Close
    rs.Open "clientdetails", admincon, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
       rs("Client_ID") = i
       rs.Update
       i = i + 1
       rs.MoveNext
    Loop 
End Sub

Regards Veena

But what about all the other database records in the other tables that have the ID in them?

[Amiet Mhaske]

WaltP, actually in my case client ID is not a primary key. It's actually requirement of my project that when i delete the customer client id should get updated.

[Amiet Mhaske]

let me first try this code

[Amiet Mhaske]

Hi,

Yes, Client_ID's should be Unique. But still if, you want to renumber them, Change your code to :

Public Sub generateid()
    Dim i As Integer
    i = 1
    If rs.State = 1 Then rs.Close
    rs.Open "clientdetails", admincon, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
       rs("Client_ID") = i
       rs.Update
       i = i + 1
       rs.MoveNext
    Loop 
End Sub

Regards Veena

You are awesome man, that code worked well for me. Thanks a lot!!!

[Amiet Mhaske]

But what about all the other database records in the other tables that have the ID in them?

Thanks for your help