Link to home
Start Free TrialLog in
Avatar of D Patel
D PatelFlag for India

asked on

MYSQL : Instead of generating unique number at coding level what is an alternate way?

Hi EE,

I have following code in VB.NET.

Public Function GenerateUniNo(ByVal Length As Integer) As String
        Randomize()

        Dim ValidChars As String
        ValidChars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"

        Dim i, x As Integer
        Dim password As String = ""
        For i = 1 To Length
            x = Int(Len(ValidChars) * Rnd() + 1)
            password = password & Mid(ValidChars, x, 1)
        Next
        GenerateUniNo = password
    End Function

Open in new window


and it is called using following statement :

Dim dr = dbCommE.ExecuteReader()
            If dr.HasRows Then
                txtUniNo.Text = GenerateUniNo (4)
                If dr(0) = Trim(txtUniNo.Text) Then
                    txtUniNo.Text = ""
                    txtUniNo.Text = GenerateUniNo (4)
                End If
            End If

Open in new window


How to write trigger or procedure in MYSQL so that same task can be achieved?

Please guide.

Regards,
D Patel
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

You can generate row numbers in MySQL.
Can u give me ur input data and expected output.
Avatar of D Patel

ASKER

See in my above code, the input is random number out of A-Z & 0-9 with each combinations of 4 alphanumeric characters.

I need to write the procedure in MYSQL so that, it generate the number first and allot to specific user session.
Avatar of D Patel

ASKER

This unique number will not represent the row number, Instead will act to identify the specific item. There is another auto number field in a table.
Avatar of D Patel

ASKER

Output will be any random number between  36 * 36 * 36 * 36 = 1679616 combinations. (i.e. SV9P, AEZO, 1SRL etc...)
Avatar of D Patel

ASKER

This particular column will have only unique data.
Avatar of D Patel

ASKER

my requirement is only alphanumeric string data of length 4.
Avatar of Julian Hansen
Look at UUID(). This is a MySQL function that generates a GUID - which is guaranteed unique and the standard means by which we uniquely identify rows. You can use it in an INSERT statement or a SELECT
Example
INSERT INTO table (field) VALUES (UUID());

Open in new window

Avatar of D Patel

ASKER

@Julian :
You are right but I need only 4 character long alphanumeric string.
I think you should create a routine that will  create a base 36 number system and thus create your specific 4 letters-digit identifier...
Avatar of D Patel

ASKER

How to use such routine (function / procedure) in ASP.NET?
Avatar of D Patel

ASKER

Data should not overwrite (once generated number will always kept into database for future tracking) and only unique number will get stored?
I had made a routine last year for a contest but i can't remember where i kept it...i will take a look...it was standard .NET
Well my code should be buried inside the image of my old computer....i have found a CodeProject article that does exactly what you want
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D Patel

ASKER

@ Ryan Chong :
While I call procedure as below :
CALL insert_randomKey();

I get an error "Error Code: 1364. Field 'nID' doesn't have a default value"

Here, 'nID' is not Auto Increment field. The field is populated at the time of generating the new record (incremented by 1).

Actually, I need a functionality like this :
User call the procedure 'insert_randomKey()' on clicking the button.
system will auto add 100 blank records with the ID and randomkey and all the other fields will be blank (or default value).

BTW your solution will work for me.
'nID' is not Auto Increment field. The field is populated at the time of generating the new record (incremented by 1).
so, how you generate that nID at the first place? via C# code or backend database script? must it follow a sequence, etc?
Avatar of D Patel

ASKER

using VB.NET code behind On clicking the add button.
any logic to generate this nID ?
Avatar of D Patel

ASKER

Select Max(colName) from tblname;

If dr.HasRows Then
            If Not IsNumeric(dr(0)) Then
                id = 1
            Else
                id = dr(0)
                id = id + 1
            End If
        Else
            id = 1
        End If

Open in new window


This is on button click event.
so can field: nID contains duplicate values if i run insert_randomKey() many times?
Avatar of D Patel

ASKER

no it cannot. It is Unique field (Primary Key). But not auto Increment.
no it cannot. It is Unique field (Primary Key)
Why not just make nID as auto-incremental?
Avatar of D Patel

ASKER

Actually, the whole project is designed on this logic.
sorry, guessing it's still a bit vague for your requirements... (at least for me)

if you run insert_randomKey() many times, what's the expected output in your table?
Avatar of D Patel

ASKER

It will add the new record with nID generated and unique random key. And all the other fields with it default values.
just don't get the clue why nID should be unique but it can't be auto-incremental...

without sample output, i can't provide a proper solution for you.

other experts may provide their inputs if they got a better idea to handle this.
Avatar of D Patel

ASKER

Thanks for your Support.

Your solution really worked for me after converting to AutoIncremental.