How to set mysql auto increment varchar

[prabuj04]

Done:
i already created Customer id as integer, primary Key and Auto increment in customer table.
Label, Text box and Save button created in VB.NET in form1
Expiation :r
while opening form1, customer id should be generated like C - 0001
Kindly help

[dday9]

Whenever you call your Insert command, do not pass a value for the auto-increment column and one will be generated for you.

[prabuj04]

Thanks for your replay how i insert string into that eg: " C - 0001"

[Shaggy Hiker]

Auto increment won't work on a varchar field. It only makes sense for numeric fields. Therefore, you have a couple options:

1) Leave an auto increment field holding just the "1" part of "C - 0001", while a second column holds the "C" part as a varchar field. When you want to display it, you concatenate the varchar field with " - ", then the auto increment field formatted to four characters.

2) Don't use an auto increment field at all, and just use a varchar field that holds the whole value. For this to work, it will be up to you to figure out what each key should be. There would be many ways to do this, such as storing the highest value in some other table, or just digesting the varchar "C - 0001" to get the number part and getting the maximum from that. Of course, that would mean that the letter would be irrelevant, so there's at least one, more complex version, where you have a different table that keeps a letter and the highest value associated with that letter.

[kebo]

I would leave the auto increment and adjust the SQL to give you what you want.

If the "C" simply denotes you are pulling from the customers table then use this sql command....

select concat('C-',LPAD(id,4,'0')) from <myTable>

If the "C" comes from the database, then adjust the command to fetch it rather than hard code it.

[Shaggy Hiker]

That would be my preference, as well.

[techgnome]

How we do it: Three fields: SEQUENCEID - int, autoinc; USERDEFINEDID - varchar; CALCUSERID - computed field, if there's something in the USERDEFINEDID field, that is returned as it is... if not, then the prefix (C-) is added to a 0-padded SEQUENCEID ....and we get C-00000008


-tg