• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 55
  • Last Modified:

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
0
D Patel
Asked:
D Patel
  • 15
  • 7
  • 3
  • +2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
You can generate row numbers in MySQL.
Can u give me ur input data and expected output.
0
 
D PatelD Patel, Software EngineerAuthor Commented:
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.
0
 
D PatelD Patel, Software EngineerAuthor Commented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
D PatelD Patel, Software EngineerAuthor Commented:
Output will be any random number between  36 * 36 * 36 * 36 = 1679616 combinations. (i.e. SV9P, AEZO, 1SRL etc...)
0
 
D PatelD Patel, Software EngineerAuthor Commented:
This particular column will have only unique data.
0
 
D PatelD Patel, Software EngineerAuthor Commented:
my requirement is only alphanumeric string data of length 4.
0
 
Julian HansenCommented:
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

0
 
D PatelD Patel, Software EngineerAuthor Commented:
@Julian :
You are right but I need only 4 character long alphanumeric string.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
I think you should create a routine that will  create a base 36 number system and thus create your specific 4 letters-digit identifier...
0
 
D PatelD Patel, Software EngineerAuthor Commented:
How to use such routine (function / procedure) in ASP.NET?
0
 
D PatelD Patel, Software EngineerAuthor Commented:
Data should not overwrite (once generated number will always kept into database for future tracking) and only unique number will get stored?
0
 
John TsioumprisSoftware & Systems EngineerCommented:
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
0
 
John TsioumprisSoftware & Systems EngineerCommented:
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
0
 
Ryan ChongCommented:
How to write trigger or procedure in MYSQL so that same task can be achieved?
try this:

1. define the stored procedure as shown in your previous question:

DELIMITER $$
USE `yourDB`$$
CREATE FUNCTION `getRandomKey`(keylength INT) RETURNS varchar(255) CHARSET utf8
BEGIN

DECLARE newKey VARCHAR(255);
DECLARE isUnique BIT;

SET isUnique = 0;

 loop_label:  LOOP
 
	 SET newKey = CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', RAND()*25+1, 1) , UPPER(LEFT(UUID(), keylength -1)));
	 SELECT COUNT(*) INTO isUnique FROM yourExisting_Table WHERE uniqueKey = newKey;

	 IF  isUnique = 0 THEN 
		LEAVE  loop_label;
	 END  IF;
            
 END LOOP;
   
RETURN newKey;
    
END$$

DELIMITER ;

Open in new window


2. you can do a simple insert using Insert statement.

Dim conn As MySqlConnection = New MySqlConnection(connStr)
        Dim cmd As MySqlCommand = New MySqlCommand("Insert into existing_table (uniqueKey) values (getRandomKey(4))", conn)
        cmd.CommandType = CommandType.Text
        conn.Open()
        cmd.ExecuteNonQuery()

        conn.Close()
        conn.Dispose()

Open in new window


3. OR create another stored procedure at Server End to get the random key.

DELIMITER $$
USE `yourDb`$$
CREATE PROCEDURE `insert_randomKey` ()
BEGIN
	Insert into existing_table (uniqueKey) values (getRandomKey(4));
END$$

DELIMITER ;

Open in new window


then call it like:

Dim conn As MySqlConnection = New MySqlConnection(connStr)
        Dim cmd As MySqlCommand = New MySqlCommand("insert_randomKey", conn)
        cmd.CommandType = CommandType.StoredProcedure
        conn.Open()
        cmd.ExecuteNonQuery()

        RefreshGridView()

        conn.Close()
        conn.Dispose()

Open in new window


full scripts is something like this:

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class WebForm1
    Inherits System.Web.UI.Page

    Dim connStr As String = "SERVER=localhost;" +
                            "DATABASE=yourDB;" +
                            "UID=userid;" +
                            "PASSWORD=password;"
    Private Sub RefreshGridView()
        Dim conn As MySqlConnection = New MySqlConnection(connStr)
        Dim cmd As MySqlCommand = New MySqlCommand("SELECT * FROM existing_table", conn)
        conn.Open()
        Dim dataTable As DataTable = New DataTable()
        Dim da As MySqlDataAdapter = New MySqlDataAdapter(cmd)

        da.Fill(dataTable)

        GridView1.DataSource = dataTable
        GridView1.DataBind()

        conn.Close()
        conn.Dispose()
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        RefreshGridView()
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim conn As MySqlConnection = New MySqlConnection(connStr)
        Dim cmd As MySqlCommand = New MySqlCommand("Insert into existing_table (uniqueKey) values (getRandomKey(4))", conn)
        cmd.CommandType = CommandType.Text
        conn.Open()
        cmd.ExecuteNonQuery()

        RefreshGridView()

        conn.Close()
        conn.Dispose()
    End Sub

    Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim conn As MySqlConnection = New MySqlConnection(connStr)
        Dim cmd As MySqlCommand = New MySqlCommand("insert_randomKey", conn)
        cmd.CommandType = CommandType.StoredProcedure
        conn.Open()
        cmd.ExecuteNonQuery()

        RefreshGridView()

        conn.Close()
        conn.Dispose()
    End Sub
End Class

Open in new window


Untitled.png
0
 
D PatelD Patel, Software EngineerAuthor Commented:
@ 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.
0
 
Ryan ChongCommented:
'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?
0
 
D PatelD Patel, Software EngineerAuthor Commented:
using VB.NET code behind On clicking the add button.
0
 
Ryan ChongCommented:
any logic to generate this nID ?
0
 
D PatelD Patel, Software EngineerAuthor Commented:
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.
0
 
Ryan ChongCommented:
so can field: nID contains duplicate values if i run insert_randomKey() many times?
0
 
D PatelD Patel, Software EngineerAuthor Commented:
no it cannot. It is Unique field (Primary Key). But not auto Increment.
0
 
Ryan ChongCommented:
no it cannot. It is Unique field (Primary Key)
Why not just make nID as auto-incremental?
0
 
D PatelD Patel, Software EngineerAuthor Commented:
Actually, the whole project is designed on this logic.
0
 
Ryan ChongCommented:
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?
0
 
D PatelD Patel, Software EngineerAuthor Commented:
It will add the new record with nID generated and unique random key. And all the other fields with it default values.
0
 
Ryan ChongCommented:
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.
0
 
D PatelD Patel, Software EngineerAuthor Commented:
Thanks for your Support.

Your solution really worked for me after converting to AutoIncremental.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 15
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now