I'm having a UserID field in my table and I need to get that value in my app and whenever I would like to add a new record it should increment by value '1'.
So this is how I'm trying to get the last ID enetered from my table.
For Example I'm having a value as "A000" and I need to increment that value by '1' so that it should become "A001" and so on..and after 'A999' as pointed out by PinnyM' it should become 'A1000'.
I don't want to write any stored procedures or anyother way from database.I would like to do it in a simpler way using my existing code.
Dim strConnection As String = "Data Source=.\SqlExpress;Initial Catalog=Subscription;Integrated Security=True"
Dim con As New SqlConnection(strConnection)
con.Open()
Dim comm As New SqlCommand
comm.CommandText = "SELECT MAX(UserID) FROM Customers"
comm.Connection = con
Dim MaxUserID As Object = comm.ExecuteScalar()
txtID.text=MaxUserID
I got it managed to work by doing it this way:
Based on the comments above, I would propose:
What this does is:
I would absolutely not try to do this using a character based key as the
UserID
appears to be built. Also, note that this is not atomic and doesn't guarantee that another connection won't insert a row before you do. So it's really returning an optimistic value that may not be actually be correct. If this doesn't work for you, then consider letting IDENTITY do its work for you and get the value after the row was created usingSCOPE_IDENTITY()