vb.net get maximum id after JPE000003

i have 3 records in cardholder table where would like to take the last id
JPE00000001
JPE00000002
JPE00000003
and make a string call JPE0000004
with the code below,
Try
            Using objDataCtrl As New DBCore(gstrConnectionStr)

                strSQL = "SELECT * FROM cardholder"

                objReader = objDataCtrl.ExecuteReader(strSQL, "")

                If objReader.Read Then



                End If

            End Using

        Catch ex As Exception
            strError = ex.Message
        End Try

Open in new window

LVL 1
doramail05Asked:
Who is Participating?
 
Luis PérezConnect With a Mentor Software Architect in .NetCommented:
Check my snippet.

Hope that helps.
Try
    Using objDataCtrl As New DBCore(gstrConnectionStr)
        strSQL = "SELECT MAX(name_of_id_field_what_contains_jpe_string) FROM cardholder"
        Dim result As String = String.Empty + objDataCtrl.ExecuteScalar(strSQL, "")
        Dim nextID As String
        If result.Length.Equals(0) Then
            nextID = "JPE000001"
        Else
            nextID = "JPE" + (Integer.Parse(result.Substring(3)) + 1).ToString("000000")
        End If
        'Right now you can check nextID variable to get your next ID
    End Using
Catch ex As Exception
    strError = ex.Message
End Try

Open in new window

0
 
LDHCommented:
Try the code below.

lastid = Right("JPE0000003", 4)
lastid = CInt("1" & lastid)
lastid = lastid + 1;
lastid = "JPE" & Right(Cstr(lastid), 1)

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Modify the query to return the maximum record (that having 'JPE00000003')

Then write code like attached.

Raj
Dim sLastString As String
        Dim nLastValue As Integer

        sLastString = "JPE00000003" '' <-- From query
        sLastString = Convert.ToInt32(Right(sLastString, Len(sLastString) - 3))
        sLastString = Left(sLastString, Len(sLastString) - Len(Convert.ToString(nLastValue + 1))) & nLastValue + 1

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Rajkumar GsSoftware EngineerCommented:
Assuming the first part 'JPE' is having 3 characters.

Raj
0
 
doramail05Author Commented:
ya, any codes for sorting out the maximum record = JPE00000003
0
 
LDHCommented:
strSQL = "SELECT * FROM cardholder ORDER BY [idfield] DESC"

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.