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.
and it is called using following statement :
How to write trigger or procedure in MYSQL so that same task can be achieved?
Please guide.
Regards,
D Patel
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
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
How to write trigger or procedure in MYSQL so that same task can be achieved?
Please guide.
Regards,
D Patel
Visual Basic.NETMySQL Server
Last Comment
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.
I need to write the procedure in MYSQL so that, it generate the number first and allot to specific user session.
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.
ASKER
Output will be any random number between 36 * 36 * 36 * 36 = 1679616 combinations. (i.e. SV9P, AEZO, 1SRL etc...)
ASKER
This particular column will have only unique data.
ASKER
my requirement is only alphanumeric string data of length 4.
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
Example
INSERT INTO table (field) VALUES (UUID());
ASKER
@Julian :
You are right but I need only 4 character long alphanumeric string.
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...
ASKER
How to use such routine (function / procedure) in ASP.NET?
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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?
ASKER
using VB.NET code behind On clicking the add button.
any logic to generate this nID ?
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
This is on button click event.
so can field: nID contains duplicate values if i run insert_randomKey() many times?
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?
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?
if you run insert_randomKey() many times, what's the expected output in your table?
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.
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.
ASKER
Thanks for your Support.
Your solution really worked for me after converting to AutoIncremental.
Your solution really worked for me after converting to AutoIncremental.
Can u give me ur input data and expected output.