I personally would not generate the ID in the C# code I would use the MySQL database to generate the id for me: See
MySQL :: MySQL 5.7 Reference Manual :: 3.6.9 Using AUTO_INCREMENT[
^]
I certainly would never use a method that relies on me incrementing the "previous" number as it will not work in a multi-user environment.
Once you have that numeric Id you can display it however you want e.g. (Not tested!)
String.Format("PC{0}//POS", id.ToString(D2));
although I would also not limit my output to 1 to 99 .. use D5 at least.
If you really, really want to store the text version on the database then you could use a trigger (
MySQL: AFTER INSERT Trigger[
^]) to capture the id and generate the text version
[EDIT after OP comment]
Try something like the following (note again, untested!)
CREATE TRIGGER product_id
AFTER INSERT
ON test FOR EACH ROW
UPDATE test set textid = concat('PC', LPAD(NEW.id, 5, '0'), '/POS') WHERE id=NEW.id;
Explanation:
The trigger is called
product_id
and it fires after a row is, or rows are, inserted into the table
test
There is only one statement in the trigger so I haven't used
BEGIN
...
END
, but you have already demonstrated that you know how to do that with your code in the comments - I'm just being lazy (and demonstrating a point). (Note, if you do have more than one statement you will need to change the delimiter when creating your trigger - see the documentation)
The trigger is going to
UPDATE
the table
test
using
NEW.id
. The special table
NEW
contains ... the new rows. You can't update it. There is another special table
OLD
which contains the old values too.
I'm sorry I haven't been able to test this properly and this isn't the correct place to tutor you further, but at least now you have some search terms to help your further research.
[EDIT - further code samples after OP comments. This solution is hidden in the reams of comments below].
Both of these will work
DELIMITER //
CREATE TRIGGER product_id
AFTER INSERT
ON test
BEGIN
String.Format("PC{0}//POS", id.ToString(D2));
END; //
DELIMITER ;
or
CREATE TRIGGER product_id
AFTER INSERT
ON test
String.Format("PC{0}//POS", id.ToString(D2));
Specifically, if you want to include a
BEGIN
and
END
you MUST use
DELIMITER
as well!