handling 18 digit numbers

Hi,
I need to store a serial number in the DB which should be 18 digits in size. when a new record comes in I should take the 18 digit number and add one. How is it possible in VB?
harishkgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CronanCommented:
Use a nvarchar(18) in the db and a string in VB?
CronanCommented:
Just had an idea - I'm sketching out a class that will hold the number in VB and do the Incr
harishkgAuthor Commented:
then how can I increment the data stored in the string to one when I enter new record in db
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

harishkgAuthor Commented:
this requirement is in VB 6
Dirk HaestProject managerCommented:
What datatype are you using in vb ?

dim YourNumber as long
' convert your varchar from database to long value and add 1
YourNumber = clng(<value from database>) + 1

Return Yournumber to the database
CronanCommented:
That's the issue - there is no numeric datatype that will hold an 18 digit number
So that's what this class is for.
Right now all it does is increment, but you could extend it ...

Use it like this (I called the class BigID):

    Dim a As BigID
    Set a = New BigID
   
    a.Value = "123"
    a.Inc
    Debug.Print a.Value
   
    Set a = Nothing

--------START OF CLASS CODE-------
Option Explicit

Private m_Low As Long
Private m_High As Long

Public Property Get Value() As String
    Value = JoinID(m_Low, m_High)
End Property

Public Property Let Value(ByVal RHS As String)
    Dim sValue As String
    Dim sLow As String, sHigh As String
   
    If Not IsNumeric(RHS) Then Exit Property
   
    sValue = Trim$(RHS)
   
    Call SplitID(sValue, sLow, sHigh)
   
    m_Low = CLng(sLow)
    m_High = CLng(sHigh)
End Property

Public Sub Inc()
    If m_Low = 999999999 Then
        m_Low = 0
        If m_High = 999999999 Then
            m_High = 0
        Else
            m_High = m_High + 1
        End If
    Else
        m_Low = m_Low + 1
    End If
End Sub

Private Sub SplitID(ByVal Value As String, ByRef Low As String, ByRef High As String)
    Dim sLow As String
    Dim sHigh As String
    Dim sValue As String
    Dim lenLow As Long, lenValue As Long, lenHigh As Long

    Low = "0"
    High = "0"

    sValue = Trim$(Value)
    lenValue = Len(sValue)
   
    If lenValue = 0 Then Exit Sub
    sLow = Trim$(Right$(sValue, 9))
    Low = sLow
    lenLow = Len(sLow)
   
   
    If lenValue > 9 Then
        sHigh = Trim$(Left$(sValue, lenValue - 9))
        High = sHigh
        lenHigh = Len(sHigh)
    End If

End Sub

Private Function JoinID(ByVal Low As Long, ByVal High As Long) As String
    Dim sLow As String, sHigh As String
   
    sLow = PadID(Low)
    sHigh = PadID(High)
   
    JoinID = sHigh & sLow
End Function

Private Function PadID(ByVal Value As Long) As String
    Dim sValue As String
   
    sValue = CStr(Value)
   
    PadID = String$(9 - Len(sValue), "0") & sValue
End Function

Dirk HaestProject managerCommented:
I justed to increment a 16-digit number (string) with the function here below

Sub test()
 Dim Num As String
 Num = "9999999999919999"
 Num = Increment(Num)
 MsgBox Num
End Sub

Public Function Increment(YourNumber As String)
   Dim NewNumber As String
   Dim Number As Integer
   Dim AddOne As Boolean
   
   If YourNumber = "9999999999999999" Then
         MsgBox "Maximum reached"
         Exit Function
   End If
   AddOne = True
   While Len(YourNumber) > 0
        Number = CInt(Right(YourNumber, 1))
        If AddOne = True Then
            Number = Number + 1
            AddOne = False
        End If
        If Number > 9 Then
             Number = 0
             NewNumber = NewNumber & "0"
             AddOne = True
        Else
             NewNumber = Number & NewNumber
        End If
        YourNumber = Left(YourNumber, Len(YourNumber) - 1)
   Wend
   Increment = NewNumber
End Function

GrahamSkanRetiredCommented:
Where can I buy shares in the company that expects to sell 10^18 serialised items?  That's a real (British) trillion or a million American trillions.

Actually, I would guess that a lot of the left part contains model information and that only the right hand needs incrementing, so you could store it as a string, take the serialising part (say 10 digits), increment that and rejoin to the right hand part?
GrahamSkanRetiredCommented:
At the end, for 'right hand part?'  read 'left hand part.'  
CronanCommented:
My class (see above) assumed a little-endian number, so should do the job too
drydenhoggCommented:
It is wrong to say that no VB data type can store and 18 digit number, the decimal datatype can store up to 28 digits. Largest value being 79,228,162,514,264,337,593,543,950,335. (assuming no decimal places)

Decimal itself is not a base data type but must be stored within a variant. Values can be placed within a decimal using the CDec(value) function.

An example piece of code to show adding 1 to the 18 digit number

MyBigNumber = "123456789012345678"
MsgBox FormatNumber(MyBigNumber, 0, vbTrue, vbFalse, vbTrue) ' Just so you can see it before
MyBigNumber = CDec(MyBigNumber) + 1
MsgBox FormatNumber(MyBigNumber, 0, vbTrue, vbFalse, vbTrue) ' and now after

Ive put in the parameter to show comma grouping of numbers, just to make it easier to read when you run the code.


ADH

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrahamSkanRetiredCommented:
Yes Cronan,
Absolutely
CronanCommented:
drydenhogg

good one
DanRollinsCommented:
Moderator, my recommended disposition is:

    Accept drydenhogg's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.