i want to Autogenerate id column in my database table , my code is given below . it is not workin it is giving me Exception as "startIndex Cannot be larger than Length of string , parameter:startIndex ".how should i solve it

  Private Sub AutoGenerateNo()

            Dim no As String
                cmd = New SqlCommand("select count(*) from Academic", conn)
                no = cmd.ExecuteScalar
                no = no.Substring(2)
                no = CInt(no) + 1

                If no > 0 And no <= 9 Then
                txtSessionId.Text = ("A000" + no)
                ElseIf no > 9 And no <= 99 Then
                txtSessionId.Text = ("A00" + no)
                ElseIf no > 99 And no <= 999 Then
                txtSessionId.Text = ("A0" + no)
                txtSessionId.Text = ("A" + no)
                End If
            Catch ex As Exception
        End Try

    End Sub
4 Years
Discussion Span
Last Post by adam_k

What database system are you using? Autoincrement fields are a function of the database and are usually integer fields. The DBMS assigns the value automatically. If you want something other than integer then you can select the largest value, strip off the non-numeric part, add one then get the new value as

"A" & newnum.ToString("0000")

This will ensure four digits with zero padding on the left.


I agree with Reverend, but I've got to say that if your error comes from line: no = no.Substring(2), that's because your count(*) returns a single-digit result.
If you remove that line, then your code will probably work, although there are errors in it's logic.
By error in the logic I mean that if you delete a record from your table, then you will be assigning duplicate IDs.
Instead try to use max(convert(int, right(column_name,len(column_name)-1))) in your select instead of count(*)

PS: The max... code is for SQL, you might need to adjust it depending on your db.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.