Counter problem

Question

igho 0 Newbie Poster

15 Years Ago

I TRY TO MAKE CODE COUNTER WITH CODE MR/IR/001 IN MY APPLICATION USING VISUAL BASIC 2008, BUT WHEN THE CODE GET MR/IR/999 IT`S STUCK AND NOT INCREASING, I WANT TO MAKE THE CODE INCREASE TO :
MR/IR/1000
MR/IR/1001
MR/IR/1002
MORE AND MORE ...

FOR DATABASE I`M USING MS ACCESS 2007

PLZ HELP ME...

------------------------------------------------------------------------------------

Dim strTemp As String = ""
Dim strValue As String = ""
Dim sql As String

sql = "SELECT * FROM DETAILPERIKSA ORDER BY IDPERIKSA DESC"
cmd = New OleDbCommand(sql, Conn)
dtReader = cmd.ExecuteReader
If dtReader.Read Then
strTemp = Mid(dtReader.Item("IDPERIKSA"), 7, 4)

Else
TxtDETAILTRANS.Text = "MR/IR/001"
Exit Sub
End If
msgBox(strTemp)
strValue = Val(strTemp) + 1
TxtDETAILTRANS.Text = "MR/IR/" & Mid("000", 1, 3 - strValue.Length) & strValue

.net:vb.net visual-basic

Edited 15 Years Ago by peter_budo because: Keep It Clear - Do wrap your programming code blocks within [code] ... [/code] tags

3 Contributors
9 Replies
210 Views
15 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by igho

Dani AI

Generated 7 Months Ago

Nice troubleshooting so far — ’s idea (extract the trailing number and increment) and ’s padding suggestion fixed the symptom, but the real cause is the way the DB and your code treat the whole ID as text. Sorting or extracting with fixed-length assumptions will break once you pass three digits. A more robust approach is to ask the database for the highest numeric suffix and increment that number; that avoids relying on lexicographic ORDER and works whether existing IDs look like MR/IR/001 or MR/IR/0999.

Here is a concise VB.NET pattern that reads the numeric maximum and builds the next code (uses ExecuteScalar so you get one value back from the engine):

Dim sql As String = "SELECT MAX(Val(Mid([IDPERIKSA], 7))) FROM DETAILPERIKSA"
Using cmd As New OleDbCommand(sql, Conn)
    Dim obj = cmd.ExecuteScalar()
    Dim maxNum As Integer = 0
    If obj IsNot Nothing AndAlso Not Convert.IsDBNull(obj) Then Integer.TryParse(obj.ToString(), maxNum)
    Dim nextNum As Integer = maxNum + 1
    TxtDETAILTRANS.Text = "MR/IR/" & nextNum.ToString().PadLeft(4, "0"c)
End Using

Notes and cautions:

Val(Mid(...)) converts the trailing digits to a number so the DB computes the true maximum; no need to rewrite old MR/IR/001 rows to MR/IR/0001.
For a long-term, concurrency-safe design add an integer SequenceNo (or use Access AutoNumber) and format that value at display time. Relying on a computed max in a multi-user environment can cause race conditions unless you use transactions/locking or unique constraints with retry logic.
If you do want to normalize existing IDs in-place, a single Access update can reformat them:
UPDATE DETAILPERIKSA SET IDPERIKSA = 'MR/IR/' & Format(Val(Mid([IDPERIKSA],7)),'0000');

This avoids brittle substring-length hacks and makes the counter reliable as you move past 999.

All 9 Replies

codeorder 197 Nearly a Posting Virtuoso

15 Years Ago

See if this helps.

Dim sCounter As String = "MR/IR/001" '// for testing.
        Dim sTemp As String = Nothing '// temp string.
        sTemp = sCounter.Substring(sCounter.LastIndexOf("/") + 1) '// get all char.s after the last "/".
        sTemp = CInt(CInt(sTemp) + 1).ToString("000") '// increment # +1, and Format it to 3 char. if less than 1000.
        MsgBox(sCounter.Substring(0, sCounter.LastIndexOf("/") + 1) & sTemp) '// display result.

codeorder 197 Nearly a Posting Virtuoso

15 Years Ago

This was not tested, but should work.

Dim strTemp As String = ""
        Dim sql As String = "SELECT * FROM DETAILPERIKSA ORDER BY IDPERIKSA DESC"
        cmd = New OleDbCommand(sql, Conn)
        dtReader = cmd.ExecuteReader
        If dtReader.Read Then
            strTemp = dtReader.Item("IDPERIKSA").ToString
            strTemp = strTemp.Substring(strTemp.LastIndexOf("/") + 1) '// get all char.s after the last "/".
        Else
            TxtDETAILTRANS.Text = "MR/IR/001"
            Exit Sub
        End If
        'MsgBox(strTemp)
        TxtDETAILTRANS.Text = "MR/IR/" & CInt(CInt(strTemp) + 1).ToString("000") '// increment # +1, and Format it to 3 char. if less than 1000.

P.manidas 39 Posting Whiz in Training

15 Years Ago

Dear Igho,

I have tested and it works fine.

If you need counter MR/IR/0001 to MR/IR/9999

Try this codes as i modify two lines ....

dtReader = cmd.ExecuteReader
If dtReader.Read Then
strTemp = Mid(dtReader.Item("IDPERIKSA"), 7, 4)

