Help: Auto increment Alphanumeric code

[abo0badr]

Good day guys!
I am doing a small function in my project, I have a Transactions Table in my database...the Table include: StudentID TransactionNumber Description Date
I already know that there is an auto-increment function using mysql db. By the way may primary key column is the StudentID, but I want to make an auto increment Alphanumeric function for my TransactionNumber like, OR #-0001 then when someone makes a transaction it will automatically get the current value in the database, and increment it by one which will become OR #-0002, etc.

How could I do this while auto increment is not a primary key column? anyhelp is really appreciated

[.paul.]

you could query your db first to get the highest TransactionNumber, then parse the string + extract the number to get your next number.

you should note, the way you have it at the moment will be interpreted as a negative number:

OR #-0001

will be selected as being greater than:

OR #-0010

to select the greatest minus number:

"SELECT TOP 1 TransactionNumber FROM Transactions ORDER BY LEN(TransactionNumber) ASC, TransactionNumber ASC"

to select the smallest minus number:

"SELECT TOP 1 TransactionNumber FROM Transactions ORDER BY LEN(TransactionNumber) DESC, TransactionNumber DESC"

[dunfiddlin]

I'm confused. Presumably it is possible for one student to be involved in more than one transaction so how can Student ID be your primary key?

[Shaggy Hiker]

Why go to all the trouble? Does the DB care whether the ID number is 1, 01, 0001, or #-0001? No. Only you really care about that. So how about just leaving the autoincrement alone as an integer and formatting it for display? The only reason to go through the contortions to use some formatted string as a primary key would be if you already have that key as a foreign key in a different table, and it sounds like that's not the case.