0
        Dim sqlsporring As String = "SELECT MAX(pID) AS pID FROM person_kunde"

    kobling.sporring(user, password, sqlsporring)
    svar = kobling.hentData()

    Dim temprad As DataRow
    Dim nyID, modID As String
    For Each temprad In svar.Rows
        nyID = temprad("pID")
        modID = nyID + 1
        nyKundeId = modID
    Next temprad

This code essentialy gives me what I need. It finds the highest ID in the database and gives the ID for (in this case, a new user) the value increased by 1. The problem is that my database store the ID for this user with 4 digits, 0001, 0002, 0003 etc. while this code just gives the new user the ID 4.

Is there a simple way to force the new ID to be 4 digits, since It will look foolish if i add 3 x '0' manually with hardcode once there are more than 9 users within this base i would prefeer a more elegant solution.

Edit As always, thanks for all the pointers, didn't help me with this issue, tho I want to improve my code.

Should have mentioned before that this was used for a INSERT - asking, and this solved it:

nyKundeId.ToString("0000")

Thank you for the MySQL - how to front pad zip code with “0”? - link, that did the trick :)

Freshman
  • 293
  • 3
  • 8
  • 19

2 Answers2

1

Just store the integer 4. Really. Format it to show extra 0's on output using the LPAD() function or (for preference) in your application code like this: nyKundeId.PadLeft(4, "0"c) This is how databases are supposed to work.

Also, the VB code you posted is a horrible way to increment your user IDs. It's creating a race condition, such that if you have an event that generates a large number of new users at the same time, you'll likely end up trying to create two users (or more) with the same ID. Instead, let your database do this for you, using it's auto_increment feature.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
0

SELECT LPAD(field_name,4, 0) FROM table;

Sirik Koster
  • 18
  • 3