Else
'TxtDETAILTRANS.Text = "MR/IR/001" 
TxtDETAILTRANS.Text = "MR/IR/0001" 'change here
Exit Sub
End If
msgBox(strTemp)
strValue = Val(strTemp) + 1
'TxtDETAILTRANS.Text = "MR/IR/" & Mid("000", 1, 3 - strValue.Length) & strValue 
 TxtDETAILTRANS.Text = "MR/IR/" & Mid("0000", 1, 4 - strValue.Length) & strValue ' change here

or If you need counter MR/IR/00001 to MR/IR/99999

Try this codes as i modify three lines....

dtReader = cmd.ExecuteReader
If dtReader.Read Then
'strTemp = Mid(dtReader.Item("IDPERIKSA"), 7, 4)
strTemp = Mid(dtReader.Item("IDPERIKSA"), 7, 5) ' change here

Else
'TxtDETAILTRANS.Text = "MR/IR/001" 
TxtDETAILTRANS.Text = "MR/IR/00001" 'change here
Exit Sub
End If
msgBox(strTemp)
strValue = Val(strTemp) + 1
'TxtDETAILTRANS.Text = "MR/IR/" & Mid("000", 1, 3 - strValue.Length) & strValue 
 TxtDETAILTRANS.Text = "MR/IR/" & Mid("00000", 1, 5 - strValue.Length) & strValue ' change here

Edited 15 Years Ago by P.manidas because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

igho 0 Newbie Poster · Answer 1 · 2011-04-16T14:11:55+00:00

See if this helps.

Dim sCounter As String = "MR/IR/001" '// for testing.
        Dim sTemp As String = Nothing '// temp string.
        sTemp = sCounter.Substring(sCounter.LastIndexOf("/") + 1) '// get all char.s after the last "/".
        sTemp = CInt(CInt(sTemp) + 1).ToString("000") '// increment # +1, and Format it to 3 char. if less than 1000.
        MsgBox(sCounter.Substring(0, sCounter.LastIndexOf("/") + 1) & sTemp) '// display result.

thanks for reply :)

but The code is not reading the database, when try your code it`s pop up in msgbox, The code MR/IR/001 I want to show in textbox a.k.a TxtTxtDETAILTRANS.Text in my aplications

My database already had code from MR/IR/001 to MR/IR/999, I want to fill the database with MR/IR/1000 to MR/IR/5000 :'(:'(

by the way. thanks for reply

igho 0 Newbie Poster · Answer 2 · 2011-04-16T15:43:44+00:00

I try your code, and when start debuging the counter get MR/IR/1000 and I try input my data when I finished Input my data I click the button in my aplication it`s work fine...:)
when I check the record is in my database I Think is work... so I try again to start a new order data when click start the button it`s still show the record MR/IR/1000 (The COUNTER Won`t INCREASING0 it should had MR/IR/1001:'(:
so, I try to change the char from 3 to 4
TxtDETAILTRANS.Text = "MR/IR/" & CInt(CInt(strTemp) + 1).ToString("000")
to
TxtDETAILTRANS.Text = "MR/IR/" & CInt(CInt(strTemp) + 1).ToString("0000")

:'(:'(it`s still not change anything, the counter record still show MR/IR/1000 not MR/IR/1001

thanks for reply

still need help

igho 0 Newbie Poster · Answer 3 · 2011-04-16T16:34:09+00:00

I try your code I change Two Lines

#
'TxtDETAILTRANS.Text = "MR/IR/001"
#
TxtDETAILTRANS.Text = "MR/IR/0001" 'change here
#
#
'TxtDETAILTRANS.Text = "MR/IR/" & Mid("000", 1, 3 - strValue.Length) & strValue
#
TxtDETAILTRANS.Text = "MR/IR/" & Mid("0000", 1, 4 - strValue.Length) & strValue ' change here

but I it`s show MR/IR/1000 it`s not increasing to MR/IR/1001, I already had database record from MR/IR/001 to MR/IR/999, I want to continue to fill the database record after the record MR/IR/999 but it`s just stuck.....

what was wrong :'(:'(:'(

thanks for reply

P.manidas 39 Posting Whiz in Training · Answer 4 · 2011-04-16T16:56:25+00:00

You have to change the filed data MR/IR/001 --- MR/IR/999 to MR/IR/0001 -- MR/IR/0999 in your database then my codes will works. --- for first 2 line changer code

you can check by changing the last records filed MR/IR/999 to MR/IR/0999

And same as for three line changer also...

P.manidas 39 Posting Whiz in Training · Answer 5 · 2011-04-16T17:11:45+00:00

You need four digit number after "MR/IR/", so you have to keep your field like MR/IR/0001, MR/IR/0002 .... MR/IR/0997, MR/IR/0998 then it will increase to MR/IR/0999, MR/IR/1000, MR/IR/1001 so on, up to MR/IR/9999.

If you keep your filed MR/IR/001, MR/IR/002 -- MR/IR/999. It will give error like "Argument length must be greater or equal to zero".

see it and check it.

igho 0 Newbie Poster · Answer 6 · 2011-04-16T21:22:14+00:00

Thank a lot for your replay, Thanks very to codeorder and P.Manidas for post replay in my thread...
Problem solved when change the database like p.manidas told me, I change The table database that related to the code and finally It`s Work

Thank a lot :):):)

Counter problem

Dani AI

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers