Question Problem with auto generating alphanumeric id

[sny]

i have a problem with generating a userID, can anyone please help me, or give me some pointer.

If b = "add" Then
                Dim cmd2 As New Odbc.OdbcCommand("select user_id from user_tbl", Conn) 'For Primary Key Alpha Numeric ID
                Dim dr2 As Odbc.OdbcDataReader
                Dim strID As String = "USR-00"
                Dim recId As New ArrayList
                Dim i As Integer = 1
                Conn.Open()
                dr2 = cmd2.ExecuteReader
                If dr2.HasRows Then
                    While dr2.Read
                        If dr2.Item("user_id") = strID & i Then
                            i += 1
                        Else
                            userAcc(1) = strID & i
                            Exit While
                        End If
                    End While
                    dr2.Close()
                End If
            Else
                userAcc(1) = "USR-001"
            End If
            Conn.Close()

 

[jmcilhinney]

Why try to generate a key yourself? Just let the database do it. You can then generate the alphanumeric version of the ID in a separate column using an expression, either in the database or just in your app.

 

[sny]

sorry sir, but i don't have any idea how to do that, can you teach me please,..

 

[jmcilhinney]

Do you want to do it in the application or in the database? Is the application the only place that value will get used?

 

[sny]

what do you think Sir, is the most flexible or less bug, in the application or in the database,
actually Sir, i am making an inventory system with client server, this is my final project for
my thesis, please Sir help me on this one.

 

[jmcilhinney]

It's your application, so it's for you to make the decision. If the application is the only place that that "number" is used then you can do it in the application. If it's used elsewhere, e.g. reports, then it will need to be done in the database, so all clients can access it.

 

[sny]

can you teach me how to do it in the database, if it ok
or just give me an example. thanks ^^

 

[jmcilhinney]

What database?

 

[sny]

MySql, thanks.

 

[jmcilhinney]

Ah, I just remembered that we're actually in the MySQL forum. Sorry, I lose track as I visit so many different ones.

In that case, I'm afraid I don't know exactly. In SQL Server you would add a column and set its Expression property to a SQL expression that used the value from the auto-generated PK column to build the required text. I'm sure that it's similar in MySQL but I don't know what the equivalent of the Expression property is or exactly what the SQL code would look like.

 

[sny]

Can you show me how to do it in SQL Server, i want to try it. please sir i desperately need this in my project.
Thank you.

 

[sny]

if you don't mind, can you how me the please show me how to do it in SQL Server, all i need is how to make a table with auto generated alpha numeric primary key,
for example i have a field name ID, name, then i insert a value John, the primary key will automatically generate a value with a format like, usr-001.
is this possible to make ?

 

[sny]

i do it in the application, here's my code:
Try
Dim cmd As New Odbc.OdbcCommand("select user_id from user_tbl", x.Conn)
Dim dr As Odbc.OdbcDataReader
Dim arrData(10)
Dim i As Integer = 0
Dim j As Integer = 1
dr = cmd.ExecuteReader
If dr.HasRows Then
While dr.Read
arrData(i) = dr.Item("user_id")
i += 1
End While
i = 0
Do
id = "USR-00" & j
If arrData(i) <> id And i > 0 Then
Exit Do
End If
i += 1
j += 1
Loop
End If
Catch ex As Exception

End Try
x.Conn.Close()
y.userFormContent(no, id, "", "", "", "", "", "", "", "", "", "add")