Solved

vb.net get maximum id after JPE000003

Posted on 2010-08-20
6
309 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:doramail05
6 Comments
 
LVL 3

Expert Comment

by:LDH
ID: 33483132
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33483142
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33483152
Assuming the first part 'JPE' is having 3 characters.

Raj
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 1

Author Comment

by:doramail05
ID: 33483156
ya, any codes for sorting out the maximum record = JPE00000003
0
 
LVL 3

Expert Comment

by:LDH
ID: 33483164
strSQL = "SELECT * FROM cardholder ORDER BY [idfield] DESC"

0
 
LVL 25

Accepted Solution

by:
Luis Pérez earned 500 total points
ID: 33483810
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question