Solved

vb.net get maximum id after JPE000003

Posted on 2010-08-20
6
306 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now